> > Perhaps I am amazingly ignorant, but I have yet to find a case where my
> > approach causes any real problems. What does using "real" data as a
> > primary key buy you? The only real advantages I can see are that an
> > individual record's data will be somewhat more human-readable without
> > joining to other tables, and that your search queries can be simpler
> > because they don't have to join against other tables.
>
> Joshua Drake wrote an interesting blog article earlier this year
> about what happened when a firm he was contracting for dropped the
> natural key on an important table and retained only the synthetic
> key. Unfortunately I can't find it... :(
I did? hmmm.... I just reviewed my blog entries and don't see one but I
can certainly point out billions of reasons why that is a bad idea.
Actually now that I think about it.. you are probably thinking of the
other illustrious Josh.. Berkus, he does have a blog discussing the
topic:
http://blogs.ittoolbox.com/database/soup/archives/primary-keyvil-part-i-7327
Here is a simple example:
id, first_name, last_name
=========================
1 | Joshua | Drake
2 | Joshua | Drake
Which one is the Joshua Drake you want?
But:
id, first_name, last_name, address_id
=====================================
1 | Joshua | Drake | 1
2 | Joshua | Drake | 2
primary key (first_name,last_name,address_id)
One will enable you to have a distinct Joshua Drake, one in portland
oregon and one in portland maine.
The other, you will have more than one Joshua Drake and not know if you
are applying your sales to the correct one (without a whole bunch of
work).
Besides, I am sure that many in this community will agree that one
Joshua Drake is certainly enough.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate