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

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