Is Surrogate Key is Required

Introduction

Here in this article I am just explaining my view related to the Surrogated Key. All depends on situation based scenario. Hope you like it.

What is Surrogate Key

As I am not going to any hardcore definition of Surrogate key over here, I am just going to explain it in my ways.

Surrogate key is just like a Primary key with an incremental number. It is basically an Identity column with data type Big Int. They Columns also contain a Clustered Index.

Use of Surrogate Key in Case of Dimension

In case of dimension we can better use of Surrogate key for SCD Type II case where we maintain the Historical records.

IDNO (PK)    ( Surrogate Key)

EMPID          ( Business Key)`

EMPNAME

EMPDESIGNATION

FROMDATE

TODATE

Is Maintaining the Historical records in Fact Table is Good ?

One use of the surrogate Key is maintaining the Historical records in Fact table. As per my point of view maintaining the historical records in Fact table is not a good idea. As the Additive fact is destroyed and getting bad result in case of aggregation.

If we want to maintain the historical records in Fact table we must use another table to maintain the history.

Here nothing if fix, all depends on case scenario. My be others have different type of views related to it. So please share your views that we can share knowledge.

Posted by: JOYDEEP DAS