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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
