Re: Normalization, Natural Keys, Surrogate Keys

In message <3cf3ead7_at_news.victoria.tc.ca>, Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca> writes
>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 cognoscenti