Using Autonumbers in database design

[nicholas111]

I have benn told NOT to use autonumbers in database design by atleast 3 database engineers but none of them can tell me why I should not use them....
I am wondering wether I should use them or not, can someone please tell me and tell me why or why not I should use them.... Thankyou

 

[zink421]

From my experience autonumbers are a quick way to create primary keys that uniquely distingush each record. So for example if I select a student from a data grid or web page I could not use sql with the students name to get to the right student because I have multiple students with the same name, instead I use the autonumber for that record which is hidden from the user to produce my criteria for that sql statement. The only alternative is to create a combined primary key like lastname and phone which your sql statement would use "and" logic. If you are a beginner use autonumbers they are easy to implement and learn db concepts. Combined primary keys can get confusing & implemented wrong, However they provide logical meaning & increased integrity. For example, with an autonumber you could enter the same student twice - because your primary key is a autonumber, however if you have a combined primary key you would not be able to because the same lastname and phone is not allowed because its your primary key. To your db engineers, they are right but why then do ORACLE, DB2, & SQL Server allow you to create autonumbers. The three leaders in Databases worldwide. GREAT QUESTION

 

[tlbroadbent]

I really appreciate the comments by zink421. They touched a number of important points. The main objection many relational purists have to autonumbers is that they are arbitrary data, not based on the real world data in the table. The contention is that primary keys should always consist of columns containing "real data."

The other major problem with autonumbers is the implementation is often buggy or appears to be buggy. For example, autonumbers may not be sequential. This happens for many reasons, some by design. As zink421 mentioned, the autonumber should be hidden from the user. Autonumbers make wonderful unique keys but lousy counters. And people like counters to be sequential and consistent.

I like autonumbers as primary keys unless a simple, relatively short and hopefully single column key can be found in the attributes of the table.

Check this link for a little more insight.

General: Use Autonumbers properly

http://www.mvps.org/access/general/gen0025.htm

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[svanels]

Auto numbers can work problematic if records are deleted, especially in a network environment. Inconsistencies in databases are an indication of problems.
Autonumbers work fine for desktop database, but if their is a chance to upscale it to a RDBMS (Oracle, Interbase etc) they should be avoided.

RDBMS have built in generators and triggers, which take care of generating unique numbers.
Example
User1 tries to enter a new record, and the database assigns
number 3000
User2 also enters a new record, and the database assigns number 3001

The next available number will allways be 3002, although user 1 or 2 can abort the operation, and the record will not be posted to the database.

My opinion is that auto numbers should be avoided for key fields. S. van Els
SAvanEls@cq-link.sr

 

[Rhonin]

Another thought, I have a number of databases, most with multiple field primary keys. I have found that including an autonumber field allows for easier use when desining specialized reports and forms. Once I have the basics all taken care of, I can easily return and change them if I want. I also find this easier when running items like listboxes where there are multiple selections made.

On some databases I have users that create their own custom reports and since most are novice/intermediate users, they find that autonumbers are usually easier.

Overall though, for databases where you only want to record unique records, autonumber fields are generally more trouble in the long run.

IMHO

 

[Paul7905]

tlbroadbent, seems this link in your post is no good, i get a page saying the web site does not exist ~


"General: Use Autonumbers properly

http://www.mvps.org/access/general/gen0025.htm

Paul