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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Primary Keys
States! Do you know that:
the state of North Dakota tried to change its name to just Dakota a couple years ago? in the '70s there was a movement to split California into three states? West Virginia was part of Virginia until the Civil War? there's been attempts to make Puerto Rico a state? Just goes to show that some things considered rock solid could change in the future. That's why I like autonumbers for primary keys. -- Jerry Whittle Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "LurfysMa" wrote: Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can I use them as primary keys or should I define an autonum primary key? One table has information about the 50 states in the US. The table looks like this: State Capitol Date Admitted Alabama Montgomery December 14, 1819 Alaska Juneau January 3, 1959 Arizona Phoenix February 14, 1912 Since the state names are unique, is there any reason not to make that field the primary key? Another table has to do with grade school multiplication tables. This is a simple table something like this: Factors Answer 1x1 1 2x1 2 2x2 4 3x1 3 3x2 6 3x3 9 ... 12x1 12 12x2 24 ... 12x12 144 Here again, the Factors field (a text field) is unique. Is there any reason not to use it as the primary key? In both cases, the tables are small, so adding another 4-byte field is no big deal, but I'd like to keep the tables as simple as possible unless there is some downside. Thanks -- Running MS Office 2000 Pro on Win2000 |
#12
|
|||
|
|||
Primary Keys
State Capitol Date Admitted
Alabama Montgomery December 14, 1819 Alaska Juneau January 3, 1959 Arizona Phoenix February 14, 1912 Since the state names are unique, is there any reason not to make that field the primary key? Yes, there are several good reasons. First, you might find some spelling errors. You might come out with a French version. Or, someone wants the names to be spelled in German, or whatever tickles your fancy. If you use a autonumber, and then start using a description for the State in place of the name, then your database can continue to function without modification. As others mentioned, there is much philosophy and strong views on each side of the camp (natural keys vs autonumber keys). My view is that when you relate a table, I simply want the database to please give me a relation between those two tables I specify. At that point, I give NOT one hoot about what field is used, and in fact I don't even want to waste my brain power coming up with a field to create the relaton. I want a one to many relaton. What you do after that is your business!! Here is my rant on this subject. It also explains why you don't every want to expose the autonumber to the end user. Be forewarned...this is a old post..and is a rant..but, it gives you the idea of how much fervor can go into the subject... ---------------- Why would you EVE"R care what id ms-access uses for the relation? Do you care what memory segment word gets loaded into? Do you care if it is memory segment 32, or 8192? Are you now to ask users with a prompt as to what memory locaton that your word document will load into? Who cares..that junk is for comptuers to deal with...not humans... Who cares about a number you, and your users will NEVER see? An autonumber is some mechanistic to generate a number. To you and me, all we care about is that we have a relation from customers to customers invoice table. Do we really care, or have to know what number is used? Really, when word loads into memory, we don't care about the number used for the memory location. Really, when ms-access has a relation between customers and the invoice file...again we don't give a hoot about what number is used. Me, or you never sees the segment number when word loads, and we as users will never see the autonumber either. These numbers are NOT for humans to see..... There is a ZILLION kinds of internal numbers that your computer uses all day to function. Why do you care what memory segment numbers the computer used to load ms-access, or ms-word? Why care? If your folks can see, or use those autonumbers...then that is your problem with autonumbers. The real wrong being done here is that users can see, or use the autonumbers. I mean, do you want ms-word to start showing you the memory segment numbers it uses to load a document into memory? It would be crazy to force users to deal with memory segment numbers when using word. With ms-access, YOU NOW are the software developer. So, just like those developers who creased word, they don't show users what memory location the documents load into. You as a developer has a responsibly to NOT LET USERS see the autonumber. If you need some number for your users, then you need to write your own custom code that generates those numbers for human consumption (say, things like invoice number etc.). You do NOT want to use the invoice number for relations etc (you still use a internal autonumber, and that way you don't even care if the invoice has a invoice number, or perhaps you wait a specified time until a invoice number is given. Either way, you can still have your relational database function...but behind the scenes it is using a autonumber). Your database should not crap out just because you don't have a order number handy. Who even cares if you enter a order number, or not? Why should your database stop function if you don't enter a order number? Even if you change the order number, again..why should your database not work? Maybe you need to delete the order number? (again, what on planet earth does deleing some arbitrary number like the order number HAVE ANYTHING to do with building a functional relation between two tables? How possibility does these two separate concepts have anything in common?). You users should NEVER EVER see a autonumber. You mistake here is to try and let humans see, or even refer to, or use the autonumber in any way. Autonumbers are NOT to be given meaning by humans...but ONLY to your software. Who cars if you have a order number, or not? What does the fact of having a order number have to do with your database to functionally correctly? If you want to require that a order number HAS to be entered, then make the order number a required field, but that simple stupid order number HAS NOTING to do with setting up a relation between two tables. Setup your relation between tables with internal numbers, and your database will JUST WORK REGARDLESS of what fields, and things you decide to store as data. Do not go and attached some number out of the blue like a stupid order number to build relations between your tables. Can you imagine if products like QuickBooks, or even products like ms-word exposed internal numbers used for relations and other internal numbers as to how the software will function? Software uses ZILLIONS AND ZILLIONS of internal numbers and pointers to function. Now that YOU ARE the software developer, it is up to you to hide these numbers. You can expose these internal numbers (like autonumbers), but that is just rude, and just services to torture your users. Hide all the internal number stuff....every other developer before you did this.... Why expose users to the exhaust pipe of a car when all they want to do is drive? Software is a machine you build. Build it...make it work, and then give it to your users. Users do NOT need to know about the kinds of teeth used in the gears for the car... So, the two concepts of how relations works is that many of us just believe that setting up a relation between two tables is a conceptual idea, and HAS NOTING to do with the data that you need to store. Others would disagree on this concept... By the way, there are some STRONG augments for using natural keys. For example, if I adopt a natural key in my data, then can freely move it between TWO DIFFERENT systems that respect this approach. (of course, you have to have those two systems respect that approach!!). -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.attcanada.net/~kallal.msn |
#13
|
|||
|
|||
Primary Keys
LurfysMa wrote:
Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can I use them as primary keys or should I define an autonum primary key? One table has information about the 50 states in the US. The table looks like this: State Capitol Date Admitted Alabama Montgomery December 14, 1819 Alaska Juneau January 3, 1959 Arizona Phoenix February 14, 1912 Since the state names are unique, is there any reason not to make that field the primary key? Another table has to do with grade school multiplication tables. This is a simple table something like this: Factors Answer 1x1 1 2x1 2 2x2 4 3x1 3 3x2 6 3x3 9 ... 12x1 12 12x2 24 ... 12x12 144 Here again, the Factors field (a text field) is unique. Is there any reason not to use it as the primary key? In both cases, the tables are small, so adding another 4-byte field is no big deal, but I'd like to keep the tables as simple as possible unless there is some downside. Thanks -- Running MS Office 2000 Pro on Win2000 You could use whatever you want. IMO most of the criticism around this topic involves autonumbers being used as the primary key, and in addition, an attempt is made to use these autonumbers as ordinals, perhaps sequential invoice numbers, rather than merely nominals, identifiers of the records. Many developers routinely create an autonumber ID in every table as they create it. This, IMO, simplifies relationships (they are always (ID, ID) where ID = ID), and ensures that a unique identifier exists for each record, without concern for any meaning, duplication or possible nullability of that identifier (Access forms often are not updateable unless such an identifier exists). But if one has the concepts and skill, other primary keys are fine. Of course, many may not have the skill, and those who do will often choose autonumbers to standardize their approach to this matter. I use autonumbers. There are sufficient things to be planned and decided about db design without including ... what will my primary keys look like. BTW, some think of Primary Key as something "special". A primary key is simply the first created non-nullable unique index. Designating an index as primary will move it to position one (or return an error). We could easily do away with this term; I worked with indexes ( a thousand times more powerful and useful than JET or SQL-Server indexes) for many many years in the X-Base world without ever hearing it and I find no particular value in its availability. |
#14
|
|||
|
|||
Primary Keys
On Wed, 12 Jul 2006 14:15:59 -0400, "Douglas J Steele"
wrote: Just to play devil's advocate, at least two of the official provincial abbreviations have changed in Canada in recent memory (Quebec used to be PQ, and now is QC, Newfoundland and Labrador used to be NF, and now is NL). We also got a 3rd territory a few years back, but an addition to the table isn't as bad as a change to the PK. OK, OK. I'm sold. I'll use an autonum field as the primary key. I suppose the phonetic English movement could still gather steam and California would become Kaliforia or something. ;-) -- Running MS Office 2000 Pro on Win2000 |
#15
|
|||
|
|||
Primary Keys
LurfysMa wrote:
Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? The tradeoff is that some people will think you're an idiot if you use them and some people will think you're an idiot if you don't. As long as you understand that adding an autonumber as a primary key has nothing to do with the normalization process I think they are just fine. |
#16
|
|||
|
|||
Primary Keys
RoyVidar wrote:
Just be aware - for some this isn't just a matter of preference, it's religion to a degree thats close to fanatism. chuckle Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm |
#17
|
|||
|
|||
Primary Keys
Hi LurfysMa
Nice discussions. Every record that has an relation with other records in other tables, must have an unique identification, that used in all the relations. As long as the "meaning" of the record stays the same, this identification stays the same. Whether it is Autonumbering or Random or whatever is not important, as long as it is unique. Independant of the the identification is what you choose as Primary Key. This may be your unique identification, but in fact can be any combination of any fields, as long as they do not contain null-values. But if you use a non-Autonumbering Primary Key, be sure that you use your unique identification in your relations! HBInc. LurfysMa wrote: Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can I use them as primary keys or should I define an autonum primary key? One table has information about the 50 states in the US. The table looks like this: State Capitol Date Admitted Alabama Montgomery December 14, 1819 Alaska Juneau January 3, 1959 Arizona Phoenix February 14, 1912 Since the state names are unique, is there any reason not to make that field the primary key? Another table has to do with grade school multiplication tables. This is a simple table something like this: Factors Answer 1x1 1 2x1 2 2x2 4 3x1 3 3x2 6 3x3 9 ... 12x1 12 12x2 24 ... 12x12 144 Here again, the Factors field (a text field) is unique. Is there any reason not to use it as the primary key? In both cases, the tables are small, so adding another 4-byte field is no big deal, but I'd like to keep the tables as simple as possible unless there is some downside. Thanks -- Running MS Office 2000 Pro on Win2000 |
#18
|
|||
|
|||
Primary Keys
LurfysMa wrote: Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? That is a good question. He's the position, as I see it, in brief. Codd introduced the idea of a primary key. He later realised that all keys are valid and that he was previously thinking non-relationally when he assumed one key would need to be nominated as 'primary'. RM theory has since moved on from the concept of primary keys. It was too late for SQL, though: SQL vendors implemented primary keys, assuming the PK would be given special meaning, and the concept of PKs was retro-fitted to the SQL standards. You can replace all your PRIMARY KEY constraints with NOT NULL UNIQUE because they logically equivalent. This is what the Access help means as referred to by the OP. However, in terms of physical SQL implementation, PRIMARY KEY has been given special meaning. This is why you are (correctly) still urged to designate a PRIMARY KEY for all your tables. What few people tell you is *how* to choose the PK. What it comes down to is this: for Access/Jet, what does PRIMARY KEY give you that NOT NULL UNIQUE does not? What is the special meaning for the particular product, Access/Jet? The answer, for Access/Jet the PK determines the (non-maintained) clustered index, the physical ordering on disk. So the next question is: what makes the best clustered index? The answer to this is that a clustered index favours BETWEEN clauses and GROUP BY clauses in SQL DML (queries, etc). In other words, your choice of PK in SQL DDL (design) is driven by you SQL DML (queries). The paradox here is that you can't write SQL DML before you've written your SQL DDL, so you need to keep your PK's under review. If you've understood the above you should come to the conclusion that a sole autonumber column will never make a good PRIMARY KEY in Access/Jet, because a random/incrementing integer/GUID does not make a good clustered index. I'd suggest that anyone who uses their autonumber column in a BETWEEN or GROUP BY construct has got something wrong in design and/or queries. I'd further suggest that anyone who uses BETWEEN or GROUP BY constructs which do not include columns that comprise their PKs are likely to have made a poor choice of PK. Jamie. -- |
#19
|
|||
|
|||
Primary Keys
onedaywhen wrote:
The answer, for Access/Jet the PK determines the (non-maintained) clustered index, the physical ordering on disk. Can you verify this? |
#20
|
|||
|
|||
Primary Keys
onedaywhen wrote:
What it comes down to is this: for Access/Jet, what does PRIMARY KEY give you that NOT NULL UNIQUE does not? What is the special meaning for the particular product, Access/Jet? The answer, for Access/Jet the PK determines the (non-maintained) clustered index, the physical ordering on disk. From http://msdn2.microsoft.com/en-us/library/wd9d69b1.aspx THE CLUSTERED PROPERTY IS IGNORED FOR DATABASES THAT USE THE MICROSOFT JET DATABASE ENGINE BECAUSE THE JET DATABASE ENGINE DOES NOT SUPPORT CLUSTERED INDEXES. |
Thread Tools | |
Display Modes | |
|
|