Data Primary key vs. Artificial (Autonumber) primary key

Jamie Collins wrote:

Consider if you have a parent and subform based a composite PK?
That's a lot of extra work there figuring out what fields go where.

This implies you design your database schema (tables etc) for the
convenience of your front end (forms and subforms) and/or for your own
convenience?!

That would be "and" not "and/or". And yes that's a small part of the
reason yes.

My approach is to design the database schema entirely
independent of the front ends (which is done by another person
anyhow).

I do everything.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Jamie Collins wrote:

In an ideal world,
surrogates would be implemented under the hood and not even be exposed
to DBA, developers, etc. I encourage people to use the random flavour
of Autonumber to discourage this practice

I disagree. Sometimes when trouble shooting problems it's a lot
easier to remember two or three digit numbers when viewing data from
different tables. I'm using my own test databases with a few or a
few dozen records. Later I'll test against client databases.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

M. wrote:

What still interests me, however, is how pepole prevent entry of duplicate
records when they use a autonumber field as primary index. This choice
doesn't offer you any guarantee that the real data in your record is
accidentally entered a second time in a later record. In my opinion you would
still need a unique single (e.g. ISBN number) or multifield index (e.g. book
title + author name) to prevent duplicate entry. In the end, it's then a
matter of taste whether you name the autonumber field index primary or the
composite field(s) index primary.

Like Dale said I use either unique indexes on other fields if
applicable and which surprisingly often aren't possible. So now you
have to use some business logic and depend on humans.

Book titles can be duplicate (Golden is one example with two different
authors in 2006) as well as having multiple authors, translators,
illustrator, etc.

Humans can have duplicate names and birth dates. Or Fred Jones and
his son Fred Jones live at the same house.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

On Dec 7, 5:37 pm, Dale Fye wrote:
I design my schemas as close to 3rd or 4th normal form as feasible.

Doubtful g. 3NF is a historical anomaly and 4NF is rarely desirable.
I'd wager you actually achieve BCNF and 5NF more often than you
think

But, rather than carry around the excess baggage of a 3, 4, or 5 field PK, I
have elected to take the path that is most efficient given my chosen
front/back tools (Access). However, even when I get the opportunity to take
advantage of the server side processing that SQL Server provides, I still use
a 'meaningless' PKs to speed development (makes my job easier) and minimize
the risk of mistakes (as one of the other posts mentioned, an update query
where one of the joins is missing can be disasterous).

It seems you use a so-called surrogate (what Codd meant by 'surrogate'
is open to debate e.g. when he said users "have no control over its
value, nor is its value ever displayed to them" did he mean DBAs and
Access Developers?) for FK references. Well, that's your design choice
and, assuming you have table constraints (rather than front end code)
to cover your candidate keys, then fair enough; I'd hope you wouldn't
use another's threads to evangelize g. However, note the essence of
the OP's question seems to me to be: given that someone has used
Autonumber as a so-called surrogate, what reasons would they have for
not making the Autonumber the PK? I cordially invite you to attempt to
answer that question.

I ... take
great exception with programmers who kludge their database together based on
their desired form layout.

Then why did you reply to me rather than take great exception with
Tony Toews [MVP] who made the point...?

Don''t forget to rate the post if it was helpful!

....Ah, perhaps I see why ;-)

Jamie.

--

On Dec 8, 7:46 pm, "David W. Fenton"
wrote:
My approach is to design the database schema entirely
independent of the front ends (which is done by another person
anyhow).

I'll bet you have developers cursing you all the time.

We have design reviews where discussion is encouraged. Cursing is not
a great way of raising an objection, IMO.

I learn a lot about how the properties and structure of the entities
represented in database schema in the process of designing the UI.
It's a two-way process, with feedback flowing from the schema design
to the UI and back again.

This is not to say that the schema design is driven by the UI, only
that in designing a UI *for people* one can learn things about the
schema that were not in the spec, or not obvious from it.

If you are saying that your logical model is only as good as the
domain expert's spec then I am in full agreement.

Jamie.

--

On Dec 8, 7:51 pm, "Tony Toews [MVP]" wrote:
In an ideal world,
surrogates would be implemented under the hood and not even be exposed
to DBA, developers, etc. I encourage people to use the random flavour
of Autonumber to discourage this practice

I disagree. Sometimes when trouble shooting problems it's a lot
easier to remember two or three digit numbers when viewing data from
different tables. I'm using my own test databases with a few or a
few dozen records. Later I'll test against client databases.

And if you make it the PK, your client and their users will end up
with the double whammy of poor concurrency and poor clustering. But,
hey, the convenience of the developer is what counts for you, eh g?

Jamie.

--

On Dec 9, 5:04 am, "Tony Toews [MVP]" wrote:
I use either unique indexes on other fields if
applicable and which surprisingly often aren't possible.

That's why we were given table-level CHECK constraints.

Book titles can be duplicate (Golden is one example with two different
authors in 2006) as well as having multiple authors, translators,
illustrator, etc.

I sure wish someone would hurry up and invent an industry standard
identifier with a trusted source which will be familiar to users. Oh,
hang on: they already did (ISBN).

Humans can have duplicate names and birth dates. Or Fred Jones and
his son Fred Jones live at the same house.

Fred Jones's credit card number is unique and he is not supposed to
disclose his PIN number but how do you know it is not his son Fred
Jones using it? Fred Jones has different fingerprints to Fred Jones
but are you authorized to retain that data? It's all about trust i.e.
you need a trusted source for identifiers.

An Autonumber in an Access database cannot verify people in reality
therefore cannot be used as a trusted source. Yes that's a straw man
argument, as are your faulty examples of natural keys.

Jamie.

--

If you want to conduct an intellectual discussion, you really need to be
able to handle it when people disagree with you and stop taking cheap shots.

"Jamie Collins" wrote in message
...
On Dec 8, 7:51 pm, "Tony Toews [MVP]" wrote:
In an ideal world,
surrogates would be implemented under the hood and not even be exposed
to DBA, developers, etc. I encourage people to use the random flavour
of Autonumber to discourage this practice

I disagree. Sometimes when trouble shooting problems it's a lot
easier to remember two or three digit numbers when viewing data from
different tables. I'm using my own test databases with a few or a
few dozen records. Later I'll test against client databases.

And if you make it the PK, your client and their users will end up
with the double whammy of poor concurrency and poor clustering. But,
hey, the convenience of the developer is what counts for you, eh g?

Jamie.

--

Jamie Collins wrote:

I disagree. Sometimes when trouble shooting problems it's a lot
easier to remember two or three digit numbers when viewing data from
different tables. I'm using my own test databases with a few or a
few dozen records. Later I'll test against client databases.

And if you make it the PK, your client and their users will end up
with the double whammy of poor concurrency and poor clustering. But,
hey, the convenience of the developer is what counts for you, eh g?

How does clustering apply to Access databases? And I've only ever had
one problem with concurrency in all these years. So not a problem
for me.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

On Dec 10, 3:32 pm, "Pat Hartman" please no wrote:
If you want to conduct an intellectual discussion
you really need to be
able to handle it when people disagree with you and stop taking cheap shots.

If I really was conducting this discussion I would have answers to my
direct questions (ping Dale Fye: "given that someone has used
Autonumber as a so-called surrogate, what reasons would they have for
not making the Autonumber the PK?")

And if I couldn't handle people around here disagreeing with me I'd
have left years ago!

FYI:

The Access Web: Netiquette
http://www.mvps.org/access/netiquette.htm
Be thoughtful of bandwidth and other folks' thought processes:
[quote] Look for Smileys :-), grins g, and other "Emoticons". When
you see one, the preceding statement was not meant to be really
serious, don't take it as such. [Unquote]

I don't think the poster was being serious either (hence my short
dismissal with a grin). Designing database for one's own convenience
rather than your client's doesn't sound like a credible proposition to
me.

Jamie.

--