Data Primary key vs. Artificial (Autonumber) primary key

And memo fields
cannot be used as indexes or searched on

I don't know what you mean but memo fields can be indexed and can be
used in WHERE and HAVING clauses; you can cast them using CSTR() in
SQL code to prevent them from being truncated in certain
circumstances.

Then you're a better man that I, because I could never get them to work for
me in that regard. But that wasn't the first time I've said that, and it
CERTAINLY won't be the last - heh....

Dennis

M.

I always love these discussions, because you have some purists, some
"religious fanatics", and some simpletons; call me a simpleton. The reason I
use "meaningless" primary keys is that it saves me work!!!

When I want to update a record, or select a record with an autonumber PK, I
type:

WHERE ID = 123

If I use a composite PK, I have to type:

WHERE [somefield] = somevalue
AND [someotherfield] = somevalue
AND [athirdfield] = somevalue
AND ....

This seems like a lot of extra work to me, so I avoid it where I can.

It also occurs to me that if you want to use the composite key, then if you
want to use this stuff in a list or combo box, and want to be able to select
the specific record, then you need to include all of the fields necessary for
the PK (which increases the load on your network).

Just my 2 cents!

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

email address is invalid
Please reply to newsgroup only.



"M." wrote:

Dear all,

Although many Microsoft Access books advise to set an autonumber field as
primary index (a so called pseudo primary key),
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)
Employee_Name (full employee name, composite index key2)
composite index SSN + Employee_Name = unique

BlueClaw setup for Employee table
Employee_ID (autonumber, unique index)
SSN (social security number, (composite) primary key1)
Employee_Name (full employee name, (composite) primary key2)

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

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 cascaded update necessary of linked relationship fields in other tables,
because autonumber is only used for linking tables and therefore will never
change.
*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.

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.

Best regards,

M.

I'm not sure why I'm jumping in here and I'm not sure if someone has already
said what I am going to say so I apologize in advance. I also have over 30
years of database design experience and in my mainframe days, all primary
keys were "natural" and many were compound. It took me a few years of using
Access to become converted to the "autonumber as PK camp" but I have. I
find autonumbers extremely easy to work with. Access does have an issue
with autonumbers that are not defined as primary keys so I would NEVER use a
"natural" PK in a table that also had an autonumber. the autonumber would
ALWAYS be the PK and would be used for all relationships. When I have
business rules to satisfy that revolve around uniqueness of "natural" keys,
I use unique indexes.
The benefits of using an autonumber PK a
1. there is never a need to cascade PK updates since the PK is an autonumber
and by definition cannot be changed.
2. Business rules regarding uniqueness can be implemented with unique
indexes.
3. In the cases where one of the "natural" keys may validly be blank, I
don't have an issue with a unique index whereas I would have an issue if I
defined a compound primary key since PKs may not contain null values. SSN
is a good example here (aside from the fact that there are laws prohibiting
its use as a PK) since not everyone will have one (h1b visa holders for one)
and there are only limited cases where a person actually has to provide one
to the requestor.
4. Combo/List boxes require code behind the scenes to work if the unique
identifier is compound.
5. As the hierarchy gets deeper, the compound key grows and joins become
more unwieldy. It is way too easy to omit one field out of 5 in a join with
disastrous results if the error isn't discovered quickly.

The only pro for using autonumber PKs is that a lot of your tables will end
up with lots of numeric columns that you would prefer to view as text when
you open the table. The solution is NOT table level lookups, it is creating
queries that join the tables so you can see the identifying "natural" key
data from the parent table. You only have to create one of these views for
each table so it's not like it is a great deal of work. Then for yourself
(because ONLY you should be viewing tables or queries directly), just
remember to open the query rather than the table when you want to see the
natural key values.

That's my opinion and I'm stickin' to it

"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),
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)
Employee_Name (full employee name, composite index key2)
composite index SSN + Employee_Name = unique

BlueClaw setup for Employee table
Employee_ID (autonumber, unique index)
SSN (social security number, (composite) primary key1)
Employee_Name (full employee name, (composite) primary key2)

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

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 cascaded update necessary of linked relationship fields in other
tables,
because autonumber is only used for linking tables and therefore will
never
change.
*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.

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.

Best regards,

M.

Dear Dale,

You could still use your preference for the (unique) autonumber field in the
WHERE statement (as suggested for defining relations in my example), even if
it's not the primary key. The same goes for the lookup box example. I'm in
the mean time convinced that it's a matter of taste (and maybe of performance
for the Jet engine) to decide between an artificial primary key and a real
data primary key.

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.

If you disagree, please let me know,

regards,

M.

"Dale Fye" wrote:

M.

I always love these discussions, because you have some purists, some
"religious fanatics", and some simpletons; call me a simpleton. The reason I
use "meaningless" primary keys is that it saves me work!!!

When I want to update a record, or select a record with an autonumber PK, I
type:

WHERE ID = 123

If I use a composite PK, I have to type:

WHERE [somefield] = somevalue
AND [someotherfield] = somevalue
AND [athirdfield] = somevalue
AND ....

This seems like a lot of extra work to me, so I avoid it where I can.

It also occurs to me that if you want to use the composite key, then if you
want to use this stuff in a list or combo box, and want to be able to select
the specific record, then you need to include all of the fields necessary for
the PK (which increases the load on your network).

Just my 2 cents!

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

email address is invalid
Please reply to newsgroup only.



"M." wrote:

Dear all,

Although many Microsoft Access books advise to set an autonumber field as
primary index (a so called pseudo primary key),
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)
Employee_Name (full employee name, composite index key2)
composite index SSN + Employee_Name = unique

BlueClaw setup for Employee table
Employee_ID (autonumber, unique index)
SSN (social security number, (composite) primary key1)
Employee_Name (full employee name, (composite) primary key2)

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

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 cascaded update necessary of linked relationship fields in other tables,
because autonumber is only used for linking tables and therefore will never
change.
*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.

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.

Best regards,

M.

Dale Fye wrote:

I always love these discussions, because you have some purists, some
"religious fanatics", and some simpletons; call me a simpleton. The reason I
use "meaningless" primary keys is that it saves me work!!!

chuckle Excellent comment.

When I want to update a record, or select a record with an autonumber PK, I
type:

WHERE ID = 123

If I use a composite PK, I have to type:

WHERE [somefield] = somevalue
AND [someotherfield] = somevalue
AND [athirdfield] = somevalue
AND ....

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.

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/

I am certain that there are some performance issues associated with either
decision.

I generally use the Autonumber field as my PK, but use multi-field unique
indexes, and application level business logic, to prevent "duplicate
entries". As long as you restrict your users access to the raw data, it is
frequently easier to implement the business logic in the application rather
then through table constraints.

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

email address is invalid
Please reply to newsgroup only.



"M." wrote:

Dear Dale,

You could still use your preference for the (unique) autonumber field in the
WHERE statement (as suggested for defining relations in my example), even if
it's not the primary key. The same goes for the lookup box example. I'm in
the mean time convinced that it's a matter of taste (and maybe of performance
for the Jet engine) to decide between an artificial primary key and a real
data primary key.

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.

If you disagree, please let me know,

regards,

M.

"Dale Fye" wrote:

M.

I always love these discussions, because you have some purists, some
"religious fanatics", and some simpletons; call me a simpleton. The reason I
use "meaningless" primary keys is that it saves me work!!!

When I want to update a record, or select a record with an autonumber PK, I
type:

WHERE ID = 123

If I use a composite PK, I have to type:

WHERE [somefield] = somevalue
AND [someotherfield] = somevalue
AND [athirdfield] = somevalue
AND ....

This seems like a lot of extra work to me, so I avoid it where I can.

It also occurs to me that if you want to use the composite key, then if you
want to use this stuff in a list or combo box, and want to be able to select
the specific record, then you need to include all of the fields necessary for
the PK (which increases the load on your network).

Just my 2 cents!

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

email address is invalid
Please reply to newsgroup only.



"M." wrote:

Dear all,

Although many Microsoft Access books advise to set an autonumber field as
primary index (a so called pseudo primary key),
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)
Employee_Name (full employee name, composite index key2)
composite index SSN + Employee_Name = unique

BlueClaw setup for Employee table
Employee_ID (autonumber, unique index)
SSN (social security number, (composite) primary key1)
Employee_Name (full employee name, (composite) primary key2)

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

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 cascaded update necessary of linked relationship fields in other tables,
because autonumber is only used for linking tables and therefore will never
change.
*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.

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.

Best regards,

M.

Amen, brother!

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

email address is invalid
Please reply to newsgroup only.



"Pat Hartman" wrote:

I'm not sure why I'm jumping in here and I'm not sure if someone has already
said what I am going to say so I apologize in advance. I also have over 30
years of database design experience and in my mainframe days, all primary
keys were "natural" and many were compound. It took me a few years of using
Access to become converted to the "autonumber as PK camp" but I have. I
find autonumbers extremely easy to work with. Access does have an issue
with autonumbers that are not defined as primary keys so I would NEVER use a
"natural" PK in a table that also had an autonumber. the autonumber would
ALWAYS be the PK and would be used for all relationships. When I have
business rules to satisfy that revolve around uniqueness of "natural" keys,
I use unique indexes.
The benefits of using an autonumber PK a
1. there is never a need to cascade PK updates since the PK is an autonumber
and by definition cannot be changed.
2. Business rules regarding uniqueness can be implemented with unique
indexes.
3. In the cases where one of the "natural" keys may validly be blank, I
don't have an issue with a unique index whereas I would have an issue if I
defined a compound primary key since PKs may not contain null values. SSN
is a good example here (aside from the fact that there are laws prohibiting
its use as a PK) since not everyone will have one (h1b visa holders for one)
and there are only limited cases where a person actually has to provide one
to the requestor.
4. Combo/List boxes require code behind the scenes to work if the unique
identifier is compound.
5. As the hierarchy gets deeper, the compound key grows and joins become
more unwieldy. It is way too easy to omit one field out of 5 in a join with
disastrous results if the error isn't discovered quickly.

The only pro for using autonumber PKs is that a lot of your tables will end
up with lots of numeric columns that you would prefer to view as text when
you open the table. The solution is NOT table level lookups, it is creating
queries that join the tables so you can see the identifying "natural" key
data from the parent table. You only have to create one of these views for
each table so it's not like it is a great deal of work. Then for yourself
(because ONLY you should be viewing tables or queries directly), just
remember to open the query rather than the table when you want to see the
natural key values.

That's my opinion and I'm stickin' to it

"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),
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)
Employee_Name (full employee name, composite index key2)
composite index SSN + Employee_Name = unique

BlueClaw setup for Employee table
Employee_ID (autonumber, unique index)
SSN (social security number, (composite) primary key1)
Employee_Name (full employee name, (composite) primary key2)

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

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 cascaded update necessary of linked relationship fields in other
tables,
because autonumber is only used for linking tables and therefore will
never
change.
*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.

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.

Best regards,

M.

BTW, I meant con not pro.

"Pat Hartman" please no wrote in message
...
I'm not sure why I'm jumping in here and I'm not sure if someone has
already said what I am going to say so I apologize in advance. I also
have over 30 years of database design experience and in my mainframe days,
all primary keys were "natural" and many were compound. It took me a few
years of using Access to become converted to the "autonumber as PK camp"
but I have. I find autonumbers extremely easy to work with. Access does
have an issue with autonumbers that are not defined as primary keys so I
would NEVER use a "natural" PK in a table that also had an autonumber.
the autonumber would ALWAYS be the PK and would be used for all
relationships. When I have business rules to satisfy that revolve around
uniqueness of "natural" keys, I use unique indexes.
The benefits of using an autonumber PK a
1. there is never a need to cascade PK updates since the PK is an
autonumber and by definition cannot be changed.
2. Business rules regarding uniqueness can be implemented with unique
indexes.
3. In the cases where one of the "natural" keys may validly be blank, I
don't have an issue with a unique index whereas I would have an issue if
I defined a compound primary key since PKs may not contain null values.
SSN is a good example here (aside from the fact that there are laws
prohibiting its use as a PK) since not everyone will have one (h1b visa
holders for one) and there are only limited cases where a person actually
has to provide one to the requestor.
4. Combo/List boxes require code behind the scenes to work if the unique
identifier is compound.
5. As the hierarchy gets deeper, the compound key grows and joins become
more unwieldy. It is way too easy to omit one field out of 5 in a join
with disastrous results if the error isn't discovered quickly.

The only pro for using autonumber PKs is that a lot of your tables will
end up with lots of numeric columns that you would prefer to view as text
when you open the table. The solution is NOT table level lookups, it is
creating queries that join the tables so you can see the identifying
"natural" key data from the parent table. You only have to create one of
these views for each table so it's not like it is a great deal of work.
Then for yourself (because ONLY you should be viewing tables or queries
directly), just remember to open the query rather than the table when you
want to see the natural key values.

That's my opinion and I'm stickin' to it

"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),
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)
Employee_Name (full employee name, composite index key2)
composite index SSN + Employee_Name = unique

BlueClaw setup for Employee table
Employee_ID (autonumber, unique index)
SSN (social security number, (composite) primary key1)
Employee_Name (full employee name, (composite) primary key2)

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

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 cascaded update necessary of linked relationship fields in other
tables,
because autonumber is only used for linking tables and therefore will
never
change.
*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.

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.

Best regards,

M.

Thanks,

That Google portal is a whole new way to look at it.

Fred

"Jamie Collins" wrote:

On Dec 6, 3:11 pm, Fred wrote:
Can someone tell me what this mere mortal is missing regarding how to look at
these long threads?

This is what I see:

http://groups.google.com/group/micro...1e479a6765df2/

Jamie.

--

=?Utf-8?B?TS4=?= wrote in
:

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.

Well, if you're using a surrogate key, that doesn't mean you leave
out the unique indexes on fields and combinations of fields that
should also be unique. However, in many cases, composite keys can't
be unique because you have to allow storage of Nulls. In that case,
your app has to do duplicate checking of some sort. Given that with
things like names, you can't rely on a unique index even when Nulls
are disallowed (because Bob and Robert could still be the same
person), you often end up putting some form of duplicate checking
into your app even when you already have unique indexes on the
fields you're checking.

So, to me, having unique indexes is a question that is answered
field by field (or composite key by composite key), and is wholly
independent of which unique index you happen to use for your primary
key. The unique indexes are a function of the data, while the
primary key is a function of your database structure. One field or
collection of fields may serve both functions, but they don't have
to.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/