Help with DB Design
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,
I guess I scared everyone away? =(
Well you have put a lot of thought into the design and it's huge...
I guess my question is about the TestParentLookup table. What is it's purpose? It looks like an associative entity but it is only related to one other table.
Other than that I don't see anything glaring, though I probably need to look at it more. What DB design tool are you using? I do not like the fact that it does not show the 1:n 1:1 and m:n relationships (you have to infer them)...
Well, in the game as you move along you get transfer to a new class after a certain level.
For example you could go like this:
Fighter -> Rogue -> Treasure Hunter
Fighter -> Rogue -> Hawkeye
There are two choices after Rogue and you must choose. This is just a basic example, but what I was trying to do was list each class and then the ID of the parent class. So if Hawkeye had and ID of 10 and Rogue 30 it would list, 10, 30. Not sure if I went about it the best way.
I am using 30 day free trial of this:
I dont think I will be buying it. I ran into quite a few bugs with it. For example for the code generator it put a DEFAULT '0' in a PRIMARY KEY AUTO_INCREMENT column. Also I ran into some trouble with the FK's and not being able to delete them properly without deleting the whole table.
Thanks for you input greatly appreciated.
I have one question too by the way, do I make any of the tables that have FK's, PK's as well? I think I have to since the FK needs to match the same column type as the parent table, but I am not sure if the PK matters any since it will just use the ID from the parent table. Thanks,
ben can you please restate your last question, i don't understand what you're asking
I guess I am not sure if any FK columns should also be PKs.
that would depend on whether it's a PK or not
there are times when a FK is (part of) a PK but this is dictated entirely by the design
PKs are used to identify a row uniquely
FKs are used to relate a row to the PK of another table
a FK can be part of a PK but it doesn't have to be
Ok thanks Rudy, so for my case, because a ID from the Items table can only appear once in either Armor, Weapons, or MiscItems then I should declare them to be PKs in those respective tables correct?
sounds okay, but i did not examine your design to be sure
I didnt like the orginal software I used, so I asked my friend and he suggested I try Dezign for Databases. I updated the model in the original post to point to the new image. It supports the 1:n and 1:1 relationships (although I am not sure if I set them up correctly, lingo was a little wack). Thanks,