how to 'scale' these three tables?

Question

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.

Answer 1

I would take a hybrid of your two approaches. Keep the weapons table for the common stats (id, type, damage, otherData). Then create gun_stats (weapon_id, accuracy, ammoType) and sword_stats (weapon_id, bladeType) tables that link to a weapon's id and have the remaining data pieces. You'll need just two queries to get all weapons of both types for a player, and there isn't any data duplication.

Answer 2

One solution is to create a master weapons table to which the Weapons_Swords and Weapons_Guns have foreign key references like so:

Create Table Weapons
{
    Id ...
    , ...
}

Create Table Weapons_Swords
{
    Id...
    , ...
    , Constraint FK_Weapons_Swords_Weapons
        Foreign Key ( Id )
        References Weapons( Id )
}
Create Table Weapons_Guns
{
    Id...
    , ...
    , Constraint FK_Weapons_Guns_Weapons
        Foreign Key ( Id )
        References Weapons( Id )
}

You would then have your standard PlayerWeapons table:

Create Table PlayerWeapons
{
    PlayerId ..
    , WeaponId ..
    , Constraint FK_PlayerWeapons_Players
        Foreign Key ( PlayerId )
        References Players( Id )
    , Constraint FK_PlayerWeapons_Weapons
        Foreign Key ( WeaponId )
        References Weapons( Id )
}

The downside to this approach is that you can have a weapon that does not point to a specific type and you do have additional joins. The upside is that you can add attributes common to all weapons into the Weapons table.

Your solution b involves denormalizing the Weapons table. The advantage is that it is significantly simpler to get data and ensure that a weapons does not point to nothing.

If the attributes of the various weapon types differ significantly, then I'd recommend creating a master weapons table. If there is a lot of similarities, then you could consider the denormalized solution. If I had no idea how much variance I would get, my inclination would be to make it as normalized as possible and thus use the master Weapons table.

Answer 3

Modern databases are pretty darn good at handling sparse data. Adding a few extra columns that are blank will not cause any trouble as long as the data type is small or variable width. This would allow you to extract some of the information out without complicating your table structures.

Another mechanism would be to add some extra index tables for the weapons that would help you search without modifying your Weapons table or your character loading code. Depending on the kinds of searches you are doing, that information should be easy to calculate, store, and update.

For the clean and truly scalable approach, I would have to go with the normalized tables @Thomas. If you just need to get a little further, one or both of the above approaches might help get you there.

Jacob