Re: Design approaches about primary key

Hi Alkos!

> 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)

With surrogate keys, your get additional baggage to carry around, you get additional overhead. End of discussion. And you have to deal with natural attributes anyway.

> 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)

I don't need to validate the facts (ETL does that for me). However in DW, integer keys are the only option. (Teradata being the only exception of a sort.) Reasons are numerous, well-researched and described in the literature, so I won't go into this here.

Conclusion:
I would go for natural keys any time, OLTP or DW. However space constraints are forcing me to use integers in DW. This is not such an issue in OLTP, because OLTP databases are relatively small and one can always throw more disks at the problem. With DWs, that's not the case - 10 bytes more in my fact tables design, and I could be forced to accomodate terabytes more of data.

I'll put an EOD here, if you would kindly allow, since there is not much to add anymore on my part.

--
HTH, Sven
---
http://www.svenkolar.net