Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21

    Question PK on multiple fields or use single integer value?

    Hi all,

    i had an hard time to come up with a good title for this post (my first here) so i shall explain my problem more clear right here:

    Lets say i have to tables.
    One is called 'Inzet' and the other is called 'Oogst'
    In the first table the PK is defined by two fields: 'cel_code' and 'i_date'
    (there are three more fields in this table, but that does not matter now)

    In the second table, i have to refere to the first table (inzet) so the fields 'cel_code' and 'i_date' should reapear in the second table to act as a foreignkey..

    Now my question is whether or not this is the best way to do this...

    One other posibility i came up with is to add an 'inzet_id' to the first table and use that ID in the second table to refere to, so i could use an integer (auto_inc) as FK.

    I also came up with some pro's and cons of these posibilities:
    1) If i use and ID to identify an 'Inzet' that would be easier to work with in our application, i mean: it would be easier to make the code for the application.. so using an ID would be a good idea
    but...
    2) if i use an ID to identify the 'inzet' i loose the PK to prevent double and thus wrong values in the table 'inzet'.. so using a ID would not be good..

    So here i have this design dilema..
    I know both posibilities are posible, but i was wondering what was the preferd way if i want a good and sollid database design.

    I realy hope someone can tell me something about these kind of problems.
    Thanks in advance

    If anything is not clear, please ask, i'm very much willing to explain some more. Realy hope it is clear the way i wrote this down..

    Cheers
    Eelko
    Last edited by MoonCrawler; 12-23-05 at 15:28.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > 1) If i use an ID to identify an 'Inzet' that would be easier to work with in our application
    i do not understand why you think this -- it is no easier to use, in fact it's slightly harder

    > 2) if i use an ID to identify the 'inzet' i lose the PK to prevent double
    no, you don't, all you have to do is declare a unique constraint on the two columns combined (in addition to the PK for the auto_increment)


    the design choice you are facing is between a natural and a surrogate key

    search the web for those terms, you will find dozens of articles, forums, discussions, etc.

    there is no one true answer

    i personally favour using natural keys unless the key is comprised of several columns and has several levels of children (i.e. children, grandchildren, great-grandchildren, etc.)

    read this article: Should sequence numbers be used as primary keys?
    and make sure you also read A Truly ID-iotic Design which is linked to in that article

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

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    hi rudy, merry solstice.

    I recommend surrogate keys with unique natural keys.

  4. #4
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21
    Hi r937,

    Thaks for the quick reply.
    I will read the articles you pointed me to after the christmas holidays i guesss.

    If any one else has thoughs about this, please share them here.

    I wish everybody who reads this a very happy christmass and an wonderfull 2006!

    Cheers
    Mc

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I use natural keys as much as reasonably possible.

    Often the surrogates are annoying when you're doing application development. For instance:

    DEPARTMENT (ID, DEPTNUM)
    EMPLOYEE (ID, FNAME, LNAME, DEPARTMENT_ID)

    Now you know an employee is supposed to be in department ABCD. You need to do a separate query to get the ID from DEPARTMENT before you can insert or update the employee.

    SELECT id from department where deptnum = 'ABCD'
    => 228

    INSERT INTO EMPLOYEE (FNAME, LNAME, DEPARTMENT_ID)
    VALUES ('John', 'Doe', 228);

    Or, you can build it in to the query:

    INSERT INTO EMPLOYEE (FNAME, LNAME, DEPARTMENT_ID)
    VALUES ('John', 'Doe', (select id from department where deptnum = 'ABCD'));

    But it's it far more clean and clear if you say:

    DEPARTMENT (DEPTNUM)
    EMPLOYEE (ID, FNAME, LNAME, DEPTNUM)

    and

    INSERT INTO EMPLOYEE (FNAME, LNAME, DEPTNUM)
    VALUES ('John', 'Doe', 'ABCD')

    ?

    I certainly think so.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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