|
On 02/04/13 08:35, jesusthefrog wrote:
On the topic of 'natural' versus 'synthetic' primary
keys, I am generally in the camp that an extra ID field
won't cost you too much, and while one may not need it for a
simple table (i.e. id, name) one might add any number of
columns later, and you'll be glad to have it.
I am, however, against using sequences (or serial integers in
Postgres) for reasons of scaling and replication across
multiple copies of a database running on different servers.
My preferred method is to give every table an ID column of UUID
type and generate a UUID using the uuid-ossp contrib module.
This also prevents someone not familiar with the database design
from using an ID somewhere they should not (as is possible with
natural PKs) or treating the ID as an integer, not an identifier
(as is all too common with serial integers).
I use synthetic primary keys, I want to minimise changes to the database because the user, directly or
due to of some law change,
changes a 'natural' value. Using synthetic primary
keys, minimises changes to a database when a 'natural' value
changes - if people's names are part of
many natural keys, then when people change their name (like when a woman gets married), only one table needs to
change. Likewise the customer nunber the manager swore would never
change, now they want to change for a numeric key to an
alphanumeric one.
Using 'natural' values for a primary key, seems to be deliberately
adding potential time bombs. Almost as bad as the
misguided idea that using 'sudo' is safer than the
alternative when executing commands a root!
Cheers,
Gavin
|
