How to store data with N columns

Question

I need a way to store an int for N columns. Basically what I have is this:

Armies:
ArmyID  - UINT
UnitCount1 - UINT
UnitCount2 - UINT
UnitCount3 - UINT
UnitCount4 - UINT
...

I can't possible add a column for each and every unit, so I need a fast way to store the number of each units in an army (you might have guesses it's for a game by now). Using XML is not an option as it will be dead slow.

Answer 1

You could use table like this:

Armies
(
  ArmyID
  UnitID
  UnitCount
)

Answer 2

Define an 1:n relationship (3rd normal form)

Table Armies
  ArmyID

Table Units
  ArmyId
  UnitId
  UnitCount

Or do it in one table

Table ArmyUnits
  ArmyId 
  UnitId
  UnitCount

This obviously means that you have more than one entry per ArmyId.

Answer 3

You can use two tables:

Armies:
ArmyID - UINT

Units:
ArmyID - UINT
UnitIndex - UINT
UnitCount - UINT

Answer 4

Armies
------
ArmyID


Units
-----
UnitID
Description
Strength
Hitpoints


ArmyUnits
---------
ArmyID
UnitID
Count

Answer 5

This will allow you to store your counts in a one to many relationship. With the UnitID in UnitCount table being a foreign key to the Armies table.

Armies
(
  ArmyID
  UnitID
)

UnitCount
(
  UnitID
  UnitCount
)