Posted by Erland Sommarskog on 07/14/05 16:42

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
>> In this case the IDENTITY _is_ a subset
>> of the attributes
>
> That's dangerous. IDENTITY is generally suitable only as an artificial
> key. It should not have any business meaning, in fact it should be
> completely hidden from users of the system. If you assign any business
> meaning to an IDENTITY key you set yourself up for a whole lot of
> potential problems to do with migrating and deploying data in a live
> environment.

Another one that competes with Joe Celko to know everything about everyone
else's system, I see.

Well, it's often the case that what you call "natural keys" are in fact
someone else's arbitrary sequential number, assigned with IDENTITY or
something else.

There is a whole of real-worl data out there that does not have any
natural key that obeys the laws of the relational model.

> Also, your design is fundamental flawed if you lack a natural candidate
> key - this is a frequent cause of incorrect results and invalid data in
> poorly designed databases.

Personally, I would be very careful with anyone else's design "fundamentally
flawed" when I have only seen glimpses of that design. Particularly when
it's clear from Elroyskimms that he has quite some experience of system
design. Just because he has not made the design that you or I(*) would
have done, does not mean that the design is wrong. When I think of it,
you should learn Perl. If nothing else, to learn the motto of Perl:
"There's more than one way to do it".


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]