Tuesday, 19 May 2015

Difference between Businesskey and Surrogate key

Introduction
In this article, I will explain the Difference between Business key and Surrogate key

Business key
Surrogate key
Def:  A key is an index which identifies uniqueness of a row based on columns that exist naturally in a table according to business rules.  For Example, In a company are issued Employee Identification number (Eid) that is unique to them.
Def: A key that is auto generated identiy seed to efficiently associate a fact table or dimension table. For Example, to create identification number at the time of Develop of Dimension or Fact
• This keys are specified by user or company.
• This keys are generated at run time.
• Business keys are not only numeric, sometimes its date, text also.
• Surrogate keys are commonly a numeric number.  
• Business keys are changed or updated depends on business rules.
• Surrogate keys will not be updated over time.
• A business key is one or more existing data attributes that are unique to the business concept. For Example, In Employee table there are two business keys like Employee id and Department id
• Introduce a new column, which is a key has no business meaning.
• Business keys index will be larger because these are typically large is size.
• Surrogate keys are typically integers, so it having small index structure means better performance for Join operations.
• Business keys are a good way of avoiding duplicate records in the tables.
• Surrogate keys are more efficient that business keys. So that is the reason surrogate keys are recommended joining a fact table to a dimension table in cube.


Let me know what you think about this article.

2 comments:

  1. Hi sir,
    Is Natural key is same as Business key ?

    ReplyDelete
    Replies
    1. Natural key and Business key are both are same.

      Delete