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_at_gmail.com> 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_at_seiler.us> 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

--
http://www.freelists.org/webpage/oracle-l