none
Natural Key

    Question

  • I am a little confused about whether or not to use a natural or surrogate key.  I have read a lot of debates on using natural vs surrogate keys and i could see the benefits of both.  however my concern with a natural keys is setting up a foreign key on a natural key that has multiple columns.  for example im building an invoice log application and i have an invoice table with the columns (bussinessUnitID,VendorID,Date,Number) which are the natural keys for an invoice and completely unique.  however i have a lot of child tables for this invoice such as a discrepancy table.  it seems to me it would make more sense to reference invoiceID in the child table rather than the 4 natural key columns in the invoice.  so i guess my question is how do those who propose using the natural key suggest that i link my child tables to the invoice table?
    Coding 4 God!
    Thursday, November 3, 2011 5:53 AM

Answers

  • I think you are on the right duration. Have InvoiceID as a Primary key and create UNIQUE Constraint  on bussinessUnitID,VendorID,Date,Number columns. You will to reference in JOIN for all Child tables on InvoiceID (probably 4 bytes INT data type) 



    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Stephanie Lv Thursday, November 10, 2011 12:01 PM
    Thursday, November 3, 2011 6:27 AM
    Moderator
  • Hi WStoreyII,

    so i guess my question is how do those who propose using the natural key suggest that i link my child tables to the invoice table?

    Those who religiously hammer on natural keys would indeed include all
    four columns in the child table.

    My personal opinion is that in general, way too many people simply add
    a surrogate key to each table without thinking. I think surrogate keys
    should only be used where they offer a real advantage, and not
    everywhere. The situation you describe is a situation where I would
    definitely add a surrogate key.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by Stephanie Lv Thursday, November 10, 2011 12:01 PM
    Thursday, November 3, 2011 12:13 PM
  • >my concern with a natural keys is setting up a foreign key on a natural key that has multiple columns.

    But what is the concern?

    >my question is how do those who propose using the natural key suggest that i link my child tables to the invoice table?

    Natural key.

    <rant>
    I love it when people give a name to something so they can write it off. A natural key is not a theory or a thing. It is the natural key, that is, the natural way to do it. It is the natural key, not the "Natural" key. The key is inherent, it is obvious, it is the default, anything else is simply redundant.
    </rant>

    If there is a need for a surrogate, ADD a surrogate. Otherwise, why add useless complexity to the model?



    Thursday, November 3, 2011 1:09 PM
    Answerer

All replies

  • I think you are on the right duration. Have InvoiceID as a Primary key and create UNIQUE Constraint  on bussinessUnitID,VendorID,Date,Number columns. You will to reference in JOIN for all Child tables on InvoiceID (probably 4 bytes INT data type) 



    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Stephanie Lv Thursday, November 10, 2011 12:01 PM
    Thursday, November 3, 2011 6:27 AM
    Moderator
  • Hi WStoreyII,

    so i guess my question is how do those who propose using the natural key suggest that i link my child tables to the invoice table?

    Those who religiously hammer on natural keys would indeed include all
    four columns in the child table.

    My personal opinion is that in general, way too many people simply add
    a surrogate key to each table without thinking. I think surrogate keys
    should only be used where they offer a real advantage, and not
    everywhere. The situation you describe is a situation where I would
    definitely add a surrogate key.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by Stephanie Lv Thursday, November 10, 2011 12:01 PM
    Thursday, November 3, 2011 12:13 PM
  • >my concern with a natural keys is setting up a foreign key on a natural key that has multiple columns.

    But what is the concern?

    >my question is how do those who propose using the natural key suggest that i link my child tables to the invoice table?

    Natural key.

    <rant>
    I love it when people give a name to something so they can write it off. A natural key is not a theory or a thing. It is the natural key, that is, the natural way to do it. It is the natural key, not the "Natural" key. The key is inherent, it is obvious, it is the default, anything else is simply redundant.
    </rant>

    If there is a need for a surrogate, ADD a surrogate. Otherwise, why add useless complexity to the model?



    Thursday, November 3, 2011 1:09 PM
    Answerer