Approach to be implemented with the keys

General
Enthusiast

Approach to be implemented with the keys

Hi,

I am confused in natural key,surrgoate key ,composite natural key which is best to use to reduce the data volatility during the data loads?

can any one help me out to choose the correct key

1 REPLY
Enthusiast

Re: Approach to be implemented with the keys

It is my viewpoint. There is nothing correct or not correct. But you can gauge yourself based on the flow, infrastructure, demography , homogeneous, heterogeneous sources etc etc.

By definition of DWH:

Subject-Oriented,Integrated,Time-Variant,Non-volatile.Also it is my trademark "Technology should serve business.Business should not bow down to technology" :)

Example:

If I, a salesperson, am transferred from one country to another by an organization.The question in

BI: sales data for me with my  first country for data before  the transfer date, and sales data for me  in the  new region after the transfer date. My identification in the same company remains the same. So sales dimension !!! A surrogate key allows me the same salesperson to participate in different locations in the dimension hierarchy.

Natural keys may be suitable when you have small, rarely changing, unique values (such as lookup tables), while surrogate keys are desirable for large, highly changing data key columns.

Also, I am pasting from design doc about surrogate keys:

Situations occur where the identification and choice of a simple primary key is difficult, if not

impossible. There might be no single column that uniquely identifies the tuples of a relation

variable or, looking ahead to physical design, there might be performance or query condition

considerations that argue against using a composite key. In these situations, and only in these

situations, surrogate keys are an ideal solution. 

Composite key for load, if you use where conditions, maybe more time-consuming.

Below links for references