Re: surrogate key or not? - Mailing list pgsql-sql

Regarding natural or surrogate keys...

It is often possible that a table definition does not depict
reality, meaning that the specification given at table design
phase was wrong, (or was later proved wrong).

I had a table "parts" like
\d parts                                     Table "public.parts"    Column      |         Type          |
        Modifiers
 
-----------------+-----------------------+-------------------------------------------------------partno          |
charactervarying(20) | not nullmachtypeclsid   | integer               | not nullpartclsid       | integer
| not nullpicture         | bytea                 |instructions    | bytea                 |
 
.............
Indexes:   "parts_ukey" primary key, btree (partno, machtypeclsid)
Foreign-key constraints:   "$2" FOREIGN KEY (partclsid) REFERENCES partclasses(id)   "$1" FOREIGN KEY (machtypeclsid)
REFERENCESmachtypesclasses(id)
 

Initially i was told, and found pretty natural, that a machine type
and a part number would fully identify a part.

Only to find out later, after a bunch of apps was written, that some 
makers, described, in the same machine type,
different parts (on differnt drawing numbers i.e. pages in maker's manual)
with the same part numbers.

The irony here, is that this was the only instance of natural primary keys
in my whole schema.

I had then to write a script to convert the table itself, as well as 
children tables to it, to the new schema.

Now the table looks like:

\d parts                                     Table "public.parts"    Column      |         Type          |
        Modifiers
 
-----------------+-----------------------+-------------------------------------------------------partno          |
charactervarying(20) | not nullmachtypeclsid   | integer               | not nullpartclsid       | integer
| not nullpicture         | bytea                 |instructions    | bytea                 |
 
..........id              | integer               | not null default 
nextval('public.parts_id_seq'::text)drgno           | character varying(20) |
Indexes:   "parts_pkey" primary key, btree (id)   "parts_ukey" unique, btree (drgno, partno, machtypeclsid)
Foreign-key constraints:   "$2" FOREIGN KEY (partclsid) REFERENCES partclasses(id)   "$1" FOREIGN KEY (machtypeclsid)
REFERENCESmachtypesclasses(id)
 

So, now if they decide, that apart from drgno (drawing number),
a new field "revision" must be added, that will be needed to identify 
"uniquely" the part, along with drgno,partno,machtypeclsid, 
it wouldn't mean a lot of changes.

In other words, with surrogate keys, you eliminate the chance
that your original design was flawed due to lack of important
initial knowledge.

A designer in the majority of cases, gets his input from people
of the subject being modeled.
Often these people fail to have the whole image described
when giving specs, hence all the trouble.

-- 
-Achilleus