Normalizing this database: what would be ideal in this scenario?

Question

I'm designing a game where a character has many items and those items can be of many types. There is a character table, and twelve different tables of possible items broken down by type (such as weapons, armors, and various other item types).

I want to make a table to hold instances of these item types (basically a characters' items table) every row will have a foreign key from the characters' table to indicate which character has ownership of that item.

At first I figured I'd make foreign keys in the characters' items table -- one key for each of the twelve item tables. But since every item can be of only one "type," that would lead to eleven null fields in every row, and that seems wrong.

What would be the better approach? I've yet to build the database, so I can entertain other inventory ideas that don't use twelve item tables, but know this: the admin interface will allow a user to add/remove/modify items of each type as needed.

Also, I'd like to stick to the best normalization practice, so I'll ignore the inevitable "Who cares? Just do what works and use null-able fields."

Answer 1

I'd first check if you could unify those twelve tables into one. Start with Single-Table Inheritance, because it's simple and I bet it would be adequate in this case.

CHARACTER --<- CHAR_ITEMS  ->-- ITEM_TYPES_STI 

If not, try Class Table Inheritance. Basically, define a supertable for "item type" and the character's items reference that. Each sub-table for the 12 individual item types also reference the item types supertable:

CHARACTER --<- CHAR_ITEMS  ->-- ITEM_TYPES_SUPER --- ITEM_TYPE_SWORDS

Re your comment: I've clarified the line above. The relationship between item_types_super and item_type_swords should be 1:1. That is, for each row in swords there should be a distinct row in super. The way to do this is to make the foreign key in swords also its primary key.

But not every row in super has a row in swords. The row in super could be referenced by a row in shield, or axes, or whatever. But only by one subtype. The idea is that columns common to all item types belong in super, and the columns that are subtype-specific go in each subtype table.

Answer 2

If you used the following tables (I only included the key values for columns). The CHARACTERS_ITEMS table contains the list of items for each character. The combination of ItemTypeID and ItemID would be used to reference a row in one of the 12 items tables.

CHARACTERS
----------
CharacterID (PK)

ITEMS
-----
ItemID (PK)

ITEM_TYPES
----------
ItemTypeID (PK)

CHARACTERS_ITEMS
----------------
CharacterID (FK)
ItemTypeID (FK)
ItemID (FK)

Here is the beginning of a view that may be helpful as well. It demonstrates how the ItemTypeId can be used to reference the appropriate table.

CREATE VIEW v_character_items
AS
SELECT {list of columns}
FROM character_items
JOIN character_item_1 on character_items.ItemID = character_item_1.ItemID and character_items.ItemTypeID = 1

UNION 
SELECT {list of columns}
FROM character_items
JOIN character_item_2 ON character_items.ItemID = character_item_2.ItemID and character_items.ItemTypeID = 2

UNION
{other tables...}

Answer 3

In your case I would try to merge all twelve tables back into one table.

The reason for this is not really anything to do with technical table design -- but rather it will alow your characters to reuse objects in unexpected ways.

You could for instance have a set of throwable attributes such as EFFORT, ACCURACY, DAMAGE, BREAKAGE so a dagger would be almost no effort to throw, and cause a great deal of injury if it stuck its target, on the other hand a goblet would be easy to throw but cause very little damage or a gold bar would be hard to throw but would cuase a reasonable amount of injury even though it is not strictly speaking a weapon. It would also allow you to have weapons which were also "treasure" objects of great value or magical use (dont know what sort of game you are planning so these examples may not be appropriate

Answer 4

I don't know if you're still looking for replies, but here is how I did mine very recently:

CHARACTER -> Char/Item Link Table -> ITEM

ITEM
----
ID
NAME
TYPE
etc.

This allows you to create base items which multiple characters can possess. If you want to do individual character modification to items (i.e. gem slotting, enchantments, etc.) that can easily be done through an EFFECT table (or something similar):

ITEM -> Item/Effect Link Table -> EFFECTS

In this manner you can have characters customize their own weapons and still keep the base around. You can also create a set of effects without having to repeat them over and over for every item in your game.