Regarding surrogate key

veera24 · Post by **veera24** » Tue Mar 04, 2008 11:07 pm

hi,
i got to load the data into Database and if the data get changed (in future) means am not supposed to overwrite it.My primary key is Customer_ID.I mean am in need of to maintain the history in the same file with cust
_id as a primary key as aforementioned.Will surrogate key logic be better or not?If so pls. help me in that logic.

Thanks in advance...

Raghavendra · Post by **Raghavendra** » Tue Mar 04, 2008 11:17 pm

How are you going to maintain the history in the database? Which type of SCD you are using? IS it type 1 or 2 or 3.

If you want to generate the surrogate key how would you like to generate. Do you want to generate it within the DataStage or do you want to generate it using a sequence in database?

Please provide more details so that people in this forum can give you proper suggestions.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 05, 2008 2:19 am

You may or may not require a surrogate key. A Type 4 SCD, for example, maintains a separate history table, and that can be keyed by the business key and an expiry date.

As a general rule, where there's a need to preserve history a surrogate key approach is to be preferred, because it is comparatively small (assuming you use Integers of some kind) and joins efficiently.

The business key is then a non-key column in the table and can repeat as often as that particular record changes.

You also need some way to know which is the current record.

jdmiceli · Post by **jdmiceli** » Wed Mar 05, 2008 4:47 pm

If you need to keep previous rows as 'historical', you could use a date for the expiration of the prior row as part of the business key. I find it is faster for searches if you add a smallint field (or BIT field if it's available), that becomes part of the primary key in your target. This field will only hold a 0 or 1. The unique index (clustered if possible) will only allow the normal business key plus the numeric field with a value of 1. Another index on the same fields will not be clustered or unique and those will hold a 0 in the numeric field. That way all unique business key rows are identified by the BK fields with the numeric field of 1, and the rest are history.

You could then put a view on the unique rows to make it easier for others to hit or just train them. Also, you could still add a date field to show when the current row became active for reference.

Kind of an SCD 2.

[/code]

veera24 · Post by **veera24** » Wed Mar 05, 2008 9:47 pm

ray.wurlod wrote:You may or may not require a surrogate key. A Type 4 SCD, for example, maintains a separate history table, and that can be keyed by the business key and an expiry date.

As a general rule, where th ...

could u pls. wat do do u man by business key?

veera24 · Post by **veera24** » Wed Mar 05, 2008 9:50 pm

ray.wurlod wrote:You may or may not require a surrogate key. A Type 4 SCD, for example, maintains a separate history table, and that can be keyed by the business key and an expiry date.

As a general rule, where th ...

could u pls. wat do do u man by business key?

ray.wurlod · Post by **ray.wurlod** » Wed Mar 05, 2008 10:06 pm

Could you please phrase your questions in English? DSXchange is not a mobile telephony device - there is no need to abbreviate. Further, the abbreviations make understanding more difficult for those whose first language is not English.

A search here or on the internet for the term "business key" will turn up suitable definitions. I intend the meaning to be the standard one.