Structure of database

Question

I'm creating a gaming website that will have a database of items found in a game (several thousand) and I'm wondering what best practice would be for the database structure.

There are 5 item types: weapons, armor, potions, quest items and junk. Each of these has unique attributes that don't apply to other items. For example, weapons can be different types (i.e. two-handed, main-hand, off-hand), armor can be different weights (plate, leather), etc.

Everything is an 'item', but I'm wondering if I should I have a single table of items, i.e.:

item id, item_type ... item_weapon_type_id, item_armor_weight_id

Thus having all possible attributes in one table.

Or separate tables for each item type, i.e.:

weapon_id, weapon_type, weapon_name

I am wondering this since each item type has a number of unique attributes, which means a single table of items would have a lot of columns, many of which don't apply to 80% of the items in the table.

Thanks in advance for any help.

You could do some sort of inheritance in the database http://stackoverflow.com/questions/386652/techniques-for-database-inheritance — the_lotus, Sep 10 '13 at 15:52

Mohsen Heydari · Accepted Answer · 2013-09-10T12:27:53.800

3

I think that will be useful having a base table for item types, called Item-Base-Type, containing shared properties of all item types.
Per item type(like shield) there will be a separate table ( like shield-type) with one-one relationship to the Item-Base-Type.
Item table that contains all items of the game will have a foreign key to the Item-Base-Type.
I think you will need some helper tables like Effect be added to the model.
is-quest-item will be a property of Item table.
Having a look at the wiki may helps.

template of game items

edited Sep 10 '13 at 12:27

answered Sep 10 '13 at 11:59

Mohsen Heydari

7,256
4
31
46

Thank you very much for taking the time to make this for me, very helpful! – user2131888 Sep 10 '13 at 19:05

score 2 · Answer 2 · answered Sep 10 '13 at 08:18

2

Have two tables:

Weapons

WeaponId
Description

WeaponType

WeaponTypeId
WeaponId
Description (One handed, two handed etc)

This way you can have the same weapon but equipped to one hand or two hands.

Separating out the tables will normalize the database and also improve performance. For example, you are not selecting repeating rows when you want a basic list of Weapons available.

answered Sep 10 '13 at 08:18

Darren

68,902
24
138
144

Thanks for the response Darren, much appreciated. – user2131888 Sep 10 '13 at 19:04

Vatev · Answer 3 · 2013-09-10T08:35:23.003

You can make two tables:

items
- id - PK
- type
- name
- some_attribute
- some_other_attribute
item_attributes
- item_id - PK
- attribute_name - PK
- attribute_value

Put the common attributes (e.g. price,weight...) in the items table, and the rest of them in the attributes table.

Also, don't worry too much if some of the fields in items are not used for all of the types. 2-3 enum fields should be sufficient to describe the types, armor 'heavy-ness', weapon types, etc... of the items (at least in the games I have seen).

Structure of database

3 Answers3