View Poll Results: Your Choice,

Voters
7. You may not vote on this poll
  • Option 1

    2 28.57%
  • Option 2

    5 71.43%
Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778

    Unanswered: Surrogate Key POLL

    Option 1

    courses_availible(course, semester, year) primary key (all of these)
    course_timetables(course, semester, year, timetable_info, day, etc)

    Option 2

    courses_availible(id, course, semester, year) primary key (id)
    UNIQUE (course, semester, year)
    course_timetables(id, timetable_info, day, etc)

    Which is your option of choice?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    I go with OPtion-2
    SATHISH .

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    ahh, now what about your future join statements

    Find timetable for course x in sem y and year z

    Option 2
    Select timetable_info
    from course_timetables c1, courses_availible c2
    where c1.id = c2.id AND
    c2.course='x' AND
    c2.semester='y' AND
    c2.year='z'.

    you now have to match the child ID in course_timetables with its parent in courses_availible.

    The trade off. Although this was just to reply with some mild reasoning.
    Last edited by r123456; 11-25-03 at 11:19.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, there is always a trade off. I was reluctant to vote one way or the other, because there are times when I might do one, and other times where I might do the other.

    Generally I prefer to avoid overusing surrogates. I use them where:
    a) no natural key exists (like with people)
    b) the natural key is likely to be updated in the future
    c) the natural key is long and cumbersome (e.g. long text string)
    d) the natural key consists of many columns, and is referenced by foreign keys

    In this case I think only (d) really applies. The natural key exists, it is fairly unlikely to be updated, and it is not cumbersome - assuming "course" is a code rather than free text. This leaves me unsure as to whether the benefit of being able to join via a single column outweighs the benefit you mention of often not needing to join at all, because the child table contains sufficient information to be menaingful.

    I used to be decisive, but these days I'm not so sure...

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    "d) the natural key consists of many columns, and is referenced by foreign keys"

    Many columns is obvious although try defining too many. However is your reasoning behind the referencing by foreign keys related to updates of all further tuples where the primary key is referenced, just wanted to clarify that.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, just the fact that joins will involve several conditions, which can be tiresome to type. However, I don't consider that a major issue - and one can always create handy views to pre-join data. With 3 columns, it's not a big deal - if it were e.g. 7, I'd be more inclined towards the surrogate.

  7. #7
    Join Date
    Oct 2003
    Posts
    87
    Option 1 -- I prefer and have benefitted over the years using natural keys. The only exception I've encountered so far is employeeIdentificationNumber; I defy you to find a natural key related to a person.
    Oracle - DB2 - MS Access -

Posting Permissions

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