I have been working on a new database design for a game site. I was wondering if a kind soul could look over it and perhaps comment on if its any good or if I am going in the wrong direction. I will be coding a PHP based front-end for it so I guess I will find out eventually .
The DB Design is located here:
Let me explain a few things before one goes crazy:
(Ignore the Test prefixes for now please)
-Items is the main table in this design. Every item in the game will be in the items table. All these items have the same basic properties and thus those are the columns in the Items table. Each table out from there lists a specific item type. Every item will fit into one of those sub tables. They all have FK links to the main Items table as the items must be there first and no item can be in more than one table.
-For the Quests table, each quest will be listed there first. Any rewards are stored in the reward table and linked to the QuestID and ItemID by FKs. The QuestItems links any items in association with that quest. Items you get along the way that are not rewards. The QuestClasses links the quest to any classes that can do it. QuestMobs links the QuestID to any mobs that are associated with the quest (ie killing for a drop).
-If we look at Monsters, we see that all the monster attributes are listed as its columns. MonLookup is a lookup table which gets the IDs from the Locations table that lists where the monster may be at. Drops lists all the drops by the MonsterID and links the ItemID to the Item table. More than one Item can drop from a monster at once. And the same item can drop from more than one mob.
-The Stores table lists connects to the Monster table and is getting NPC names. I use monsters and npcs interchangably. It basically lists all item availalble from one NPC from a certain place.
-The other tables I have not mentioned are not as great of importance as these at the moment. Once I am sure I did everything right for the others I will get to them.
I hope I didnt scare the whole forum away and someone could take a look. Thanks,