Skip site navigation (1) Skip section navigation (2)

Re: surrogate key or not?

From: Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: Kenneth Gonsalves <lawgon(at)thenilgiris(dot)com>
Cc: Michael Glaesemann <grzm(at)myrealbox(dot)com>,Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: surrogate key or not?
Date: 2004-07-23 12:25:05
Message-ID: Pine.LNX.4.44.0407231453090.15184-100000@matrix.gatewaynet.com (view raw or flat)
Thread:
Lists: 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          | character varying(20) | not null
 machtypeclsid   | integer               | not null
 partclsid       | integer               | not null
 picture         | 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) REFERENCES machtypesclasses(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          | character varying(20) | not null
 machtypeclsid   | integer               | not null
 partclsid       | integer               | not null
 picture         | 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) REFERENCES machtypesclasses(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


In response to

Responses

pgsql-sql by date

Next:From: Janning VygenDate: 2004-07-23 13:59:38
Subject: Re: surrogate key or not?
Previous:From: Michael GlaesemannDate: 2004-07-23 11:17:37
Subject: Re: surrogate key or not?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group