New Database - Primary Key

[Trini Gal]

Hello,

I've read a lot of the threads referencing Primary Key and I need a little
help. I'm creating a database for my company to track changes to channels.
This database will be accessed in different regions throughout the company
(possibly sometimes at the same time). I need to figure out how to create a
Primary Key for one of my tables. I've figure out how to use the DMax
function to increment #s, but if two users try to enter a new record at the
same exact time, one of them won't be able to save that record because the
one before got the #. I know this because its happened before.

Thanks.

 

[Armen Stein]

I've read a lot of the threads referencing Primary Key and I need a little
help. I'm creating a database for my company to track changes to channels.
This database will be accessed in different regions throughout the company
(possibly sometimes at the same time). I need to figure out how to create a
Primary Key for one of my tables. I've figure out how to use the DMax
function to increment #s, but if two users try to enter a new record at the
same exact time, one of them won't be able to save that record because the
one before got the #. I know this because its happened before.

Hi Trini,

Is there a reason that you aren't using an AutoNumber? Then Access
will manage it for you. However, you need to be okay with having gaps
in the sequence.

If you need your own method, then as you discovered the DMax method
does have this concurrency problem.

Alternatively, you can keep a "Next Available" number in another
configuration table, then write a small VBA function that opens the
recordset for update (locking it), incrementing the number, then
updating the record again. This ensures that only one user can
increment at a time.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Bernard Peek]

Hello,

I've read a lot of the threads referencing Primary Key and I need a little
help. I'm creating a database for my company to track changes to channels.
This database will be accessed in different regions throughout the company
(possibly sometimes at the same time). I need to figure out how to create a
Primary Key for one of my tables. I've figure out how to use the DMax
function to increment #s, but if two users try to enter a new record at the
same exact time, one of them won't be able to save that record because the
one before got the #. I know this because its happened before.

If your database is going to be accessed by lots of people
simultaneously then it's worth considering whether Access is the right
tool for the job. Depending on the usage pattern you may want to
consider using a client-server database like SQL Server which can wrap
multiple updates into a transaction.

If all you want is a unique ID then an autonumber field will do the job.
Don't your tables have natural keys?

 

[Trini Gal]

Bernard and Armen,

Thank you for answering. Honestly, I have read so much about why I should
and shouldn't be using the AutoNumber, that I just didn't use it and used the
DMax function instead. I don't know anything about the SQL Server, I only
know a little Access. I don't know what you mean by "natural key".

This is what I have right now:

Channel_ID (incrementing #, say 9 is the next #)
Division (NC, SC)
Region (Manhattan, Bronx, Brookly, Queens)

So when the user selects the Division and Region, the File_Name eg
SC_Bronx_9, which is a concatenation of all three fields, which I have as the
Primary Key.

Was that a good decision? Thats where I need your input. I read some
threads where there are like 2 and 3 primary keys, but I didn't quite
understand that.

Thanks,

 

[Michael Gramelspacher]

Bernard and Armen,

Thank you for answering. Honestly, I have read so much about why I should
and shouldn't be using the AutoNumber, that I just didn't use it and used the
DMax function instead. I don't know anything about the SQL Server, I only
know a little Access. I don't know what you mean by "natural key".

This is what I have right now:

Channel_ID (incrementing #, say 9 is the next #)
Division (NC, SC)
Region (Manhattan, Bronx, Brookly, Queens)

So when the user selects the Division and Region, the File_Name eg
SC_Bronx_9, which is a concatenation of all three fields, which I have as the
Primary Key.

Was that a good decision? Thats where I need your input. I read some
threads where there are like 2 and 3 primary keys, but I didn't quite
understand that.

Thanks,

Is channel_id something real, as perhaps a television channel number?

In that case the natural primary key is
PRIMARY KEY (channel_id, division, region).

If you go with an autonumber as a primary key, then make that
UNIQUE (channel_id, division, region).

Do not concatenate the three values together in the table. if you need then
concatenated, then do it in a query.

 

[Tony Toews [MVP]]

Thank you for answering. Honestly, I have read so much about why I should
and shouldn't be using the AutoNumber, that I just didn't use it and used the
DMax function instead.

FWIW I've been using autonumber keys for three decades both in Access
and previous environments. Some of my systems are in use by 25 and
30 users all day long. And I can't recall any problems with using
autonumbers.

Tony

 

[Armen Stein]

Thank you for answering. Honestly, I have read so much about why I should
and shouldn't be using the AutoNumber, that I just didn't use it and used the
DMax function instead.

Whether to use AutoNumber keys (aka Identity in SQL Server) is
somewhat of a religious debate. They are "meaningless surrogate
keys", as opposed to "natural keys". There are proponents on both
sides. I am on the AutoNumber side, and like Tony and others I've
been on that side for decades. My shop has built hundreds of
databases using this approach.

I don't know anything about the SQL Server, I only
know a little Access. I don't know what you mean by "natural key".

A natural key is a field (or group of fields) of real world data that
uniquely identify one row in the table. No duplicates allowed. And
the field(s) must always have a value - no nulls allowed. Also, it's
best if the key value never changes.

Proponents of AutoNumber (like me) say that it is difficult to find
truly unique natural keys like that, and that combination keys are
unwieldy to work with when relationships to child tables are involved.
So we stick with a single AutoNumber key in every table - problem
solved.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

Whether to use AutoNumber keys (aka Identity in SQL Server) is
somewhat of a religious debate. They are "meaningless surrogate
keys", as opposed to "natural keys". There are proponents on both
sides. I am on the AutoNumber side, and like Tony and others I've
been on that side for decades. My shop has built hundreds of
databases using this approach.

A programmically-incremented PK is no less a surrogate key than an
Autonumber PK. It's just more work to make reliable.

There is really no need to programmatically increment your PK unless
you really do need the numbers to be incremented because you are
exposing them to the users (e.g., an invoice number). Most
properly-designed Access applications don't need that, so
Autonumbers are fine.

 

[Bernard Peek]

Bernard and Armen,

Thank you for answering. Honestly, I have read so much about why I should
and shouldn't be using the AutoNumber, that I just didn't use it and used the
DMax function instead. I don't know anything about the SQL Server, I only
know a little Access. I don't know what you mean by "natural key".

A natural key is a unique identifier that is derived from the data
itself rather than being added later. If there is a field or combination
of fields that uniquely identifies a record then that is a natural key.
If you don't have a natural key in each of your tables then you may have
to do some more analysis.

Access is a very nice tool for some jobs but it has limitations. It's
not suitable for applications where lots of people need frequent access
to the data. For those types of applications SQL Server is more
appropriate.

This is what I have right now:

Channel_ID (incrementing #, say 9 is the next #)
Division (NC, SC)
Region (Manhattan, Bronx, Brookly, Queens)

So when the user selects the Division and Region, the File_Name eg
SC_Bronx_9, which is a concatenation of all three fields, which I have as the
Primary Key.

Was that a good decision? Thats where I need your input. I read some
threads where there are like 2 and 3 primary keys, but I didn't quite
understand that.

There must be only one primary key in any table but it can be made up
from more than one column. The primary key has to be unique but that
doesn't mean that the individual components have to be unique. It just
means that any possible combination of the values in the component
fields must not occur more than once and none of the components can ever
be null.

In database theory the field or combination of fields that uniquely
identify the record are referred to as the primary key. If you add an
autonumber field you would call it a surrogate key. But Access refers to
an autonumber field as a primary key, which I consider to be a serious
flaw in its design.

So in any table you need a primary key. You can add a surrogate key as
well if you want, but you still need that real primary key as well.

 

[Armen Stein]

A programmically-incremented PK is no less a surrogate key than an
Autonumber PK. It's just more work to make reliable.

That's true. But often one's desire to control the key means that one
is attributing some meaning to it.

There is really no need to programmatically increment your PK unless
you really do need the numbers to be incremented because you are
exposing them to the users (e.g., an invoice number). Most
properly-designed Access applications don't need that, so
Autonumbers are fine.

I agree. And even in the rare instance when we expose the PK to the
user, we call it something like a "reference number" and let them know
that it's merely a unique number, and not to worry about gaps.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

Hi Bernard,

If you add an
autonumber field you would call it a surrogate key.

Yes, an AutoNumber can act as a surrogate key with automatically
generated values.

But Access refers to
an autonumber field as a primary key, which I consider to be a serious
flaw in its design.

Not really. Access supports a table having a primary key, which is a
field (or combination of fields) that uniquely identifies each record.

And Access supports an AutoNumber datatype, which is a Long Integer
assigned unique values automatically. However, you do not *have* to
make it the primary key, although that's normally how it's used. You
can have one field (or fields) as a primary key, and another non-PK
AutoNumber field. So they are *not* the same thing, and Access
doesn't refer to them as such. Access will offer to create a PK using
an AutoNumber if you try to save a table without a PK, but that's just
it trying to help. It doesn't claim that they're the same thing.

So Access does not have the "serious flaw" you're referring to.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tony Toews [MVP]]

Access is a very nice tool for some jobs but it has limitations. It's
not suitable for applications where lots of people need frequent access
to the data. For those types of applications SQL Server is more
appropriate.

And when the app reaches the point where SQL Server becomes viable you
only need to move the data across and do some tweaking in Access and
SQL Server. And keep on going using all the time and effort you've
invested in Access.

Tony

 

[Bernard Peek]

Hi Bernard,


Yes, an AutoNumber can act as a surrogate key with automatically
generated values.


Not really. Access supports a table having a primary key, which is a
field (or combination of fields) that uniquely identifies each record.

It's not that simple unfortunately. The primary key is *always* made up
of attributes in the underlying entity and this does not include the
autonumber field. Access should identify the data field(s) as the
primary key and the autonumber field as a surrogate key. Both primary
and surrogate keys uniquely identify a record and either can be used to
implement joins. By merging the two ideas Access makes it more difficult
than it should be to follow good practise. But that's probably an
unrealistic expectation for a tool that's designed for people with no
formal training in database design.

 

[Bernard Peek]

And when the app reaches the point where SQL Server becomes viable you
only need to move the data across and do some tweaking in Access and
SQL Server. And keep on going using all the time and effort you've
invested in Access.

Indeed. Even when I have built a production database in SQL Server I am
likely to use Access as a tool for database maintenance.

 

[Trini Gal]

Thank you all for all of your information. I really learned a lot from your
post and they cleared up a lot of questions I had about the Primary Key.

Thanks Again.

 

[Armen Stein]

It's not that simple unfortunately. The primary key is *always* made up
of attributes in the underlying entity and this does not include the
autonumber field. Access should identify the data field(s) as the
primary key and the autonumber field as a surrogate key. Both primary
and surrogate keys uniquely identify a record and either can be used to
implement joins.

Hi Bernard,

It seems your definition of "primary key" is narrower than mine. In
Access you can choose what your primary key is. It can be a natural
data field (or combination of them). Or it can be an AutoNumber. Or
both, although there's no real reason to do that.

The writeup at http://en.wikipedia.org/wiki/Surrogate_key is pretty
informative. We consistently use surrogate keys as primary keys, as
we feel the advantages outweigh the disadvantages.

By merging the two ideas Access makes it more difficult
than it should be to follow good practise. But that's probably an
unrealistic expectation for a tool that's designed for people with no
formal training in database design.

Well, it is designed for an easier initial learning curve. But we've
been developing custom database applications using Access, while
following good database design practices, for over 15 years. I have
no problem with Access's implementation of AutoNumbers and primary
keys.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Bernard Peek]

Hi Bernard,

It seems your definition of "primary key" is narrower than mine. In
Access you can choose what your primary key is. It can be a natural
data field (or combination of them). Or it can be an AutoNumber. Or
both, although there's no real reason to do that.

I've spent quite a lot of time in the comp.databases.theory newsgroup
where terminology tends to be used very carefully. In fact some of the
people there want to abolish the term and just use "key" so that it's
clear that a table may have more than one key (even though that's
generally seen as bad practise.)

The writeup at http://en.wikipedia.org/wiki/Surrogate_key is pretty
informative. We consistently use surrogate keys as primary keys, as
we feel the advantages outweigh the disadvantages.

I often use surrogate keys too, but only after I've established that
there isn't a real primary key that I can use. There were once very good
arguments for using surrogate keys even if a table had a real primary
key, but I believe that they have mostly gone away.

Well, it is designed for an easier initial learning curve. But we've
been developing custom database applications using Access, while
following good database design practices, for over 15 years. I have
no problem with Access's implementation of AutoNumbers and primary
keys.

For the benefit of those that have tuned in late to this particular
debate before, many practitioners consider that using a surrogate key
when a real primary key is available is bad practise. The argument is
that you still need to maintain a unique index on the real data and
keeping a second one on the autonumber just adds processing overhead.
There's a countervailing argument that using a small field like a
long-integer for creating joins is more efficient. Personally I doubt
that either effect is particularly strong when considering the type of
databases that are commonly built using Access.

My personal view is that adding a surrogate key adds unnecessary
complexity and another point of failure, so I discourage it. And as I'm
the boss it's my opinion that counts.

 

[David W. Fenton]

The primary key is *always* made up
of attributes in the underlying entity and this does not include
the autonumber field.

You're just restating the old natural key vs. surrogate question as
though there is a right answer to the question. There isn't. You may
*prefer* natural keys, but that's only a preference.

No defender of natural keys has ever been able to answer the
question of how you define a natural key for a table storing data
about people without populating the fields with useless default
values (to avoid the Null problem, i.e., PKs can't have Nulls in any
of the fields).

 

[Bernard Peek]

You're just restating the old natural key vs. surrogate question as
though there is a right answer to the question. There isn't. You may
*prefer* natural keys, but that's only a preference.

No defender of natural keys has ever been able to answer the
question of how you define a natural key for a table storing data
about people without populating the fields with useless default
values (to avoid the Null problem, i.e., PKs can't have Nulls in any
of the fields).

In the theory newsgroup I postulated a primary key consisting of the
latitude, longitude, altitude and time of birth. I didn't claim that it
was very practical.

There are lots of situations where there isn't any alternative to
surrogate keys, in fact the majority of tables I have come across don't
have a good natural key. But in any system that I manage the DBA will
have to justify the decision to use surrogates on a case-by-case basis.

 

[David W. Fenton]

I've spent quite a lot of time in the comp.databases.theory
newsgroup

They are idiots in that group. None of them really work with actual
client data, so far as I can tell. Celko is one of the worst
blowhards -- a smart man, but doesn't really understand the real
world.

Theory is a starting place for designing a real-world app. It is not
the end point.