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
Received on Fri Nov 28 2003 - 15:52:00 CST
