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.
