Re: AW: Uniquely identifying a record in SQL without a unique key?

On 12/30/2012 3:54 PM, rob@xxxxxxxxx wrote:

Occasionally this debate rages. From those who say that a transaction log
that logs with a simple timestamp, which may not be unique, is ok and
doesn't need a unique key to those who say even that needs a unique key. I
can see both sides of the argument. Hey, even IBM's journals have a
transaction number that is unique. However, some may be concerned about
the disk space used, etc. Doesn't take much to generate a unique key.


Rob Berendt

The bigger debate in my mind is whether you should ONLY use a unique generated key (as opposed to a "natural" key like customer number or order number and line). This is a really interesting point and leads to some wickedly interesting design decisions (if you're into that sort of thing).

I'm old school and I still tend towards unique keys, but I designed an entire system based on unique generated keys and it had some powerful features (like the ability to change a part number on the fly by updating one record - every reference in every other record in the system stayed unchanged).

At the end, though, the unique key concept got tricky when you wanted historical information. Changing a part number today caused the past to be changed. Database time travel!!!

Joe