Re: Help finding natural keys

Bernard Peek wrote:

> I've been looking for an excuse to post some thoughts about surrogate
> keys. This looks like a good time.
>
> When a table has a real and surrogate key it is not fully normalised.
> That is the surrogate key is (you hope) completely dependent on the real
> key. But that isn't always certain. There are ways that dependency can
> be broken. When that happens your database is broken.
>
> Insert Anomaly: A new record can be inserted that duplicates all of the
> data from an existing record. But it will have a new surrogate key. The
> system will duplicate data.
>
> Update Anomaly: The data for the real key is changed, but without
> changing the surrogate. As a result the data in associated tables linked
> by the surrogate key now refers to the wrong person.
>
> Neither of these situations can be detected from within the database,
> they need additional processing systems to detect the anomalies, or the
> risk of anomalies. There may have to be manual processes or additional
> programs. This additional processing makes the system as a whole less
> robust. That is my main objection to using surrogate keys unnecessarily.

The above really made the advantages of primary keys as natural keys clear to me. It made me see it in terms of the extra work I am going to have to do. Until this it all sounded like theory, but in practice, I am going to have to write screens that will ask the user, are you sure you didn't already admit John Smith yesterday? Please look at this similiar record...

Alan Gutierrez - ajglist_at_izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows Received on Tue Jan 28 2003 - 17:04:45 CET