Re: Surrogate Key vs Production Key

jcelko212_at_earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0410091028.2a9a36c0_at_posting.google.com>...
>
>
> >> And that is exactly what you get six relationships into the design,
> with natural keys: a compound key that is so large it will be a total
> disaster to index and store... <<
>
> This is the year 2004; storage is insanely cheap, why we make it from
> sand! And if there is a huge compound key, then you must make it
> unique to have a valid model -- unless doing it fast was more
> important than doing it right. What I find in reality with larger
> keys is that they are very natural to the users -- best examples:
> physical locations like (longitiude, latitude) or bin address systems
> (area, aisle, stack, bin).

Dealing daily with a bin address system, I can say that "natural" does not equal RI. All you need is someone to type in C1204A6 instead of C2104A6 and you've lost inventory until it is found on the next physical inventory - past its expiration date. And they always wind up wanting a staging location anyways.

As far as cheap storage, if it is so cheap, gimme some.

>
> You also forget that indexes are only one kind of access method. For
> compound keys and VLDB problems, I'd use a hash or compressed bit
> vector. Teradata for example takes an absolute max of two probes to
> locate any row regardless of the key; 90%+ are done in one probe.

USER-11 did that in the '80's on a PDP. Where is it now? (Answer: went through several revisions, paradigm-shifts and renaming to be more database portable, eventually winding up with a lot of lowest-common-denominator and environment-dependent code to deal with SQL-Server - because the applications sold are more important than the database internals).

>
> >> and you believe all that is easier to program and faster than a
> surrogate key based on a sequence, because?.... <<
>
> Because I already have the columns that make up the key and I already
> have made them unique. In theory, if I were to type in a VIN in this
> posting, you could go to the Internet, and find out all about that
> car. If I were to type the exposed physical locator used in a
> particlar release of a particular product on one particular machine,
> what do you do with it?

Actually, VIN is a very good example of a "natural" key one should _not_ use, because it is unnatural and the original designers thought it would be replaced with something else by now: http://www.cars.com/news/stories/070104_storya_dn.jhtml?page=newsstory&aff=national , among others.

jg

--
@home.com is bogus.
"Corporate chess?"  More like "Corporate Core-Wars:"
http://www.signonsandiego.com/uniontrib/20041009/news_1b9oracle.html