Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    10

    Unanswered: Primary Key Selection

    Hello everybody,

    I am the newbie in the database normalization. What is the advantage of using a surrogate key? I have designed a database where the user will belong to one primary group and member of other groups. I designed the entity(table) group with a surrogate key so that can referenced in a users table for group membership details. One of my colleague insists on using the group ( Ex. animation) itself as PRIMARY KEY instead of using surrogate key since it reduces the number of queries ( Ex. To find out the membership if I use surrogate key I have to refer to the groups table and display the groups whereas if I use the group name itself as primary key I need not use two tables)

    The scanario is

    groups table
    group_id(PK)---------- group name----------- other fields
    1 --------------------------leather
    2 -------------------------IT enabled services
    3 --------------------------Education

    user table

    user_id --------primary_group --------secondary_group
    1 -----------------1 ---------------------------2-3

    In the second approach it becomes

    group table
    group_name(PK) ----------other fields
    Leather
    IT enabled services
    Education

    users table
    user_id -----------primary_group --------secondary_group
    1 ---------------------leather-------------- Education-IT enabled serrvices

    Can anybody help me to resolve this?

    Thanks in advance

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Primary Key Selection

    Originally posted by aniruthan
    Hello everybody,

    I am the newbie in the database normalization. What is the advantage of using a surrogate key? I have designed a database where the user will belong to one primary group and member of other groups. I designed the entity(table) group with a surrogate key so that can referenced in a users table for group membership details. One of my colleague insists on using the group ( Ex. animation) itself as PRIMARY KEY instead of using surrogate key since it reduces the number of queries ( Ex. To find out the membership if I use surrogate key I have to refer to the groups table and display the groups whereas if I use the group name itself as primary key I need not use two tables)

    The scanario is

    groups table
    group_id(PK)---------- group name----------- other fields
    1 --------------------------leather
    2 -------------------------IT enabled services
    3 --------------------------Education

    user table

    user_id --------primary_group --------secondary_group
    1 -----------------1 ---------------------------2-3

    In the second approach it becomes

    group table
    group_name(PK) ----------other fields
    Leather
    IT enabled services
    Education

    users table
    user_id -----------primary_group --------secondary_group
    1 ---------------------leather-------------- Education-IT enabled serrvices

    Can anybody help me to resolve this?

    Thanks in advance
    Surrogate keys are over-used, but sometimes necessary. An important criterion for choosing a primary key is that it should be a value that will never be updated (because updating primary key values that are referenced in foreign keys is messy and to be avoided).

    In this case, group_name is debatable: it is a fairly long, descriptive, mixed-case text column; I would not normally use such a column as a key because:
    - it is cumbersome to write the full key values in queries
    - it seems likely they will get updated at some time, e.g. if the group gets renamed.

    So in this case a surrogate seems reasonable. An alternative would be a short mnemonic group_code with values like 'LTHR', 'ITES', 'EDUC'. However, while these don't have the problems I listed above, they do involve someone having to come up with the codes, and the codes could become out of sync if the group names change.

    In the end, you have to weigh up the pros and cons and choose.

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    To add to what Tony has already said - don't use a sequence generated column as a primary key either - all that will happen is one day you'll do an export/import and the triggers will merrily replace your Primary Keys.

    Personally, I always use an ID column (sequence generated), they're great for client side grid (list) stabilisation etc but are considered 'throwaway values'. They're no good as primary keys.

    Hth
    Bill

Posting Permissions

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