Re: Normalization, Natural Keys, Surrogate Keys
Date: Wed, 29 May 2002 13:30:50 +0100
In message <3cf3ead7_at_news.victoria.tc.ca>, Malcolm Dew-Jones
>Bernard Peek (bap_at_shrdlu.com) wrote:
>: In message <c1ec9b8f.0205241023.ebb99a0_at_posting.google.com>, Kai Ponte
>: <cybermusicdude_at_aol.com> writes
>: >> Having a surrogate primary key shouldn't let you off the hook. As a
>: >> rule of thumb, you always HAVE TO HAVE an alternate UNIQUE constraint.
>: >I respectfully disagree with "always have to have." Yes, it may often
>: >be necessary, but not always.
>: Can you suggest a situation where you do not need a unique constraint in
>: addition to the surrogate?
>The inability to provide a unique constraint (other than a surrogate) is
>exactly one of the reasons why data may need a surrogate.
>This happens when using old data. In addition to it being impractical to
>fix all old data before loading it into a new system, it may also be
>questionable to do so because the existing data may be a historical
>record, such as a license, which cannot be "corrected" within the
>framework of the application even if it appears to be wrong.
Good point. In that situation I'd probably do something similar, possibly by implementing a unique constraint on all new data but permitting it on old data, and flagging the old data as suspect.
The fact that data has been entered into a database without a uniqueness constraint is an indication that the database structure is at fault. One of the reasons why I prefer to avoid surrogates where possible is that using them can hide this type of failure.
-- Bernard Peek bap_at_shrdlu.com In search of cognoscentiReceived on Wed May 29 2002 - 14:30:50 CEST