From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Kenneth Gonsalves <lawgon@thenilgiris.com>
Cc: Michael Glaesemann <grzm@myrealbox.com>
Cc: Karsten Hilbert <Karsten.Hilbert@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Fri, 23 Jul 2004 15:25:05 +0300 (EEST)
Message-ID: <Pine.LNX.4.44.0407231453090.15184-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <0407231532531I.01150@thenilgiris.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, lawgon@thenilgiris.com, grzm@myrealbox.com, Karsten.Hilbert@gmx.net
Subject: Re: surrogate key or not?
In-Reply-To: <Pine.LNX.4.44.0407231453090.15184-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox