Results 1 to 13 of 13
  1. #1
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65

    Why use autonumber primary key fields?

    I have never fully understood the usage/Purpose of the autonumber fields that are set into any new table created. So perhaps someone can explain the usage/purpose/need for this type of field.

    Below are some related tables from an application I have. The Primary Key fields, in the tables that have them are not auto numbers, but are generated from one of two tables that provide a company specific set of unique ID numbers.

    You will note that the tables lnkChangeTrain and lnkChangeApprove have no primary key fields. Both of these tables are nothing more than groups of employees (either for training and/or approving) that have been assigned to specific change requests.



    I see no need for an autonumber field in those tables that already have a unique, primary key field. That leaves the tables lnkChangeTrain and lnkChangeApprove and I would like to know how the database, the users, or myself benefits in anyway from having an autonumber primary keyed field in those two tables.


    Click image for larger version. 

Name:	Relationships.jpg 
Views:	24 
Size:	91.7 KB 
ID:	30990

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Here is an excerpt from Pau Litwin's "Fundamentals of Relational Database Design." I believe that the last paragraph sums it up.
    Tables, Uniqueness and Keys

    Tables in the relational model are used to represent "things" in the real world. Each table should represent only one thing. These things (or entities) can be real-world objects or events. For example, a real-world object might be a customer, an inventory item, or an invoice. Examples of events include patient visits, orders, and telephone calls. Tables are made up of rows and columns.

    The relational model dictates that each row in a table be unique. If you allow duplicate rows in a table, then there's no way to uniquely address a given row via programming. This creates all sorts of ambiguities and problems that are best avoided. You guarantee uniqueness for a table by designating a primary key—a column that contains unique values for a table. Each table can have only one primary key, even though several columns or combination of columns may contain unique values. All columns (or combination of columns) in a table with unique values are referred to as candidate keys, from which the primary key must be drawn. All other candidate key columns are referred to as alternate keys. Keys can be simple or composite. A simple key is a key made up of one column, whereas a composite key is made up of two or more columns.

    The decision as to which candidate key is the primary one rests in your hands—there's no absolute rule as to which candidate key is best. Fabian Pascal, in his book SQL and Relational Basics, notes that the decision should be based upon the principles of minimality (choose the fewest columns necessary), stability (choose a key that seldom changes), and simplicity/familiarity (choose a key that is both simple and familiar to users). Let's illustrate with an example. Say that a company has a table of customers called tblCustomer, which looks like the table shown in Figure 1.

    Figure 1. The best choice for primary key for tblCustomer would be CustomerId.


    Candidate keys for tblCustomer might include CustomerId, (LastName + FirstName), Phone#, (Address, City, State), and (Address + ZipCode). Following Pascal's guidelines, you would rule out the last three candidates because addresses and phone numbers can change fairly frequently. The choice among CustomerId and the name composite key is less obvious and would involve tradeoffs. How likely would a customer's name change (e.g., marriages cause names to change)? Will misspelling of names be common? How likely will two customers have the same first and last names? How familiar will CustomerId be to users? There's no right answer, but most developers favor numeric primary keys because names do sometimes change and because searches and sorts of numeric columns are more efficient than of text columns in Microsoft Access (and most other databases).

    Counter columns in Microsoft Access make good primary keys, especially when you're having trouble coming up with good candidate keys, and no existing arbitrary identification number is already in place. Don't use a counter column if you'll sometimes need to renumber the values—you won't be able to—or if you require an alphanumeric code—Microsoft Access supports only long integer counter values. Also, counter columns only make sense for tables on the one side of a one-to-many relationship (see the discussion of relationships in the next section).

    Note
    : In many situations, it is best to use some sort of arbitrary static whole number (e.g., employee ID, order ID, a counter column, etc.) as a primary key rather than a descriptive text column. This avoids the problem of misspellings and name changes. Also, don't use real numbers as primary keys since they are inexact.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Number values are supposed to index faster.

    I have a db that does not use autonumber PK/FK except for one table and seems fast enough. However, the largest table is only about 60,000 records. One of the tables has autonumber PK so I can avoid a composite key of 4 fields.

    I have another db where using autonumber PK actually got in the way because of regular import of data. I eliminated all autonumber PK and in one relationship had to use a composite key (only 2 fields so not too bad).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65
    OK, I can understand the words and I can understand the concept of the relational model, as described by Pau Litwin. However, I have a hard time accepting and just do not see the point when something is not used nor has any reason to be used.

    Starting with my example, the two tables lnkChangeApprover and lnkChangeTrain are nothing more than an extension of the table tblChangeRequest. Between the two of them they provide lists of the employees who approve and/or are trained on a change request, along with their electronic signatures. By the way, I failed to include the table lnkEmployeeInfo which is where the tables lnkChangeApprover and lnkChangeTrain obtain the data for strLogin and strPassword. The electronic signatures are the only thing in those two tables that is not subject to change over time but are, in fact, unique every single time they are entered by an approver or trainee (I would never use an electronic signature for a primary key, nor would I think anyone else would).

    Now since we have established that the data in the tables lnkChangeApprover/lnkChangeTrain are really nothing but extensions of a record in the table tblChangeRequest, the first providing a list of approvers and the second a list of trainees. The only reason I can see for having a unique primary key field for either of those two tables would be for what might be called GDBP (Good Database Practice) without having any actual functional justification/usage in the database or the real world.

    By the way, GDBP I just made up so you might not have seen anything like that unless you have worked on something that is regulated where such terms like G___P (Good ____ Practice) are commonly used.

    In the example you use I can fully understand the need for a unique primary key. Each of these records describe a unique individual. However, my two tables have a relationship (possibly a perverted relationship) with two other tables, each of which have unique records with a primary key.

    What these two tables do is hold groups of records that are related to a single unique change request. What that means, is that there cannot and should not be a record in either of these tables that is completely unique, unless there happens to be only one approver and/or trainee (possible but not likely). My point being that this is the real world and I believe that sometimes, for certain purposes, a table can and should be relational without any need for a unique primary key. Is that completely out of line with accepted doctrine?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    A table does not require a unique PK if that table is not the 'one' side of a 1-to-many or either side of a many-to-many relationship. You might want intEmployeeID and intChangeID to be a unique compound index to prevent duplicate pairs.

    Beware 'spiderweb' design, could be circular referencing. https://www.codeproject.com/articles...atabase-design
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02.....

    I'm in the camp of "Every table has a Autonumber primary key field". I may not use it, but if I need it, it is there.
    Also, PK/FK fields are never displayed on a form/report.

    I use a suffix for my PK/FK fields:
    CustomerID_PK
    CustomerID_FK

    I use "ID" in the name to indicate a numeric field and "_PK"/"_FK" to clarify which field is which. I have seen dBs that have something like "CustomerID" in 3 tables. After linking tables in queries, it is hard to determine which is the PK field.


    AutoNumber

    Purpose
    Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

    And here are a couple of sites to read:
    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/Autonumbers

    Microsoft Access Tables: Primary Key Tips and Techniques

    http://www.fmsinc.com/free/newtips/primarykey.asp

    Use Autonumbers properly
    http://access.mvps.org/access/general/gen0025.htm

  7. #7
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65
    June,

    Not entirely clear about a compound index. But the two tables, lnkChangeApprove/lnkChangeTrain both have a many-to-1 relationship to tblChangeRequest through intChangeID. intChangeID is the primary key in tblChangeRequest. Additionally, both tables are linked the same way to tblEmployee through intEmployeeID. Does that relate in anyway to what you are referring to. It has been years since I have worked much with Access and if you are talking about the Join properties I have been leaving those alone since I no longer understand what they do.

  8. #8
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65
    Ssanfu,

    Your handle looks suspiciously like a play on snafu. Any way, your $0.02 is probably worth more than that (I would say about $1.240). Anyway, I think I will take your suggestion. You never know when you might need a primary key and even if never used it doesn't waste any processing time or use much memory.

    Thanks

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Every table can have an autonumber field but it does not have to be designated as the primary key.

    I would have to know more about purpose of this db and the business process - how does the data flow - to have any further suggestions on the structure. But I doubt I would have done as shown in the image.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65
    Actually June, I am aware of that. What this process is all about is a change request. You might not be familiar with that, depending on what industry you work in so let me give you some background. A change request, sometimes called a change order is something that originally came out of engineering. It defines a process, and all the documentation that goes with it, of creating, or making a change to a process or object.

    Let's say I have a pH meter and a new software upgrade for the system arrives. GEP (Good Engineering Practice) would dictate that I would not just download and implement the upgrade and hope for the best. What would occur is a change request would be initiated defining what the change to the system is and what it is going to take to make that change and then provide a process for implementing the change and documenting the results and, hopefully, a successful conclusion. In the process of completing a Change Request, the change that is taking placed must be approved, usually by representatives of whomever owns the pH meter, Quality, and probably one or more Subject Matter Experts (SMEs). Additionally, training might be required for the users of the pH meter after the change is complete. Approval and trainee sign-off are integrtal parts of a Change Request.

    The application discussed here is for this change request process, which is partially defined through the tables tblChangeRequest, lnkChangeApprove and lnkChangeTrain. Each record in tblChangeRequest represents a single and unique Change Request and has a Primary Key, intChangeID, which is not a autonumber field. In this case I use an integer field starting at the number 10000. I do sequence the numbers, however. The table lnkChangeApprove holds records of all the approvers for each change request generated, while the table lnkChangeTrain holds records of all the trainees for each change request generated.

    Now what we have here is a failure to communicate...... Sorry, that comes out of one of my favorite old movies and I can't help myself when I see or use the words, "What we have here". What we have is a Change Request that contains some number of approvers and their signatures, as well as some number of trainees and their signatures. In terms of the database we now have a table representing all change requests, a table representing all approvers, and a table representing all trainees. There are other tables associated with the Change Request, but they are not relevant to the discussion or this part of the process.

    Click image for larger version. 

Name:	ChangeRequest.jpg 
Views:	18 
Size:	73.7 KB 
ID:	31001

    As previously stated, what I have is tblChangeRequest containing a unique record representing a single Change Request. for each Change Request record generated in this table there are subsequently generated a group of approver records and trainee records, represented in the tables lnkChangeApprove and lnkChangeTrain, respectively. Each of these tables are related to tblChangeRequest by the field intChangeRequest (the Primary Key field). Note, I have followed ssanfu's advice and put in an autonumber PK into the other two tables, even though I have no discernable use for them.

    When the process is complete what would be generated is a form of the completed Change Request with a listing of all the approvers and their signatures (I would treat the trainees as an attachment that can be displayed if desired). Certainly there are other ways one might approach dealing with a record that is indeed meant to be unique but contains data that requires multiple inputs for a single record. I would be interested in your ideas for other approaches to this process. By the way, I went light on the data flow, since I believe that would be a little more than this process deserves for keeping my explanation relatively simple and not too long winded.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just a comment that may or may not be relevant to your question;
    Why use autonumber primary key fields?

    There is no rule that says you must use an autonumber. The underlying rule is that each tuple/record in a table must be uniquely identified. So, you can define your own primary key. An autonumber PK is a simple surrogate key that serves only to uniquely identify a "record" in a table, which satisfies the unique identification rule.

    From wikipedia: In order for an attribute to be a good primary key it must not repeat. While natural attributes (attributes used to describe the data being entered) are sometimes good primary keys, surrogate keys are often used instead. A surrogate key is an artificial attribute assigned to an object which uniquely identifies it (for instance, in a table of information about students at a school they might all be assigned a student ID in order to differentiate them). The surrogate key has no intrinsic (inherent) meaning, but rather is useful through its ability to uniquely identify a tuple.

    That Access offers an autonumber type to populate a field (typically PK) is not an isolated approach. A similar/same concept of a database sequence** is used in other DBMS such as Oracle and MySQL...The purpose of the sequence is to supply a unique number that, when used as PK, you can be assured that uniqueness is attained. But you do not have to use an autonumber nor a sequence as PK, you can always choose a "natural key" as your table's PK.

    **A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns: CURRVAL : Returns the current value of a sequence. NEXTVAL : Increments the sequence and returns the next value.

    I recall an older post re Change Orders. You seem to be working with a more disciplined approach than this poster.

    Good luck.
    Last edited by orange; 10-29-2017 at 02:01 PM.

  12. #12
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65
    Thanks Orange. I am not having any problems with what I am attempting to do. However, as a one time practicing engineer the usual practice was (and I hope still is) that you never do things that do not specifically add value to whatever it is you are designing or working with (keeping in mind that no rule is sacrosanct). It will almost always come back and bite you on the ass. While I can understand that it is considered good practice to have a PK in every table, the two tables used for Approvers and Trainees in my change request are used for only one thing and they have absolutely no requirement for a PK field. On the other hand, I cannot see anyway that having them there will come back and bite me on the ass, so I decided to follow what is considered good practice with relational databases.

    Your description of the surrogate key, as I understand it, might also be described to be the intChangeID field that is in the Approver and Trainee tables and related to the PK field intChangeID in the Change Request table? This provides those two tables a means of filtering/isolating a tuple (I love that word and haven't seen it in years) of records related to a specific record in the Change Request table. It is not unique to each record, but is unique to a tuple of records.

    As I see it, the Approver and Trainee tables contain a set (or tuple) of sub-records specific to a single record in the Change Request table. I can see some other approaches to handle this type of data/process, but none that really appealed to me.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I cannot see anyway that having them there will come back and bite me on the ass
    I have heard this many times--sometimes it never happens, but you have to be careful.
    I worked with a group that worked with Canadian postal codes and provinces. Many years ago they decided to use
    the first 1 or 2 characters of postal code to represent Province, then Canada decided to extract Nunavut from North WestTerritories and the doodoo hit the fan. I have also seen where the province abbreviation of Quebec changed from QU to PQ, and Newfoundland NF to Newfoundland and Labrador to NL - that threw a few systems into a panic.

    From database management perspective, you need to uniquely identify "records" in "tables". If the mechanism you use to do that is guaranteed to be unique, then all is well. Often, when the choice for PK has some meaning in the "business", the business (processes) change and the sure thing undergoes a change in meaning or scope. I'm not suggesting your choice(s) are incorrect, at least you've given thought to it and have made the decision based on knowledge. Many do not.
    Last edited by orange; 10-29-2017 at 01:15 PM. Reason: spelling

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Tables and Primary Key autonumber
    By snoop832 in forum Access
    Replies: 5
    Last Post: 11-28-2016, 09:11 AM
  2. Primary Key Autonumber
    By data808 in forum Access
    Replies: 19
    Last Post: 07-15-2014, 06:51 AM
  3. Using AutoNumber primary key on sorted column
    By grkatz823 in forum Access
    Replies: 7
    Last Post: 03-04-2013, 03:47 PM
  4. Sequential autonumber primary key
    By JackCampion in forum Access
    Replies: 0
    Last Post: 09-17-2012, 05:07 PM
  5. Replies: 2
    Last Post: 04-30-2010, 09:43 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums