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  

Autonumber Fields



 
 
Thread Tools Display Modes
  #1  
Old September 22nd, 2005, 12:13 PM
Denis
external usenet poster
 
Posts: n/a
Default Autonumber Fields

Access allows a table to have an autonumber field which could be considered a
record number.

In an application I am involved in developing there are a number of code
tables which are using the autonumber field as the code. This is autonumber
code is then used in the tables holding the data.

I am seeking opinions on this approach. Is this a reasonable practice? What
are the dangers of doing this?

I come from a mainframe environment where this sort of approach is avoided
by generating a unique code value which is not an effective record number as
a record is added.
--
Denis
  #2  
Old September 22nd, 2005, 12:38 PM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

Denis wrote:
Access allows a table to have an autonumber field which could be
considered a record number.

In an application I am involved in developing there are a number of
code tables which are using the autonumber field as the code. This is
autonumber code is then used in the tables holding the data.

I am seeking opinions on this approach. Is this a reasonable
practice? What are the dangers of doing this?

I come from a mainframe environment where this sort of approach is
avoided by generating a unique code value which is not an effective
record number as a record is added.


An AutoNumber is also a generated value that is NOT an effective record number.
Its only guarantee is uniqueness, not a gapless progression of incrementing
numbers.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



  #3  
Old September 22nd, 2005, 01:23 PM
Roger Carlson
external usenet poster
 
Posts: n/a
Default

Autonumber fields make excellent Primary Keys. That's what they were
designed for, and as Rick said, they are not designed to be a record number.
They are used only to create a guaranteed unique value for relating tables.
I use them in every database and highly recommend their use.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L



"Denis" wrote in message
...
Access allows a table to have an autonumber field which could be

considered a
record number.

In an application I am involved in developing there are a number of code
tables which are using the autonumber field as the code. This is

autonumber
code is then used in the tables holding the data.

I am seeking opinions on this approach. Is this a reasonable practice?

What
are the dangers of doing this?

I come from a mainframe environment where this sort of approach is avoided
by generating a unique code value which is not an effective record number

as
a record is added.
--
Denis



  #4  
Old September 22nd, 2005, 01:40 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

Denis

As Rick & Roger point out, autonumbers are designed to serve as unique row
identifiers. If this matches your definition of "record number", fine. You
can find some folks who are willing to expose autonumbers to their users,
but I find them (the autonumbers, not the users or the folks) unfit for
human consumption.

I make extensive use of tables of lookup values, and, where suitable, use
autonumbers IDs on those tables.

JOPO (just one person's opinion)

Jeff Boyce
Access MVP

"Denis" wrote in message
...
Access allows a table to have an autonumber field which could be

considered a
record number.

In an application I am involved in developing there are a number of code
tables which are using the autonumber field as the code. This is

autonumber
code is then used in the tables holding the data.

I am seeking opinions on this approach. Is this a reasonable practice?

What
are the dangers of doing this?

I come from a mainframe environment where this sort of approach is avoided
by generating a unique code value which is not an effective record number

as
a record is added.
--
Denis


  #5  
Old September 23rd, 2005, 08:48 AM
external usenet poster
 
Posts: n/a
Default


Roger Carlson wrote:
Autonumber fields make excellent Primary Keys.


You've misunderstood what PRIMARY KEY means. An unique integer which
has no meaning in respect fo the entities being modelled makes a lousy
PRIMARY KEY. Google for "clustered index" in the Access groups.

An autonumber is a convenient uniqueifier but unquieness for its own
sake make not be such a good thing.

  #6  
Old September 23rd, 2005, 12:39 PM
BruceM
external usenet poster
 
Posts: n/a
Default

That you disagree with somebody does not make that person wrong. Roger has
provided a wide range of assistance in this forum, and has made samples
available on his web site. Based on his track record I would be inclined to
follow his advice. If you are trying to convert people to the idea of using
clustered indexes, a very basic discussion of what they are would be most
helpful. I have taken your suggestion to look at Google groups. There is
indeed a lot of discussion, but I have not yet found how I would create a
clustered index if I wanted to. My databases with a few thousand records
seem to work just fine. Why would I want to put extra effort into something
that already works well? I know you have posted code that includes MAKE
TABLE or some such, but the utility of such code is not clear. The other
thing I noted in Google groups is that most of the discussion of clustered
indexes seems to be in discussions about SQL server.

wrote in message
oups.com...

Roger Carlson wrote:
Autonumber fields make excellent Primary Keys.


You've misunderstood what PRIMARY KEY means. An unique integer which
has no meaning in respect fo the entities being modelled makes a lousy
PRIMARY KEY. Google for "clustered index" in the Access groups.

An autonumber is a convenient uniqueifier but unquieness for its own
sake make not be such a good thing.



  #7  
Old September 23rd, 2005, 03:15 PM
Craig Alexander Morrison
external usenet poster
 
Posts: n/a
Default

Jet 4.0 and 3.5 (and earlier versions) cluster on the Primary Key and a
Compact will keep it managed.

Indeed a clue to this is the Registry entry for:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engi nes.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes

both contain the setting CompactByPKey.

I am not sure what would happen if you changed the above setting from 1, I
expect 0 would skip the clustering - I am not sure if any other setting
would be valid.

SQL Server generally clusters on the Primary Key, however, you can select
another index.

AutoNumbers are very poor devices to truly define a unique record in the
real world, You can enter the name John Doe 1,000,000 times in your database
if the Primary Key is an AutoNumber and you have failed to do something to
prevent the creation of 1,000,000 John Doe's. You may have 1,000,000 unique
records but so what?

Recommending the AutoNumber as Primary Key without pointing out the dangers,
and suggesting the definition and declaration of the natural key (should one
exist), is unwise.

BTW A clustered index is merely a physical ordering of the records in a
table in the database file. Using the true natural key (should one exist) as
the primary key will ensure that all the records with a similar PK will be
physically located next to each other. Using an AutoNumber (sequential
order) as PK will mean the records are clustered according to their creation
order. Using IDENTITY and AutoNumber as PK defeats the purpose of PK, this
is not so bad in SQL Server as it allows you to choose something more
sensible if you have an IDENTITY field in use as PK.


--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"BruceM" wrote in message
...
That you disagree with somebody does not make that person wrong. Roger
has provided a wide range of assistance in this forum, and has made
samples available on his web site. Based on his track record I would be
inclined to follow his advice. If you are trying to convert people to the
idea of using clustered indexes, a very basic discussion of what they are
would be most helpful. I have taken your suggestion to look at Google
groups. There is indeed a lot of discussion, but I have not yet found how
I would create a clustered index if I wanted to. My databases with a few
thousand records seem to work just fine. Why would I want to put extra
effort into something that already works well? I know you have posted
code that includes MAKE TABLE or some such, but the utility of such code
is not clear. The other thing I noted in Google groups is that most of
the discussion of clustered indexes seems to be in discussions about SQL
server.

wrote in message
oups.com...

Roger Carlson wrote:
Autonumber fields make excellent Primary Keys.


You've misunderstood what PRIMARY KEY means. An unique integer which
has no meaning in respect fo the entities being modelled makes a lousy
PRIMARY KEY. Google for "clustered index" in the Access groups.

An autonumber is a convenient uniqueifier but unquieness for its own
sake make not be such a good thing.









  #8  
Old September 23rd, 2005, 03:29 PM
Craig Alexander Morrison
external usenet poster
 
Posts: n/a
Default

Using IDENTITY and AutoNumber as PK defeats the purpose of PK, this
is not so bad in SQL Server as it allows you to choose something more
sensible if you have an IDENTITY field in use as PK.



When I wrote that it defeats the purpose of the PK it should have read it
defeats the purpose of clustering on the PK unless the creation order is the
one you want to physically order your records.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"Craig Alexander Morrison"
wrote in message ...


  #9  
Old September 23rd, 2005, 03:55 PM
BruceM
external usenet poster
 
Posts: n/a
Default

Thank you for the explanation. It makes sense that it has to do with
physical ordering in a table rather than on the disk. Having said that, I
cannot discover the connection between indexes, the table's Order By
property, and anything else that suggests an order within the table, on the
actual order of records in the table. Order By, in particular, seems to
accomplish nothing.
Regarding John Doe, it may well be a name used by more than one person. How
does this fit in with clustered indexes? I may need duplication in that
field.
Suppose I wanted to create a clustered index in an Access table. How would
I do that? The term does not appear in Access Help, and discussions of the
subject tend to assume the reader knows what a clustered index is and how to
create one. Even if one is created, what benefits will I notice?

"Craig Alexander Morrison"
wrote in message ...
Jet 4.0 and 3.5 (and earlier versions) cluster on the Primary Key and a
Compact will keep it managed.

Indeed a clue to this is the Registry entry for:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engi nes.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes

both contain the setting CompactByPKey.

I am not sure what would happen if you changed the above setting from 1, I
expect 0 would skip the clustering - I am not sure if any other setting
would be valid.

SQL Server generally clusters on the Primary Key, however, you can select
another index.

AutoNumbers are very poor devices to truly define a unique record in the
real world, You can enter the name John Doe 1,000,000 times in your
database
if the Primary Key is an AutoNumber and you have failed to do something to
prevent the creation of 1,000,000 John Doe's. You may have 1,000,000
unique
records but so what?

Recommending the AutoNumber as Primary Key without pointing out the
dangers,
and suggesting the definition and declaration of the natural key (should
one
exist), is unwise.

BTW A clustered index is merely a physical ordering of the records in a
table in the database file. Using the true natural key (should one exist)
as
the primary key will ensure that all the records with a similar PK will be
physically located next to each other. Using an AutoNumber (sequential
order) as PK will mean the records are clustered according to their
creation
order. Using IDENTITY and AutoNumber as PK defeats the purpose of PK, this
is not so bad in SQL Server as it allows you to choose something more
sensible if you have an IDENTITY field in use as PK.


--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"BruceM" wrote in message
...
That you disagree with somebody does not make that person wrong. Roger
has provided a wide range of assistance in this forum, and has made
samples available on his web site. Based on his track record I would be
inclined to follow his advice. If you are trying to convert people to
the
idea of using clustered indexes, a very basic discussion of what they are
would be most helpful. I have taken your suggestion to look at Google
groups. There is indeed a lot of discussion, but I have not yet found
how
I would create a clustered index if I wanted to. My databases with a few
thousand records seem to work just fine. Why would I want to put extra
effort into something that already works well? I know you have posted
code that includes MAKE TABLE or some such, but the utility of such code
is not clear. The other thing I noted in Google groups is that most of
the discussion of clustered indexes seems to be in discussions about SQL
server.

wrote in message
oups.com...

Roger Carlson wrote:
Autonumber fields make excellent Primary Keys.

You've misunderstood what PRIMARY KEY means. An unique integer which
has no meaning in respect fo the entities being modelled makes a lousy
PRIMARY KEY. Google for "clustered index" in the Access groups.

An autonumber is a convenient uniqueifier but unquieness for its own
sake make not be such a good thing.











  #10  
Old September 23rd, 2005, 04:31 PM
external usenet poster
 
Posts: n/a
Default


BruceM wrote:
That you disagree with somebody does not make that person wrong.


That someone has a track record of providing a wide range of assistance
does not make that person right ;-)

In this case, I disagree with the person because they do not understand
what PRIMARY KEY means. Autonumber does not make a good uniquifier, let
alone a good PK (different concepts). Remember this list? the
advantages of using autonumber a

1. Convenience, because it's provided by the 'system';
2. It's an 'efficient' data type;
3. Erm...
4. That's it!

 




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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting a table by concatenating several fields in the same table salsaguy Running & Setting Up Queries 3 March 6th, 2005 09:41 PM
Sorting a table by concatenating several fields in the same table salsaguy Running & Setting Up Queries 0 March 6th, 2005 02:33 AM
Additional fields for form based parameter query/null fields geeksdoitbetter Running & Setting Up Queries 2 January 7th, 2005 11:05 PM
Selecting Fields for Update Steve Daigler Page Layout 4 October 15th, 2004 02:13 PM
My tables lost their AutoNumber fields Bill Nicholson Database Design 2 July 2nd, 2004 02:20 AM


All times are GMT +1. The time now is 12:13 AM.


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