OK, so I just got my grade back for a project I did for school(no, I never used this forum for that project, I don't beleive in those kind of ethics) but I am not pleased with my grade. I know right off that partof my structure sucked(for lack of better word at the moment). Bu what is irritating me is the comment made by my instructor that Autnumber should not be used for PK's. So hopefully this is the start of a thread to really set things straight(at least in my head) of proper PK's. I recently read that using Autonumber for a PK violates the Relational Model(which I am in the process of reading more about now.) My goal - to know as much as possible about this by the time it's said and done with. So, start posting away guru's because I'm about to argue my project grade for a few other reasons as well, like form design.

And, if possible, list your credentials if you will that pertinent to your knowledge of RDBMS(like training). Not to be nosy, but some around here think I rock at db's due to some creations but I have no paper under the belt to say so. I just want to see a few Certified DB Admins post in favor of Autonumber PK's. OK, enough rambling..let me go find a punching bag.

Thanks in advance for the undoubted good reading that is soon to follow,
Jason

Edited by: JVanKirk on Tue Dec 14 9:39:44 EST 2004.

Jason,

I've heard this as well and would like to know why. I added this thread to my favorites and hope it's an active one.
Jason K.

Other then having been designing databases for close to 20 years I have no certs or other geegaws to justify my opinions. However, I would go back to the instructor and ask him to explain his comment. Clearly Microsoft designed Autonumbers to be used as a PK. To prove this, create a table with an autonumber but do not deisgnate a PK. Save the table and Access will prompt you if you want to assign a PK. If you choose yes the Autonumber field will be assigned as the PK.

While I'm saying that MS has always made good design choices with Access (i.e. Lookup fields), Your instructor should justify why he thinks Microsoft designed Access incorrectly.

I'd also like to know where you read about autonumber violating the relational model.

Jason,

See if this helps any. Personally, I have no formal certifications. I don't use anything but autonumber PK's. Here in the real world, I have to keep things simple and moving as quickly as possible. Deadlines to meet, etc. I've run into lots of folks that think that all PK's should actually describe the entity. Well, that being the case, you'd really need to use every field in the table, in a properly normalized db, as the PK. T'ain't happenin' McGee...at least not in any db I create. Autonumber gives me that unique value to id the record, and I can use a compound index to control uniqueness.

Good luck changing your instructor's mind.

Noah

Scott,

It was in another forum(I emailed the poster already to find out why it violates the relational model). When I read some sites about the relational modelit actually mentions the use of an "alternative key" as the PK when no other values is appropriate.

What gets me, is everything I am reading leads to make sense that an Autonumber is the way to go(other than taking up a little extra space in the db). The requirements for a PK from my research so far is, No Nulls, NEVER change, not even the slightest chance for change, and unique to the record. What the heck is better than an Autonumber field. Granted, when I have the SSN in a table I think, why not use that for the ID? I still can't answer that one the best other than to say, maybe, just maybe that number could change one day, and the Autonumber will NEVER have to change.

Noah, couldn't agree more, it's a quick efficient way to make a PK in the real world and it's not like Access is a fully relational DBMS anyway.

Jason

Noah, thanks for the link, it is bookmarked. Yeah, I doubt that I will change the instructor's mind but I got a 75 on my project which pulled my final grade down to a B. It is perturbing since I learned a lot of what I know from right here and have gotten some good returns on a few designs and then I get a 75 on my project. I hate when someone goes excactly by the book instead of taking into account of practices in the real world since this is where it all get hashed out, where we all try to make a living. Yeah, so the book say 2 apples and 2 oranges = 4 fruit and 1 fruit feeds one person. If I need to feed 5 people we're eating fruit salad.

Jason

Ahhh... SSN's as Primary Keys

Here's why not to use them:
1) The Social Security Administration may re-issue the number after the death of an individual. There is a chance that two people in your db may have the same SSN, if one is deceased. (Hey, you may need dead people in your table. It depends on your app)

2) Not everyone has a SSN -- Foreign nationals are not issued an American SSN at the border. Newborn infants don't have an SSN yet.

3) Security -- If the SSN is used as a PK, it will show up in every table that is linked to the 'people' table. I'd be a little worried if my SSN was that exposed. Also, people are loathe to give out the SSN for security reasons -- and there's no legal recourse to force them to. The Social Security card is a convenient proof of legal citizenship when you start a job, but it's not the only one.

That said, The only time I ever use anything BUT an autonumber for a primary key is when I'm designing a table that is a lookup table with very few records. In that case, I often want to control those numbers myself.

-AJ

-AJ

Here is one reason why I agree with your instructure.
http://www.utteraccess.com/forums/showflat...9167&Forum=,All_Forums,&Words=&Searchpage=0&Limit=25&Main=599105&Search=true&where=&Name=31057&daterange=&newerval=&newertype=&olderval=&oldertype=#Post599167&bodyprev=
hth.

Jason,

I suggest you do read that link from quest4, as Jerry gives his reasoning in that thread as to why you do want to use autonumbers.

Noah

You should not agree with the instructor. He (or she) is incorrect (as far as Access goes) concerning the use of autonumber as the PK. It is, by far, the best choice for PK you will find. It satisfies all of the requirements for the definition of the primary key (uniqueness, non-editability, non-assignment, etc....). You really should do more research before espousing such views.

Hello, Jerry. I understand your views, but I live in the world of ISO 9000 where we need almost total accountability. Inspectors/ auditors take a very dim view of any missing numbers, Access AN can not give that kind of stablity, it leaves blanks all over the place, we need to reuse our numbers and the DCount method which I suggested does do that to a fairly good degree. It all comes down to the situation that you are working in. If you are in a loose envirement then MS AN will probably be OK, but if you are working in an enviroment with audits and inspectors, then MS AN will never do.

Jason,
I really am curious to hear what your instructor has to say to justify his comments and the grade.

Geoffrey --
I'm going to have to back Jerry on this one.

Even given the situation you describe, where you need a sequential number, I would have two field in my table --
1) an Autonumber Primary Key, to be used throughout the database as a key field, and
2) A numeric field that would remain sequential, populated via the DMax method (or a method using a recordset. I hate DMax, it's too darn slow.)

The second a number absolutely needs to be strictly sequential, it has a meaning to the user, and as such, has lost its suitibility as a primary key.

-AJ

WHOA! There's your problem right there:

This has nothing to do with stability. If you thought that autonumbers should or will provide an exact sequential numbering system, then you were looking at them the wrong way.

This is the key issue. An autonumber has only ONE function; to provide a unique identifier for a record. For that function it does exactly what its supposed to.

If you need a sequential numbering system then use one, but don't call autonumbers unstable because they don't provide something they aren't meant to.

Then we get into the issue of whether you should use your sequential number as your PK. Most people here will say no, that the PK should not have meaning. I prefer that reasoning, but I can see that there are valid reasons to use a PK that has meaning.

Thank you for your imput AJ and Scott, your point are well made. With my method of numbering I have had no trouble with the inspectors, and I don't need two fields to make it work and it takes care of itself, which is nice. It seems like this subject always brings up a major debate almost everytime, but it is always good to get different ideas, we can all learn from them and hopefully it will make us just a little bit smarter in the long run. FYI 4U.

I beg to differ. We are ISO 9000/2000 registered and I am VERY familiar with audit trails, etc....

None of these have anything to do with the purpose of the AN. Nor does the AN conflict with any ISO or CFR 21 Part 11 requirements. The primary (no pun intended) misconception concerning the AN is that it is supposed to have meaning for people. Quite the contrary is true. The AN should NEVER even be shown to people (including the developer). It is nothing more than a way for the application to identify and link records.

The AN is the primary key, think of it as the folder in the drawer that holds the record. Does the folder have any meaning when it comes to the contents? No, neither does the AN (PK) have any meaning. It is the contents of the folder (i.e. the record) that has the meaning.

In a properly normalized data structure. Every single record is unique for all values of all fields (excluding the PK). In other words, in a properly structured table you could eliminate the single field PK and use all of the fields together as the PK. This is not practically feasible. So the best way to then uniquely identify the record is to use a unique number (numbers take up less space than any other datatype). That way, it is very easy to link child records to their parent (can you imagine having to have all of the fields from the parent table in the child table just to establish the link between the two?).

Jerry, getting ready to read the link from quest4, but on your last sentence, in a post above I mention pretty much what you said here based on my research. I feel the same that Autonumber is the best way to assign PK's in Access based on the relational model info I have read so far. As always though, I appreciate the advice you give. You are among those that I try to read and reread posts so I do have more knowledge before stating my views.

Jason

Jerry, just read the other post where you stated why you like AN for PK's. Again, I couldn't agree more. Everything I have read here leads me to use AN for PK's. This thread was started as my "more research" besides the 100 pages of relational model and PK info I printed yesterday and have started to scan through. Do you have anything from any reputable source stating advantages/disadvantages to AN as a PK in Access. Not saying you aren't in my book, but to say to the instructor, oh yeah, some guy online says AN PK's are the way to go isn't going to be too impressive. Believe me, I have done quite a bit of research on keys, I used to use the old way, SSN's and so forth before I stumbled into this forum one day, since then it's been nothing but AN's.

Thanks to all who have posted so far, it's been some good reading. Even if it doesn't do squat for my grade which was a B for the class overall anyway, it does reinforce in my mind that PK's in Access should be autonumber.

Jason

Scott, me too, I emailed her two days ago and still haven't gotten anything back yet. I asked her to break down where points were taken off for the project(table structure, form design, etc.) I know my structure was a little chewed up because I couldn't get a join table to work for data input on a form right so I ended up changing a few things. I attached the db here, it's in 200 format so I can't even open it here at work(built at home) but you all can look at it anyway. It's not too fancy as I didn't feel like spending a whole lot of time on something I have no intention of using ever(we had to use a switchboard for our main menu and I hate switchboards). Anyway, have a good day everyone.

Jason

I just had a thought on PK's having no meaning to the user. I actually use my PK field in a concactenation with "IM-" for ticket numbers. The only reason I do this is I do not need to have sequential tickets and they can be any number as long as it's unique to refer back to. Since the PK is already there I use it fromthe table to concact with the IM- text in an unbound field so the user has something to refer to when asking us a question on the status of a ticket or resolution of that ticket.
So I guess the foreseeable debate on that, is it an improper use of the PK, or is it acceptable since it will never be changed by the user and essentially has no meaning to the user other than a reference number. No one can touch it, not even the developer.

Jason

I probably wouldn't use the AN PK for this purpose.
Here's why: If the database has to be replicated later (unlikely in some cases, but hey, I don't know), then all Autonumbers become random Autonumbers, which can include negatives. Someone holding ticket IM--256435 might not notice the extra -, wouldn't mention it when inquiring about there ticket status, and would just cause general confusion.

-AJ

Hmm, interesting point, though I couldn't ever see the need to replicate this db. I guess I would need to read some more about replication as I have never needed it before. Thanks AJ.

J

Yeah, you probably won't need to replicate it, but even if you don't, Incremental autonumbers can eventually loop around and become negative.

Whenever I think "Is an Autonumber suitable for this?" one of the questions I ask myself is "Does it matter if it's a random Autonumber, meaning negatives are possible?" If the answer is yes, then the Autonumber is not used. I come up with a way to code it.

-AJ

Good, no great points AJ. In this case, where I am concact it I could see where that could be an issue one day. In that, I also see even more why to go with AN for a PK and have it never be used for anything other than that. Who cares if the PK is negative, but for an IM ticket to be IM--43 and IM-43 could get confusing. May need to concact the IM and the date and a number for that date or something.

Jason

Jason,

From what I've found (mostly by searching the internet), portability is the largest argument against autonumber primary keys. Another (and this one is arguable) is that having an autonumber PK violates the normal forms by creating a second candidate key in the same table.

Here's an interesting article with a lengthy debate (check the Comments section). It's focused on SQL Server, and kind of badmouths ANs in Access. Anyhow, here's the link

I still believe in Autonumber PKs, but I think I'll back off if there's an easy 'natural key' solution (e.g. lookup tables).

Jason K.

You may tell your instructor that the Information Systems Manager of a Fortune 500 company disagrees with him. I am also a past member of the Alpha and Beta test pool for MS Access. I have been designing and developing enterprise wide database applications for more than 15 years and have been working with Access specifically since v1.0. Before that I developed DB apps using Sybase, Oracle, SQL Server on the enterprise side and dBase, Superbase (which came before Access and what actually a fairly decent Window's based desktop DB but couldn't compete with Access when it came out). I have taught several college level courses in Access and trained hundreds of people in Access (not including the thousands I've helped (at least I hope I've been of some help) here on UA (and A3 before it)).

You tell 'em Jerry!!

UA rocks, and you guys have always steered me in the right direction.

Have definately made this thread a favourite

Cheers,
Averil

Hello Jason,

I am going to make a very large assumption that you are going to have to correct if I am mistaken.

Assumption: When creating the database, were you supposed to use third normal form to create the primary keys for the project and then design the relational model from that to finally building this model into Access.

I use AN within Access for PK's most of the time without even thinking but when creating an Entity Relationship Model for lecturers by producing forms using the third normal form there can be no Autonumber creation. It is up to the designer to correlate the Primary keys based on the needs of the company you are designing the database for.

Again I will add that using AN within Access is a very good solution.

Steve

In the strictest sense, the AN is not a true attribute of the entity. So, for purposes of the data model all candidate keys would be comprised of the appropriate attributes. In a fully normalized table the best candidate key is usually all of the attributes, but will almost always be multiple attributes. Working with composite keys is very cumbersome and never practical. Thus, I always substitute the AN as the PK.

Believe it or not, the article is making my case for me (at least as far as Access goes). I have stated (numerous times) that a properly normalized table could use every field as its composite primary key. This is not practical when trying to link a child table to its parent (you'd have to include every field from the parent in the child to facilitate the link, talk about a waste of db real estate!). So, let's say that you want to insure uniqueness of all records in your table then go ahead and make the PK the composite of all the fields but have another field (AN or otherwise, anything that cannot be duplicated within the single field) and make it the one by which you link your child records. This other field is then in essence the pseudo PK, so what's the difference? Why not eliminate the semantics of whether or not the AN is the PK or the pseudo PK and make it the defacto PK?

AJ, sorry in a post above this I said Good, no great poitns AJ...that shoudl have said some not no...don't know how my fingers typed that LOL

Jason

Exactly, and if a PK is never suppose to change, never have the slightest possibility of it, why would you use a composite of fields that could change? Using an AN ensures there will never be a need for the PK to change. Everything else that exists naturally for an entity is information that no matter how much you say it will never change could. SSN --Oh I need a new identity because I witnessed Hoffa's demise. VIN number on a vehicle. Mistyped the actual VIN and need to change it now. The list goes on. AN, AN, AN!!! SO what about when we leave Access to go to the big guns? Do we just code to make a random number. I guess when I get somewhere that I can actually play with the big db's I'll figure that one out, but making it this far in the thread, I can't see not wanting to use AN for the PK in Access. Again, Jerry, thanks for the several great post in this thread. I knew it was going to get good and am hoping this sets the midset for Access PK's atleat within the members of this community.

Jason

Hey y'all,

I actually ended up with an A in the class, got a 102 on my final. Still waiting for the instructors comments on autonumber PK's. I invited her to read this thread because I really want her opinion of AN PK's. It could be some valuable reading for this thread.

Well, hope you all are ready for Christmas(or whatever holiday you celebrate).

Jason

Well, don't know that my instructor will ever post here but here is what she said about the PK's:

"As for your question about the use of AutoNumber, remember that I am looking at this from the perspective of a database designer, not an Access user. Access is not a business-level database and provides many shortcuts that are not acceptable in standard database design. Yes, I do realize that many businesses use Access, but the limitations on Access mean that it cannot be used for large-scale databases, like inventory management or payroll management for a large corporation. From that perspective, AutoNumber is a sloppy shortcut that does not give the DBA enough control over what is going on behind the scenes. Higher-end relational DBMSs (like SQL and Oracle) use Sequences instead. Sequences allow you to specify a starting point for the numbering system, then sequentially increase that number by the amount specified.

AutoNumber is also considered sloppy because it takes up so much storage space. A five-character number (which allows up to 99999 records) only requires five bytes of storage, while an AutoNumber takes up eight. This storage space is used not only in the table for which the AutoNumber is the primary key, but also in each field in the related table. If you have one primary record and three related records, the five-character field will take up 20 bytes of space, while the AutoNumber field will take up 32. As you add record to the tables, the difference becomes even more significant. As the file gets larger, it also gets slower, so even minor differences like this can add up to more efficiency of the database.

Finally, whether you choose to believe me or not, there IS reason for primary key values to be meaningful, especially in large, corporate-level databases where the users routinely see and use the primary key values. I've given numerous examples of this in my comments on both the original design and your final project, so I won't take time here to reiterate those examples. However, VBA could be used to write an Access script that would automatically assign numbers as primary keys with the same control that Sequence gives the DBA in other DBMSs.

In the long run, the best option for a primary key is a value that is already used in the database, whenever this is possible. For example, the ISBN or UPC code that is stamped on the product you are tracking could be used as your Media PK. This would be an excellent option because it would mean that you could not inadvertently enter the same item in the database more than one time (which AutoNumber does not prevent at all)." - from her email to me as for the grade I received for my project. There are some good points.
Have a great day everyone.
Jason

Ok, this adds some thing you left out of your original question. Her points on the portability of ANs are valid. So is the space issue and (to some extent) I agree that Access is not a large enterprise level platform. It was designed to be an end user/small workgroup solution.

However, I must disagree with her on 2 points. First, her point about not inadvertently entering the same value. This is just not a valid reason for using a natural key since you can still set a unique index on a field to prevent duplicates. Or you can programmatically prevent them using data validation routines.

The second is her mentioning large corporate-level databases where the users routinely see and use primary key values. In my experience that is not true. In large, corporate-level databases users rarely see the actual data structures. That is the province of DBAs. Users don't have a clue about what are keys and what aren't.

<begin ramble>On the storage space required by Autonumber, entirely within the framework of the Access DBMS.


In what data type? By my reckoning, within the Access infrastructure, a five digit number must be a Long Integer, as the Integer data type caps out just shy of 33,000. Also, a Long Integer, which is what Autonumbers are, is only 4 bytes (unless you're using GUID's, and if you need those, there's a reason.)
IMHO, using a 5-digit number as a PK only limits the number of records you can have for no very good reason.

The only way to create a field that only takes 5 bytes of storage space is a length 5 text field, using Unicode compression-- and a text PK would significantly degrade performance, as the index inherent in a primary key field will function faster on a numeric datatype.

If your instructor is concerned about the storage space required by an Autonumber, these particular candidate keys should be thrown right out the window. A Long Integer caps out at around 2.2 billion -- that allows any 9-digit number to be entered as a Long Integer, and 10-digit numbers less than 2.2 billion. An ISBN number is 10 digits. A UPC can be 12 or more. To reliably store either of these fields, you'll need a text datatype. At 1 byte per character, the Autonumber wins out over either text field by a long shot in terms of storage space. (at least it does as soon as you start adding related records in other tables)

As far as I can tell, the objection about the amount of storage space an Autonumber takes up is pure horse-hockey. The ONLY substitute within Access for an Autonumber PK is a number of long integer size or smaller, that you can programattically ensure to be unique, and that doesn't have any meaning outside whatever the database is related to.
<end ramble>

-AJ

Thank you AJ and Jerry Dennison for setting the record straight. I too, have been doing db's a long time and have a fair amount of education and agree with YOU and not 'the teacher' in this thread.
I too use AN's for PKs on all principal tables. As someone up the thread menitioned, the only time I don't is a concious decision on some lookup tables where I will use the actual meaningful text of the lookup as the primary key. I do this for a couple of reasons, but the most blatent is for ease of data display. (Example: a large table with 15 lookups requiring 15 joins (or combo boxes, etc) to display any meaningful information....and don't even go there about defining a field in a table as a 'lookup field').
Thanks for all the (good) info you guys pass on.
Rob

Scott and AJ,

I too took her 5 digit to be a text field since text fields only use as much space as is used, no info in the field, no space used. And yes, I agree with what you start storing in your related tables as far as the PK. Sure you may add an extra field to your table when you have a UPC(if you're even storing UPC's which my database clearly did not, no reason to.), but in all your other tables you have the short An number compared to the long UPC. And AJ, good point about indexing speed of an integer vs. text, didn't think of that one. Scott, I thought the same exact thing when she said about preventing duplicate entries..I had to take a second to chuckle over that one and think...how much do we really know about indexes. Obviously to some, it would be possible to index on another field to prevent duplicate entries, heck you could even do some code to pop up a message without using indexes that says this album title already exists, are you sure you wnat to add because you could conceivably have duplicate album titles, movie titles, etc. Anyway, that's off the PK issue.

The thing she mentioned is that the class is about databases in general and not just access, and granted the name of the class is database apps, but why teach with Access, sing an Access book, and going into all the wizards and what all Access can do, when you could focus on database design principles and common likenesses shared by al DBMS's? Anyway, enough from me, I've got a network to go troubleshoot, seems some people around here want to be able to do some work LOL!!

Have a great day and I hope it's snowing where you are too.

Jason