I may have misunderstood your post. I got the impression you were looking
to compare artificial vs. natural primary keys, trying to determine a "best
practice" approach.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"M." wrote in message
...
Thanks for your reply, but unfortunately this doesn't answer my question.
I'm
sorry to distract you with minor issues, like the SSN and name fields.
These
were copied from the Blue Claw example on their website. Of course I'm
aware
of full name issues, but that's not my question.

My main question is: are there negative aspects associated with using a
primary key based on data fields versus using a primary key based on an
artificial primary key as generated with an autonumber field? In both
cases
the autonumber field would be used for defining relations between tables.

Until now my answer would be: there are no negative aspects associated
with
the data fields approach.

Best regards,

M.


"Jeff Boyce" wrote:

You may have just inflamed a long-running religious war about proper
primary
keys.g

See comments in-line below...

"M." wrote in message
...
Dear all,

Although many Microsoft Access books advise to set an autonumber field
as
primary index (a so called pseudo primary key),

"psuedo" implies "not real" -- a primary key is a unique identifier, no
matter where it comes from. It isn't a question of "real".

http://www.blueclaw-db.com/database_link_tables.htm advises to use
real
data
to define a (composite) primary key. In summary, this results in the
following two designs:

Microsoft Acces books setup for Employee table
Employee_ID (autonumber, primary key)
SSN (social security number, composite index key1)

you will want to be very careful about capturing/displaying SSNs.
Moreover,
not every "person" has (or cares to share) one. How will you handle a
Null
SSN?

Employee_Name (full employee name, composite index key2)

No, no no! If you combine more than one fact in a single field, you
have to
work extra hard to do simple things, like, say, sort by LAST NAME! Use
FirstName and LastName fields, then use a query to concatenate them when
needed.

composite index SSN + Employee_Name = unique

can you say "identity theft"? what makes you think that SSN +
Employee_Name
will be unique?


BlueClaw setup for Employee table
Employee_ID (autonumber, unique index)

Why? By definition, an Autonumber is supposed to already be unique, so
you
wouldn't gain anything by indexing it.

SSN (social security number, (composite) primary key1)

(see above)

Employee_Name (full employee name, (composite) primary key2)

(see above)


In both approaches, Employee_ID would be used as a foreign key in
other
tables to define the relationship with the Employee table.

If you go to the effort of creating a composite primary key, then why
would
you not also go to the effort to "migrate" that key (i.e., all fields)
to
the "child" tables?


Are there any negative aspects associated with the BlueClaw approach?

Pros of BlueClaw approach
*Display of table is meaningful, because it's sorted on primary index

No, NO, NO!! Access tables store data, Access forms (and reports)
display
it. Using Access tables to display data is asking for trouble! (can
you
tell I have some strong feelings on this topic?g - check this
newsgroup
for others' ideas about using tables to display data. From experience,
I
don't want inexperienced users mucking about directly in my tables.
Instead, I'll guide their use of the data via forms. This is a major
difference between, say, Word {everyone knows how to move words around}
and
Access, a relational database {how many normal people understand
relational
database design?})

And "meaningful"?! To whom? Just because a set of data is sorted in
one
order doesn't mean that EVERYONE wants to see it in that order. I, for
one,
prefer to see a list of employees sorted by last name when I'm
considering
Human Resources activities, but by firstname when I'm looking for their
phone numbers.

*No cascaded update necessary of linked relationship fields in other
tables,
because autonumber is only used for linking tables and therefore will
never
change.

The implication is that the SSN and Employee_Name MAY change. So what?
There's next to zero effort required to set Cascading Updates when you
set
the relationships among tables. And while an Autonumber may not change,
you
can re-record a row of data and get a NEW autonumber, then delete the
old
record. Where's your foreign key now?!

*Prevention of duplicates is improved, since data fields are used to
check
for duplicates, instead of an (always unique) autonumber field this
can
also be achieved with the composite unique index as shown above in the
Access
books example.

Ahem! ?"Duplicates"? Are the following employees the same person:
John Doe
J. J. Doe
And what about John and his son John, who both work for your company,
both
live at the same address, and both have the same last name.
?Duplicates?!


Cons of BlueClaw approach
*???

I would appreciate your comments / opinion on the BlueClaw approach,
because
I currently have the feeling that I'm missing something that explains
why
so
many people use autonumber fields as primary (artificial) key. If the
BlueClaw approach is the best one, I'm considering to use it as a
standard
in
new database design questions.

JOPO (just one person's opinion) Any approach to this that claims to be
the
one and only appropriate way to do this is probably wrong! Use what
works
for you.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Best regards,

M.