Re: Design approaches about primary key

Thank you Sven!

Our database indeed is a data warehouse; there is 90% reporting activity.

For us, cardinality and minimum of data storage were behind the decision for surrogate keys.

The main reason anyway was Oracle not being able to "cascade update" keys (as it SQL server can, btw).

-- 
Georg Scholz
www.scholz-informatik.at

"Sven Kolar" <devnull_at_svenkolar.net> schrieb im Newsbeitrag
news:bq8g6g$20vfvj$1_at_ID-216312.news.uni-berlin.de...


>

> Hi Georg!

>

>

> > I currently ask myself if using the integer primary keys

> > is really worth the effort.

>

>

> Are you working with OLTP databases only?

> My answer would be: "Surrogate keys, Sir? OLTP - no. DW - yes."

>

>

> OLTP:

> Surrogate keys should IMHO be avoided in OLTP

> implementations. Main reason: avoiding excessive lookups

> during atomic-transaction SELECTs, UPDATEs and

> INSERTs (which are plentiful in a typical OLTP setup). This

> approach simplifies my access logic (client-side DML), plus

> lessens the load on the server (no lookup joins!) -- join-hashing

> notwithstanding.

>

>

> DW:

>

> In my DW (data warehousing) implementations, however,

> I use surrogate keys (integers) only. Reasons for this include

> the following:

>

> - "FK cardinality":

>   because _cardinalities_ of fact-table's

>   foreign-key columns should be low (to take advantage of bitmap

>   indexes), star schema (with its "dimensions + facts" vector-spaces-,

>   granular- and top-down-like decomposition of data) lends itself

>   naturally to any DW implementation, and star schema would

>   explode (in terms of disk space) would I use natural keys;

>

> - "FK smallness" (closely related to the "FK cardinality" point above):

>   my star-fact table's composite key must be _small_ because

>   I'm dealing with huge number of rows in fact tables - every byte

>   counts - and integers are extremely compact in that respect;

>

> - (digression):

>   "FK smallness" and "FK cardinality" notwithstanding,

>   star schema is _elegant_ and easily understandable (as distinguished

>   from conventional 3NF ER designs which can become extremely convoluted),

>   and this (simplicity of star schemas) is a boon to business folks - so

>   we are talking happy marriage between simplicity and performance here;

>

> - I must accomodate SCDs (Slowly Changing Dimensions) of type 2

>   while designing DW dimension entities/tables, and surrogate keys are

>   perfect for this task;

>

> - only bulk INSERTs in DW, so I don't care about looking up

>   "natural" attributes in this case;

>

> - UPDATEs are rare in DW, so I again don't care much about lookups

>   in this case;

>

> - last but not least, for DW SELECTs, I use fast star transformations.

>

>

>

>

> Surrogate keys, Sir? OLTP - no. DW - yes.

>

> --

> HTH, Sven

> ---

> http://www.svenkolar.net

>

>