Re: The Case Against Compound/Natural Keys
- From: JApplewhite@xxxxxxxxxxxxx
- To: oracle-l-bounce@xxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Sat, 27 Jan 2007 21:03:59 -0600
I actually prefer natural keys to surrogates. Our developers tend to slap
surrogate, sequence-generated, keys on tables that still allow duplicate
natural data. I'm surprised that your developers want to code the joins
based on several columns, instead of a single column. Usually they're
lazier than that. ;-)
To enforce uniqueness, we're all stuck with having space taken up by
indexes, but that's OK with me. The important thing is the quality of the
data.
You should consider partitioning these tables. That and all local indexes
should help all round.
IMHO you're off base in arguing against normalization. It's a GOOD thing
- a VERY GOOD thing, actually. Again, quality of the data and flexibility
of the data model are the most important things.
They really should have FK constraints where called for. Plus indexes on
the FK columns.
Jack C. Applewhite - Database Administrator
Austin (Texas) Independent School District
512.414.9715 (wk) / 512.935.5929 (pager)
Same-Day Stump Grinding! Senior Discounts!
-- Mike's Tree Service
"Don Seiler" <don@xxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
01/26/2007 10:50 PM
Please respond to
don@xxxxxxxxx
To
oracle-l <oracle-l@xxxxxxxxxxxxx>
cc
Subject
The Case Against Compound/Natural Keys
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
