Re: Design approaches about primary key

"Sven Kolar" <devnull_at_svenkolar.net> a écrit dans le message 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
>
>

You
OLTP => no surrogate keys
DW => surrogates

Me
The total opposite

OLTP => because artificial,sequenced,trigger set keys (especially for lookup buffer kept tables) are more efficient than large natural composite keys (in most cases)

DW => because most of the time you don't need to enable, validate the uniqueness of the facts. All you need (is love ??) is to optimize joins on dimensions columns of your star schema. In this case, single column bitmap indices seem to be the best approach (once again, it is NOT a rule on the thumb)

Cheers,
Alkos Received on Mon Dec 01 2003 - 02:43:33 CST