Josh Berkus wrote:
> Another tip: beware of over-reliance on surrogate autoincrement
> keys. They are convenient and necessary some of the time, but use
> real keys where you can. The current industry reliance on "ID"
> primary keys encourages sloppy thinking .... and sloppy schema ... by
> DBAs. I have caught myself building tables without any real keys
> this way.
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.
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").
You think that 4 out of 21 is a good score? Should I allow primary key
changes? Since the RDBMS does not support cascade updates, I'd need to
write triggers to do the job, and I would also have to make the
constraints deferred. And I doubt it would apply to more than one or two
entity types.