Re: primary ID trick: speed/disk space?

[Quoted] >> If an entire system is built with natural keys the number of
conditions in the where clause can reach 20-30 joins/conditions <<

I have never found that to be the case. Yes, multi-column primary keys do lead to more predicates that single column keys, but with far, far fewer errors. For example, one client I had used GUIDs on all tables for the keys and called them all "ID". You could make absurd joins by a simple typo in the correlation names, you had no idea what the query meant, etc. Life will be better when more vendors get the SQL-92 row constructors, so you can say things like "(S1.longitude, S1.latitude) = (S2.longitude, S2.latitude)" in one predicate.

>> In the accounting tables I worked with, they had 5-6 columns
representing the primary key of the table. <<

The Chart of Accounts I have seen are usually an integer with 5 to 9 places and the keys are a location/branch/office code plus the account number. What exactly are you putting in those columns? It sounds like an atomic data element has been split (like storing year, month and day separately instead of as a scalar date).

>> Serogate keys are easy for the optimizer to parse and require less
CPUs when joining, plus it makes updates and deletes easier since the where clause in many cases includes a list of rows or a subquery. <<

You forgot the storage and disk access overhead of storing the redundant pseudo-keys. And they make the updates and deletes harder because you have to verify that each pseudo-key matches to the intended row. Received on Thu May 01 2003 - 01:20:20 CEST