SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    First attempt at a Database design... Help a Newb with his Keys.

    Can someone see any potential problems with this design, it is my very first attempt and I hope I understand the 3rd Normal form. Any feed back will be greatly appreciated.

    Client Table
    Client_ID {Primary Key}
    Client_Name
    Client_Location {Foriegn Key?}

    Client Location Table
    Client_Location {Primary Key}
    State_ID {Foriegn Key?}
    District Name

    District Code Table
    Client_Location {Primary Key [Composite?]}
    District_Code_ID {What kinda key is this? Primary 2?}
    District_Code

    Building Code Table
    District_Code_ID {Primary Key [Composite?]}
    Building_Code_ID {What kinda key is this? Primary 2?}
    Building_Code

    Client Contact Table
    Client_ID {Composite Key?}
    Client_Address_ID {Primary Key?}
    Contact_Name
    Contact_Email
    Contact_Phone
    Contact_Fax

    Client Address Table
    Client_Address_ID {Primary Key}
    Client_Address
    State_ID {Composit Key?}
    City
    Zip

    Mileage Table
    Client_Address_ID {Primary Key?}
    Round_Trip_Mileage

    Remote Access Table
    Client_ID {Primary Key?}
    Client_IP
    Client_gif_location


    Hmmmm, as you can see I am still a lot confused on what kinda Keys there are... all I know is that I think all the tables relate to each other... Can some lend some advice or corrections. I tried to break down the tables to "Single Themed" based... or 3rd Normal Form.

    Thanks to everyone who can lend a hand.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one good way to do a preliminary design is to examine entity relationships and their cardinalities

    for example, you appear to have clients, locations, contacts, and addresses

    take these in pairs, examine the relationship in both directions, and see if they make sense

    for example (i'm making this up) --

    each client has exactly one location
    each location can have one or more clients

    ~ this tells you location-client is one-to-many, and that there will be a foreign key from client to location

    each location has exactly one address
    each address has exactly one location

    ~ this tells you location and address are one-to-one and could share the same record

    each person can be a contact for one or more clients
    each client can have one or more people as contacts

    ~ this says client and contact are in a many-to-many-relationship, and as far as keys go, you will need an intermediate "intersection table" to be the child table in two one-to-many relationships

    now obviously, i've just made up a bunch of crappy relationships that might not be right for you

    the point is, only *you* can decide what the relationships are in your application -- it's quite difficult to "unravel" them from the keys that you have embedded in your tables

    i mean, i could try, but what if you have erroneously placed IDs and foreign keys into tables that didn't need them?

    one way to salvage what you have is to pull all the foreign keys out of your tables and see what you're left with -- should be an ID and one or more descriptive fields in each table

    then your relationship analysis should tell you where the proper foreign keys go

    without the analysis of what you consider to be allowable one-to-many and many-to-many relationships, we'd just be guessing...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, thank you for your insight.

    There seems to be a couple ways to design a DB, some people follow a "Star Scheme," One2Many/Many2Many, or Normal Form... hopefully I figure it out... its one of those things that takes practice I gather.

    J.Loor


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
  •