How to store this data?

[Chris]

There are military units (1 type for simplification of example), these can have different training value depending when these were created (the older ones have more garrison training). How to store such data?

I would like to avoid creating separate table for a new batch of units with their creation time and quantity, since this would lead to heavier and heavier dataset (especialy if we assume no cap on training level).


My solutions so far:

1) Make 3 variables green/regular/veteran (training class). All new units start as green. Then, after some time, a progression check is made, all units have 10% chance to move 1 class up (starting from topmost progressable class, so the lowest class can not progress all the way due to luck in one iteration).
The con of this solution is that the progress checks had to be quite sparse and/or there must be more classes or the progress would be too fast. Also it is a quite artificial solution.

2) Similar classes as above, but each class has also experience variable. During each iteration (progression check) +1 experience per unit in that class is added. Then, when a class has 100+ experience it promotes 1 unit per 100 experience to higher class (experience is deducted then of course).
This solution has more gradual progression also it can be done as slow as we want it, also no luck involved. The con is that if you have a lot of units you are guaranted to progress each time (like: after you reach 100 units in all classes you will be getting 1 veteran unit each iteration; or if you instantly produce 10000 new units you would get several veterans in 2 iterations then you could disband all the green units and end up with strong veteran only army). Any ideas how to fix it? And is it a serious con?

3) Make several tables containing batches of units created around the same time. Like one table to hold all units created in one day. The table would hold quantity of units and experience. Units created at the first hour of the day start with 24 experience, units created next hour 23 experience. The experience is of course multiplied by units volume and modify the experience of the table. Assuming that there is a cap on 30 days worth of experience (all units that reach 24*30 experience are moved to the final "ultimate elite" table entry) we would need 30 table entries per player & unit type. But if we increase the batch time to 3 days (some gameplay reasoning like army conscript in intervals) we would end with 10 table entries only.

[133794m3r]

i'd just make one table with the units within it holding the creation time, the player's ID that owns them, the unit's number, the unit's type, the unit's rank, and since you want exp. Then use a foreign key link to the player's table and hold the data there. That's it and it's well two tables one extra one but your code would then have to change those values as they grow. The table would get larger but i doubt it'd get "too large"  since the type and the unit's rank can both be tinyint's which is 2 bytes total. the Unit's reference number shouldn't be more than a smallint since i highly doubt you'er going to have more than 65k of each unit's type so that's another 2 bytes. The creation time which'll have to be a 4byte unsigned int. owner's id depending on your storage no idea how big it's going to be. And the exp i doubt would be more than 4b so that's another 4 bytes. 4+2+2+x=8+x bytes of data per unit. If each person had 10k units tha'ts only 80KB of data per user. Sure it's a bit much but it's not exactly "huge" and your code could handle the ranking types and other such algorithms. that's how i'm currently doing the items and such since a simple switch() case is faster than an additional query/space constraint.

[BaRRaKID]

You can create a table with 11 (considering that the experience increases in steps of 10, and max is 100) columns (Zero, Ten, Twenty, Thirty, Forty, Fifty, Sixty, Seventy, Eighty, Ninety, One Hundred). To simplify lets also assume that the game is tick based.

Tick 1 - Player created 10 units -> Add 10 to column Zero
Tick 2 - Player created 0 units -> Add 10 to column Ten, Subtract 10 from column Zero
Tick 3 - Player created 5 units -> Add 5 to column Zero, Add 10 to column Twenty, Subtract 10 from column Ten

So you now know that of the 15 units you created, 10 of them are at level 20, and 5 are at level 0, you don't really need to know which ones, just how many. What you're doing basically is moving the units up the experience ladder.

In practice you would have to do the following in each "tick"

column100.value = column100.value + column90.value
column90.value = column80.value
column80.value = column70.value
column70.value = column60.value
column50.value = column40.value
...
column0.value = AmountOfUnitsCreated

I understand that this is not an elegant solution and that it can get a bit messy if you have more steps or a higher max level, but I think it's less expensive than storing each unit and the time it was created.