SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with DB Design

    Hello,

    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:

    UPDATE:
    http://lin.warcry.com/tests/DB_Design.jpg

    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,

    -Ben
    Last edited by Ben; Jan 6, 2005 at 15:56.

  2. #2
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess I scared everyone away? =(

  3. #3
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    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)...

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    -or-
    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:

    http://www.microolap.com/products/database/mydesigner/

    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.

  5. #5
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    ben can you please restate your last question, i don't understand what you're asking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess I am not sure if any FK columns should also be PKs.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sounds okay, but i did not examine your design to be sure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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,

    -Ben


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •