locked
What Should be used Natural Key Or Surrogate Key RRS feed

  • Question

  • Hi Experts,

     

    I have few questions about Natual Key and Surrogate Key.

    We are designing financial application (Wealth Management System) database. I would appreciate if you could throw some light on following scenario.

     

    Scenario

    -----------------------

    As you all would be aware CustType,Country,Currency are various master fields that are required at any customer Master. Generally data entry users prefer to enter code for an item rather than selecting an item from a list of 200 items because they do remember the code for frequent items after few months of deployment. Data entry operator would not prefer to select a country from a dropdown of 200 he would simply write the country code in field or incase he does not know the code then he can chose from the ajax based division panel just close to text field. If we provide dropdowns for each these masters items then at page load we have to populate all these dropdowns.

     

    Table1: CustomerType

     

    CustTypeCode  CustomerType

    -------------------    -------------------------------

    Trust                Trusts

    Corp                 Corporate

    Asso                 Association

    Socie                Societies

    Club                  Clubs

    '

    '

    '

    Table2:Country

     

    CountryCode       Country

    ------------------        --------------

    IN                       India

    US                     United States of America

    '

    '

    Table3:Currency

     

    CurrencyCode    Currency

    ------------------      ----------------

    INR                   Indian National Rupess

    USD                 United States Dollar

    AUD                 Australian Dollar

    '

    '

    My question is should be use codes as a natural key or a surrogate key should be added to above child tables and that surrogate key would act as foriegn key in  Customer Master Table.

     

    If we use implement surrogate key then for every search in customer master we have to build join with all these tables.

    Suppose there are 50 these type of master are used in customer master table then we have to connect with all 50 to show the users.

     

     

     

    Customer Master In case of Natual Key

    ------------------------------------------------------------------------------------------------------------------------------

    Cust_Id| Cust_Name     | Cust_Type_Code | Cust_Currency_Code| Cust_Country_Code|

    ------------------------------------------------------------------------------------------------------------------------------

    123311| ABC LTD          |          CORP      |        INR                    |           IN

    ------------------------------------------------------------------------------------------------------------------------------

     

    Customer Master In case of Surrogate Key

    ------------------------------------------------------------------------------------------------------------------------------------

    Cust Key|  Cust_Id | Cust_Name | Cust_Type_Key |Cust_Currency_Key |Cust_Country_Key

    ------------------------------------------------------------------------------------------------------------------------------------

    1           |  123311 | ABC LTD      |         2              |          1                  |          1

    ------------------------------------------------------------------------------------------------------------------------------------

     

    Which one is better approach, our customer based would be approx 10 million records.

     

    Regards,

     

    Sameer Gautam

     

    Wednesday, May 28, 2008 1:43 PM

All replies

  • Hi Sameer,

     

         About your question the answer is not always simple because there's a huge discussion about the subject, and you can see about this in the following blog entry that speaks about the subject.

     

         In my personal experience, I normally try to use the Natural Key's rather than the Surrogate because the Natural Keys have business meaning and for this reason it's easier to identify, search and so on, but this will stop if those Natural Keys are not numeric, and in those cases I tend to use Surrogate Keys because the Join's with non numeric columns is very slow, but that for me is the only reason.

     

    Hope it helps,

     

       Nuno Godinho

    Wednesday, May 28, 2008 6:55 PM
  • Natural vs Surrogate keys is a huge topic of debate ... and like most things in technology, the answer is ... it depends. When possible, I favour using surrogate keys for several reasons:

     

    1. They provide a common, consistent key strategy for across all of your tables.

    2. They take up less disk space in cases where the alternative would have been to use a composite key.

    3. They decouple your business logic from your keys.

    4. They allow for fast joins.

    5. As natural keys represent real attributes of the entity they represent, any changes to them need to be cascaded to ensure referential integrity. Using a surrogate key bypasses this need because it should never need to be changed.

    6. They can speed up debugging, as joins are less verbose.

     

    There are of course tradeoffs in using surrogate keys ... BUT I've found that the pay-offs more than justify them.

     

    In this case what you're specifically referring to is a Lookup/Code/Static/Reference Data table. There are a couple of schools of thought on this (both quite valid so it's really going to come down to which side of the fence you want to lean on).

     

    One school of thought is that Lookup tables present a special case. If a Lookup table has a natural key made up of one column ... that is never going to change ... there's an argument that there isn't much to be gained from adding a surrogate key. However if the natural key is composite in nature, I'd still lean towards going surrogate.

     

    The other school of thought is that lookup tables are just like any other table, and so should be keyed the same way as the rest of your database.

     

    Unless there is a genuine exception case that warrants going the natural route, I prefer consistency ... using surrogate keys for all my tables.

    Wednesday, May 28, 2008 7:19 PM
  • Surrogate keys versus natural keys seems to be one of those topics that are largely down to opinion and personal choice.

     

    I like to use surrogate keys because a signal field can refer to the records, whereas with the natural key version, with composite keys it's just easier, and quite possibly faster.

     

    Another interesting issue arises when a 'key' is a string, then joins and the like are down to table scans with the natural key version, but are much more efficient with surrogate keys.

     

    The other issue with natural keys that plays a large part in the issue, is if the key needs to be altered, the index needs to be regenrated with natural keys.  With surrogate keys, once the record exists, the data for that records can be altered as appropriate.

     

    Hope this helps,

     

    Martin Platt.

    Friday, May 30, 2008 4:26 AM
  • Although it's breaking Codds rules you can have both. There is nothing techncally stopping you using some sort of identity/auto increment/guid surrogate key *and* have the natural key. This can actually be useful as the surrogate key will remain fixed whereas the natual key can change. As has already been posted this is a lively debate and as Martin says it's really down the context/problem you're trying to solve. If you're using SQL Server then it's trivial to enforce the unique key and you can (depending on the version) use a view to create two clustered indexes on the same data (with the usual update implications)
    Sorry I've probably just muddied the waters but IMO it's not a black or white answer.



    Sunday, June 1, 2008 12:25 AM