Ok, so I finally have the time to get back to making games.
Currently I'm working on the battle engine. Two players and their units (1-16 per player) are going to duke it out. During combat, certain things can happen to a unit. They can be set on fire, put to sleep, paralyzed, slowed, etc (you get the idea).
Now my question is how to design the user-owned units table.
I was thinking of doing the following:
- unitid (which unit this is)
- playerid (to which player this belongs)
- ... (different fields like level, etc.)
- on_fire (1 if the unit is on fire, 0 if not)
- sleep (1 it it's asleep, 0 if awake)
- etc.
Of course there is the other option of having a table with:
- statusid (which status this is)
- unitid (which unit is affected)
- affected_by (by which status this is affected 1 = on fire, 2 = asleep...)
Now I'm wondering which one would be better?
The first one uses more space in the playerunits table, but no additional tables, the second one uses smaller but multiple tables.
Now a bit of background maybe: as I said, each battle can be fought from 2 up to 32 units. Every player can have an (as of now) unlimited amount of units. Each unit can be affected by 4-6,7 (still to be decided) different things at once.
I know, generally normalization is better, but what about a case where there could be a new table with n*6 (n = number of units in the whole game) entries vs. extending the units-table by 6 fields?
[edit] Oh, by better I mean which one of the two approaches (or a third approach I didn't list here) would be faster and more importantly more stable for a lot of queries.
