A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

New Database - Primary Key



 
 
Thread Tools Display Modes
  #1  
Old June 5th, 2009, 07:34 PM posted to microsoft.public.access.tablesdbdesign
Trini Gal
external usenet poster
 
Posts: 20
Default New Database - Primary Key

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.
  #2  
Old June 5th, 2009, 08:17 PM posted to microsoft.public.access.tablesdbdesign
Armen Stein
external usenet poster
 
Posts: 507
Default New Database - Primary Key

On Fri, 5 Jun 2009 11:34:01 -0700, Trini Gal
wrote:

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

  #3  
Old June 5th, 2009, 08:18 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default New Database - Primary Key

In message , Trini
Gal writes
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?


--
Bernard Peek
  #4  
Old June 5th, 2009, 08:58 PM posted to microsoft.public.access.tablesdbdesign
Trini Gal
external usenet poster
 
Posts: 20
Default New Database - Primary Key

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,

"Bernard Peek" wrote:

In message , Trini
Gal writes
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?


--
Bernard Peek

  #5  
Old June 5th, 2009, 09:27 PM posted to microsoft.public.access.tablesdbdesign
Michael Gramelspacher
external usenet poster
 
Posts: 482
Default New Database - Primary Key

On Fri, 5 Jun 2009 12:58:01 -0700, Trini Gal wrote:

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,

"Bernard Peek" wrote:

In message , Trini
Gal writes
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?


--
Bernard Peek

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.
  #6  
Old June 6th, 2009, 06:03 AM posted to microsoft.public.access.tablesdbdesign
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default New Database - Primary Key

Trini Gal wrote:

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
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/
  #7  
Old June 6th, 2009, 06:43 PM posted to microsoft.public.access.tablesdbdesign
Armen Stein
external usenet poster
 
Posts: 507
Default New Database - Primary Key

On Fri, 5 Jun 2009 12:58:01 -0700, Trini Gal
wrote:

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

  #8  
Old June 8th, 2009, 01:35 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default New Database - Primary Key

Armen Stein wrote in
:

On Fri, 5 Jun 2009 12:58:01 -0700, Trini Gal
wrote:

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.


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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #9  
Old June 8th, 2009, 04:00 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default New Database - Primary Key

In message , Trini
Gal writes
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.




--
Bernard Peek
  #10  
Old June 9th, 2009, 06:02 AM posted to microsoft.public.access.tablesdbdesign
Armen Stein
external usenet poster
 
Posts: 507
Default New Database - Primary Key

On 8 Jun 2009 00:35:46 GMT, "David W. Fenton"
wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.