Re: Surrogate vs natural keys (Was: Almost relational PostgreSQL (was: one-to-one))

Antonios,

> Speaking of this, I'm finishing the design of a database and I tried to
> follow this rule, but there were very few cases where I could. For
> example, I have a table with device types, the key to which could be the
> composite (manufacturer [a numeric id], modelname [a string]). I didn't
> want to use that, however, because the user might type the wrong
> modelname and later want to correct it. Not to mention that I've
> frequently seen a different model name on the cover and on the label of
> some machines.

Yes, this is exactly the problem with trying to use real keys consistently.

> I decided to use natural primary keys only for four entity types (I have
> 21 in total): languages, countries, states of the US, and
> mime types (examples of keys are, respectively, "en", "UK", "CA",
> "image/jpeg").

All reference lists. This is frequently how it goes.

> You think that 4 out of 21 is a good score? Should I allow primary key
> changes?

It's not about scores, and most of use are forced to use autonumber surrogate
keys a lot of the time to simplify database design. The issue is to beware
of the "surrogate key trap" where you grow so used to autonumber keys that
you create a table with no *real* keys. The problem with tables with no
real keys is that it's impossible to detect duplicates ....

Also, keep in mind that the term "primary key" is inherently meaningless, and
exists only as a convenience for legacy database developers and database
guis. Within the world of relational design ... and relational calculus ...
there are only keys, which are all "equal".

> Since the RDBMS does not support cascade updates,

We don't? That's news to me.

--
-Josh Berkus
Aglio Database Solutions
San Francisco