none
surrogate key RRS feed

  • Question

  • Hi gurus

    can any one tell me what is the best way to use surrogate key (except uniqueidentifier datatype)? how can I use with TSQL?

     

    Tuesday, September 19, 2006 10:02 AM

Answers

  • i have  a hospital information system

    and i have several tables.

    one is doctors with pk doctor_id

    and i have a patients with pk patient_id.

    when a patient is admitted he/she will have

    a case in the cases  table identified by a unique key on

    patient_id , doctor_id, and admission date. however the cases table

    uses the pk case_id on the cases table.

    i have medicines_for_admitted  table which stores the

    information for the intake of medication of an admitted patient.

    this table relates to cases table using the FK cases_id.

     

    all of the PKs in the table are surrogate keys of identity type of big integer.

    the obvious advantage here is that the system or the user doesn't realy care

    about what ever value may have been assigned for  a record

    on any of its table considering the number of patients and doctors the hospital may have had

    in its exisitence. the second advantage is that its really easy to relate cases to medicine

    bu just using a single case_id rather than a combination of the three combined unique keys.

    third is that you can actually edit one of the three keys in the cases table with out the needs of

    cascading the changes to the medicines tables

     

    some more reading materails on this site:

    Intelligent Versus Surrogate Keys

    http://www.bcarter.com/intsurr1.htm

     

     

     

    Tuesday, September 19, 2006 11:44 AM
  • Just to add one thing to what joey said.  The name "surrogate" keys is very important.  It is a surrogate for some other key, typically used for performance and ease of programming.  It is not a real key in and of itself because it has no real value in the world and cannot be tied back to something in the real world. 

    So if you use an identity (or a guid/uniqueidentifier for that matter) as the key for a person, be sure to include some other key, social security number, badge/employee number, etc too.  Otherwise you can have:

    personId    firstName   lastName
    -------------  ---------------  ----------------
    1                bob              smith
    2                bob              smith
    3                bob              smith
    4                bob              smith

    and this be treated as valid data.  Even if you do happen to work with 4 bob smith's, I know that they don't all want to get mistaken for each other on pay day (especially CEO bob smith) or on layoff day (bob smith with 4 kids, 2 dogs, and a wife with an atomic credit card would not appreciate that for sure)

     

    Wednesday, September 20, 2006 1:27 AM
    Moderator

All replies

  • i have  a hospital information system

    and i have several tables.

    one is doctors with pk doctor_id

    and i have a patients with pk patient_id.

    when a patient is admitted he/she will have

    a case in the cases  table identified by a unique key on

    patient_id , doctor_id, and admission date. however the cases table

    uses the pk case_id on the cases table.

    i have medicines_for_admitted  table which stores the

    information for the intake of medication of an admitted patient.

    this table relates to cases table using the FK cases_id.

     

    all of the PKs in the table are surrogate keys of identity type of big integer.

    the obvious advantage here is that the system or the user doesn't realy care

    about what ever value may have been assigned for  a record

    on any of its table considering the number of patients and doctors the hospital may have had

    in its exisitence. the second advantage is that its really easy to relate cases to medicine

    bu just using a single case_id rather than a combination of the three combined unique keys.

    third is that you can actually edit one of the three keys in the cases table with out the needs of

    cascading the changes to the medicines tables

     

    some more reading materails on this site:

    Intelligent Versus Surrogate Keys

    http://www.bcarter.com/intsurr1.htm

     

     

     

    Tuesday, September 19, 2006 11:44 AM
  • Just to add one thing to what joey said.  The name "surrogate" keys is very important.  It is a surrogate for some other key, typically used for performance and ease of programming.  It is not a real key in and of itself because it has no real value in the world and cannot be tied back to something in the real world. 

    So if you use an identity (or a guid/uniqueidentifier for that matter) as the key for a person, be sure to include some other key, social security number, badge/employee number, etc too.  Otherwise you can have:

    personId    firstName   lastName
    -------------  ---------------  ----------------
    1                bob              smith
    2                bob              smith
    3                bob              smith
    4                bob              smith

    and this be treated as valid data.  Even if you do happen to work with 4 bob smith's, I know that they don't all want to get mistaken for each other on pay day (especially CEO bob smith) or on layoff day (bob smith with 4 kids, 2 dogs, and a wife with an atomic credit card would not appreciate that for sure)

     

    Wednesday, September 20, 2006 1:27 AM
    Moderator
  • Hi 

    You can check following link for the Surrogate keys : http://www.aboutsql.in/2013/03/surrogate-keys.html

    Wednesday, March 13, 2013 2:14 PM