I have the following Tables:

Players

• id
• playerName

Weapons

• id
• type
• otherData

Weapons2Player

• id
• playersID_reference
• weaponsID_reference

That was nice and simple.

Now I need to SELECT items from the Weapons table, according to some of their characteristics that i previously just packed into the otherData column (since it was only needed on the client side). The problem is, that the types have varying characteristics - but also a lot of similar data.

So I'm trying to decide on the following possibilities, all of which have their pros and cons.

Solution A

Kill the Weapons table, and create a new table for each Weapon-Type:

Weapons_Swords

• id
• bladeType
• damage
• otherData

Weapons_Guns

• id
• accuracy
• damage
• ammoType
• otherData

But how will i Link these to the Players ?

• create Weapons_Swords2Players, Weapons_Guns2Players for each weapon-type? (Will result in a lot more JOINS when loading the player with all his weapons...and it's also more complicated to insert a new player)

or

• add another column to Weapons2Players called WeaponsTypeTable, then do sub-selects to the correct Weapons sub-table (seems easier, but not really right, slightly easier insert i guess)

Solution B

Keep the Weapons table, and add all the fields i need to it. The Problem is that then there will be NULL fields, since not all Weapon-Types use all fields (can't be right)

Weapons

• id
• type
• accuracy
• damage
• ammoType
• bladeType
• otherData

This seems to be pretty basic stuff, but i just can't decide what's best. Or is there a correct Solution C?

many thanks.