Re: The Case Against Compound/Natural Keys

Niall nailed it right off the bat  - you've a really bad case of a
de-normalized app by the sound of it.

1st (by now famous rule) of normalization - "...the (table) data has to be
dependant on the key, the whole key and nothing but the whole key - so help
me Codd".

It's probably too late, but a competent data-modeler to drive out a
normalized data model instead of what the duh-velopers assume to be the
requirements could be the voice of sanity you need in the environment.

When the app gets re-designed (inevitable when it collapses under it's own
weight) this could the out you need at that time.

On  1/27/07, Niall Litchfield <niall.litchfield@xxxxxxxxx> wrote:


Well first up natural vs synthetic is a rather religious argument. My
observations are the, perhaps apparently contradictory.

1) Really well designed applications tend to have 'natural' keys.
2) Badly designed applications also tend to have natural keys.

It sounds rather like you might be falling into the latter category.

In a well designed application careful thought is given to the entities
involved, what properties they have that you are interested in and how they
relate to each other, either logically or in terms of the rules of the model
(aka business rules). When you do this the 'natural' key will tend to fall
out of the analysis.

In other applications tables tend to be thrown in one after another to
meet changing application requirements, to have columns added and deleted
almost at whim and to have a changing definition of what it is that the
developer is interested in from the table. Sometimes the table ends up
fulfilling an entirely different purpose altogether than it's original one.

I'd not be arguing for or against the choice of natural vs surrogate but
for a rational process of design. Adding a field to a primary key because
the data arriving as the primary key is non-unique seems daft to me, surely
you throw out the duplicates at the load stage. I can however almost
guarantee that if you get a surrogate key, they'll still want a second
unique index and will want to add fields to it in the event of
non-uniqueness.

On 1/27/07, Don Seiler <don@xxxxxxxxx> wrote:
>
> I've been on a crusade against my developers lately after having had
> enough of fields being added to already-insane primary keys.
>
> Today I was given a script to add a field to make what is now a
> 15-field primary key, all natural data.  The first 9 fields of the key
> also provide the foreign key into the parent table (although a foreign
> key constraint is, of course, not used), and it carries on down the
> line, growing worse and worse.  They've all heard me calling for
> surrogate keys,  but they say they need uniqueness among this set of
> fields.  Then when they discover duplicates, they just add another
> field.
>
> I even suggested having NO primary key, just a non-unique key on the
> first 4 or 5 fields.  But again they say they need to guarantee
> uniqueness.  These tables are bulk-loaded and can contain over 150
> million records.  There is no query that even comes close to utilizing
> these fields, it is purely a unique constraint.  However, since a
> unique constraint also creates an index, I didn't see any advantage
> there.
>
> I'm looking for the words to basically doom this practice once and for
> all.  I've already told them about the degradation of normalization,
> the storage needed for these unused indexes, etc.  They claim there's
> nothing they can do for now.  Migrating to surrogate keys would be
> non-trivial and isn't a priority, it seems.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


--
Niall Litchfield
Oracle DBA
http://www.orawin.info





--
Johan Muller
Oracle DBA
(214) 676 2147 anytime.

"I love deadlines. I like the whooshing sound they make as they fly by."
Douglas Adams

Other related posts: