On Dec 8, 7:43 pm, "David W. Fenton"
wrote:
Jamie Collins wrote
:

I broadly agree with everything you've said here. I do still think
that many (perhaps most) users of Autonumber columns use it for their
one and only unique constraint per table; for the record, I think such
people are ill informed rather than stupid. Some should now be better
informed after reading your post

One point:

in a lot of situations, forcing someone to use
the natural key can lead to all sorts of other problems, like
default values to avoid Nulls, and then you have to program around
those (e.g., you won't be able to use this to concatenate the name
fields: Mid(("12" + [LastName]) & (", " + [FirstName]),3), because
with default values, none of your "empty" fields will be Null).

I don't design my schemas to fit your ready-rolled formulas g!. I
have no doubt that if I define my middle_name column as NOT NULL with
a DEFAULT of '{{NONE}}' (plus CHECK constraints to disallow the empty
string, spaces, etc) then you would be able to come up with a similar
formula to derive a full name. My create/update stored procs handle
the NULL value and the empty string so it shouldn't be too onerous on
the 'front end guy' to replace '{{NONE}}', '{{NK}}', etc, with
whatever is appropriate in context.

That said, person's name rarely makes a good key, so is not IMO a good
example of a natural key. My favourite is the ISBN: industry standard
(ask at your local public library or look at the URLs on Amazon),
trusted source (will resolve rare cases of duplication), familiar and
verifiable in reality (take a look at the bar code on the back cover
of your favourite book), stable (sure, they have recently changed from
10 to 13 characters but this has been managed by the trusted source to
provide an upgrade path), fixed length with a check digit (easy to
validate at time of keying in), a so-called 'intelligent' key (it
comprises detail of region, publisher, etc) and so on.

Jamie.

--