Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: DB Design question, customer id.

    OK I am working on a program for a local company to store their client's contact info as well as the Jobs they do for each client.

    in my Contact table I have the usual stuff. FName LName Email PNum STAddress City ST Zip

    for jobs I need to store the following info Brand Model FormFactor PowerSupply Property1 Property2 Details and Presently in-house.

    Since each client may have multiple Jobs I cannot store it in one table. I have no clue how to reference Jobs based on Client. I have tried using the phone number but that proved to be ineffective.

    Is there a way that I can ensure a unique entry in "Clients" and be able to use that info when referencing "Jobs.

    I am not really in need of the query code just the layout.

    Any help is a[[reciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by xandout View Post
    OIs there a way that I can ensure a unique entry in "Clients" and be able to use that info when referencing "Jobs.
    make sure the clients table has an auto_increment primary key (call it id)

    then add a client_id column to the jobs table, and link up each job to its client by placing the client's id value from the clients table into the client_id column of the jobs table for that particular job

    for full marks, use the FOREIGN KEY syntax to declare the relationship explicitly

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    2

    Tjanks

    ok that is what i had thought as well but I was tinking of doing it programmatically. I just was curious if there was a way to do it through the DataBase. Thanks for reassuring me.


    Could you explain the foreign keys concept a little bit. I have tried Google but I couldn't make sense of it.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by xandout View Post
    Could you explain the foreign keys concept a little bit.
    a foreign key is a column on one table that has values which reference (i.e. come from) the primary key of another table

    you can relate tables this way blindly, and control the values with application code, or better yet use the FOREIGN KEY syntax to register this relationship with the database, so that the database will ensure that no one and no application can ever insert a row with a foreign key value that doesn't exist in the referenced table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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