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 |
#1
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
Dear all,
Although many Microsoft Access books advise to set an autonumber field as primary index (a so called pseudo primary key), http://www.blueclaw-db.com/database_link_tables.htm advises to use real data to define a (composite) primary key. In summary, this results in the following two designs: Microsoft Acces books setup for Employee table Employee_ID (autonumber, primary key) SSN (social security number, composite index key1) Employee_Name (full employee name, composite index key2) composite index SSN + Employee_Name = unique BlueClaw setup for Employee table Employee_ID (autonumber, unique index) SSN (social security number, (composite) primary key1) Employee_Name (full employee name, (composite) primary key2) In both approaches, Employee_ID would be used as a foreign key in other tables to define the relationship with the Employee table. Are there any negative aspects associated with the BlueClaw approach? Pros of BlueClaw approach *Display of table is meaningful, because it's sorted on primary index *No cascaded update necessary of linked relationship fields in other tables, because autonumber is only used for linking tables and therefore will never change. *Prevention of duplicates is improved, since data fields are used to check for duplicates, instead of an (always unique) autonumber field this can also be achieved with the composite unique index as shown above in the Access books example. Cons of BlueClaw approach *??? I would appreciate your comments / opinion on the BlueClaw approach, because I currently have the feeling that I'm missing something that explains why so many people use autonumber fields as primary (artificial) key. If the BlueClaw approach is the best one, I'm considering to use it as a standard in new database design questions. Best regards, M. |
#2
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
You may have just inflamed a long-running religious war about proper primary
keys.g See comments in-line below... "M." wrote in message ... Dear all, Although many Microsoft Access books advise to set an autonumber field as primary index (a so called pseudo primary key), "psuedo" implies "not real" -- a primary key is a unique identifier, no matter where it comes from. It isn't a question of "real". http://www.blueclaw-db.com/database_link_tables.htm advises to use real data to define a (composite) primary key. In summary, this results in the following two designs: Microsoft Acces books setup for Employee table Employee_ID (autonumber, primary key) SSN (social security number, composite index key1) you will want to be very careful about capturing/displaying SSNs. Moreover, not every "person" has (or cares to share) one. How will you handle a Null SSN? Employee_Name (full employee name, composite index key2) No, no no! If you combine more than one fact in a single field, you have to work extra hard to do simple things, like, say, sort by LAST NAME! Use FirstName and LastName fields, then use a query to concatenate them when needed. composite index SSN + Employee_Name = unique can you say "identity theft"? what makes you think that SSN + Employee_Name will be unique? BlueClaw setup for Employee table Employee_ID (autonumber, unique index) Why? By definition, an Autonumber is supposed to already be unique, so you wouldn't gain anything by indexing it. SSN (social security number, (composite) primary key1) (see above) Employee_Name (full employee name, (composite) primary key2) (see above) In both approaches, Employee_ID would be used as a foreign key in other tables to define the relationship with the Employee table. If you go to the effort of creating a composite primary key, then why would you not also go to the effort to "migrate" that key (i.e., all fields) to the "child" tables? Are there any negative aspects associated with the BlueClaw approach? Pros of BlueClaw approach *Display of table is meaningful, because it's sorted on primary index No, NO, NO!! Access tables store data, Access forms (and reports) display it. Using Access tables to display data is asking for trouble! (can you tell I have some strong feelings on this topic?g - check this newsgroup for others' ideas about using tables to display data. From experience, I don't want inexperienced users mucking about directly in my tables. Instead, I'll guide their use of the data via forms. This is a major difference between, say, Word {everyone knows how to move words around} and Access, a relational database {how many normal people understand relational database design?}) And "meaningful"?! To whom? Just because a set of data is sorted in one order doesn't mean that EVERYONE wants to see it in that order. I, for one, prefer to see a list of employees sorted by last name when I'm considering Human Resources activities, but by firstname when I'm looking for their phone numbers. *No cascaded update necessary of linked relationship fields in other tables, because autonumber is only used for linking tables and therefore will never change. The implication is that the SSN and Employee_Name MAY change. So what? There's next to zero effort required to set Cascading Updates when you set the relationships among tables. And while an Autonumber may not change, you can re-record a row of data and get a NEW autonumber, then delete the old record. Where's your foreign key now?! *Prevention of duplicates is improved, since data fields are used to check for duplicates, instead of an (always unique) autonumber field this can also be achieved with the composite unique index as shown above in the Access books example. Ahem! ?"Duplicates"? Are the following employees the same person: John Doe J. J. Doe And what about John and his son John, who both work for your company, both live at the same address, and both have the same last name. ?Duplicates?! Cons of BlueClaw approach *??? I would appreciate your comments / opinion on the BlueClaw approach, because I currently have the feeling that I'm missing something that explains why so many people use autonumber fields as primary (artificial) key. If the BlueClaw approach is the best one, I'm considering to use it as a standard in new database design questions. JOPO (just one person's opinion) Any approach to this that claims to be the one and only appropriate way to do this is probably wrong! Use what works for you. Regards Jeff Boyce Microsoft Office/Access MVP Best regards, M. |
#3
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
Thanks for your reply, but unfortunately this doesn't answer my question. I'm
sorry to distract you with minor issues, like the SSN and name fields. These were copied from the Blue Claw example on their website. Of course I'm aware of full name issues, but that's not my question. My main question is: are there negative aspects associated with using a primary key based on data fields versus using a primary key based on an artificial primary key as generated with an autonumber field? In both cases the autonumber field would be used for defining relations between tables. Until now my answer would be: there are no negative aspects associated with the data fields approach. Best regards, M. "Jeff Boyce" wrote: You may have just inflamed a long-running religious war about proper primary keys.g See comments in-line below... "M." wrote in message ... Dear all, Although many Microsoft Access books advise to set an autonumber field as primary index (a so called pseudo primary key), "psuedo" implies "not real" -- a primary key is a unique identifier, no matter where it comes from. It isn't a question of "real". http://www.blueclaw-db.com/database_link_tables.htm advises to use real data to define a (composite) primary key. In summary, this results in the following two designs: Microsoft Acces books setup for Employee table Employee_ID (autonumber, primary key) SSN (social security number, composite index key1) you will want to be very careful about capturing/displaying SSNs. Moreover, not every "person" has (or cares to share) one. How will you handle a Null SSN? Employee_Name (full employee name, composite index key2) No, no no! If you combine more than one fact in a single field, you have to work extra hard to do simple things, like, say, sort by LAST NAME! Use FirstName and LastName fields, then use a query to concatenate them when needed. composite index SSN + Employee_Name = unique can you say "identity theft"? what makes you think that SSN + Employee_Name will be unique? BlueClaw setup for Employee table Employee_ID (autonumber, unique index) Why? By definition, an Autonumber is supposed to already be unique, so you wouldn't gain anything by indexing it. SSN (social security number, (composite) primary key1) (see above) Employee_Name (full employee name, (composite) primary key2) (see above) In both approaches, Employee_ID would be used as a foreign key in other tables to define the relationship with the Employee table. If you go to the effort of creating a composite primary key, then why would you not also go to the effort to "migrate" that key (i.e., all fields) to the "child" tables? Are there any negative aspects associated with the BlueClaw approach? Pros of BlueClaw approach *Display of table is meaningful, because it's sorted on primary index No, NO, NO!! Access tables store data, Access forms (and reports) display it. Using Access tables to display data is asking for trouble! (can you tell I have some strong feelings on this topic?g - check this newsgroup for others' ideas about using tables to display data. From experience, I don't want inexperienced users mucking about directly in my tables. Instead, I'll guide their use of the data via forms. This is a major difference between, say, Word {everyone knows how to move words around} and Access, a relational database {how many normal people understand relational database design?}) And "meaningful"?! To whom? Just because a set of data is sorted in one order doesn't mean that EVERYONE wants to see it in that order. I, for one, prefer to see a list of employees sorted by last name when I'm considering Human Resources activities, but by firstname when I'm looking for their phone numbers. *No cascaded update necessary of linked relationship fields in other tables, because autonumber is only used for linking tables and therefore will never change. The implication is that the SSN and Employee_Name MAY change. So what? There's next to zero effort required to set Cascading Updates when you set the relationships among tables. And while an Autonumber may not change, you can re-record a row of data and get a NEW autonumber, then delete the old record. Where's your foreign key now?! *Prevention of duplicates is improved, since data fields are used to check for duplicates, instead of an (always unique) autonumber field this can also be achieved with the composite unique index as shown above in the Access books example. Ahem! ?"Duplicates"? Are the following employees the same person: John Doe J. J. Doe And what about John and his son John, who both work for your company, both live at the same address, and both have the same last name. ?Duplicates?! Cons of BlueClaw approach *??? I would appreciate your comments / opinion on the BlueClaw approach, because I currently have the feeling that I'm missing something that explains why so many people use autonumber fields as primary (artificial) key. If the BlueClaw approach is the best one, I'm considering to use it as a standard in new database design questions. JOPO (just one person's opinion) Any approach to this that claims to be the one and only appropriate way to do this is probably wrong! Use what works for you. Regards Jeff Boyce Microsoft Office/Access MVP Best regards, M. |
#4
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
ANY data can be used as a Primary Key AS LONG AS you are 100% sure of no
duplication and/or have code in place to prevent such an occurrance. I've been a database designer for over 30 years, and until I started working with MS Access, there was NO SUCH THING as an "autonumber key." EVERY key/primary key was based on data. And several of my projects written in Access use data-based keys/primaries. "M." wrote: Thanks for your reply, but unfortunately this doesn't answer my question. I'm sorry to distract you with minor issues, like the SSN and name fields. These were copied from the Blue Claw example on their website. Of course I'm aware of full name issues, but that's not my question. My main question is: are there negative aspects associated with using a primary key based on data fields versus using a primary key based on an artificial primary key as generated with an autonumber field? In both cases the autonumber field would be used for defining relations between tables. Until now my answer would be: there are no negative aspects associated with the data fields approach. Best regards, M. "Jeff Boyce" wrote: You may have just inflamed a long-running religious war about proper primary keys.g See comments in-line below... "M." wrote in message ... Dear all, Although many Microsoft Access books advise to set an autonumber field as primary index (a so called pseudo primary key), "psuedo" implies "not real" -- a primary key is a unique identifier, no matter where it comes from. It isn't a question of "real". http://www.blueclaw-db.com/database_link_tables.htm advises to use real data to define a (composite) primary key. In summary, this results in the following two designs: Microsoft Acces books setup for Employee table Employee_ID (autonumber, primary key) SSN (social security number, composite index key1) you will want to be very careful about capturing/displaying SSNs. Moreover, not every "person" has (or cares to share) one. How will you handle a Null SSN? Employee_Name (full employee name, composite index key2) No, no no! If you combine more than one fact in a single field, you have to work extra hard to do simple things, like, say, sort by LAST NAME! Use FirstName and LastName fields, then use a query to concatenate them when needed. composite index SSN + Employee_Name = unique can you say "identity theft"? what makes you think that SSN + Employee_Name will be unique? BlueClaw setup for Employee table Employee_ID (autonumber, unique index) Why? By definition, an Autonumber is supposed to already be unique, so you wouldn't gain anything by indexing it. SSN (social security number, (composite) primary key1) (see above) Employee_Name (full employee name, (composite) primary key2) (see above) In both approaches, Employee_ID would be used as a foreign key in other tables to define the relationship with the Employee table. If you go to the effort of creating a composite primary key, then why would you not also go to the effort to "migrate" that key (i.e., all fields) to the "child" tables? Are there any negative aspects associated with the BlueClaw approach? Pros of BlueClaw approach *Display of table is meaningful, because it's sorted on primary index No, NO, NO!! Access tables store data, Access forms (and reports) display it. Using Access tables to display data is asking for trouble! (can you tell I have some strong feelings on this topic?g - check this newsgroup for others' ideas about using tables to display data. From experience, I don't want inexperienced users mucking about directly in my tables. Instead, I'll guide their use of the data via forms. This is a major difference between, say, Word {everyone knows how to move words around} and Access, a relational database {how many normal people understand relational database design?}) And "meaningful"?! To whom? Just because a set of data is sorted in one order doesn't mean that EVERYONE wants to see it in that order. I, for one, prefer to see a list of employees sorted by last name when I'm considering Human Resources activities, but by firstname when I'm looking for their phone numbers. *No cascaded update necessary of linked relationship fields in other tables, because autonumber is only used for linking tables and therefore will never change. The implication is that the SSN and Employee_Name MAY change. So what? There's next to zero effort required to set Cascading Updates when you set the relationships among tables. And while an Autonumber may not change, you can re-record a row of data and get a NEW autonumber, then delete the old record. Where's your foreign key now?! *Prevention of duplicates is improved, since data fields are used to check for duplicates, instead of an (always unique) autonumber field this can also be achieved with the composite unique index as shown above in the Access books example. Ahem! ?"Duplicates"? Are the following employees the same person: John Doe J. J. Doe And what about John and his son John, who both work for your company, both live at the same address, and both have the same last name. ?Duplicates?! Cons of BlueClaw approach *??? I would appreciate your comments / opinion on the BlueClaw approach, because I currently have the feeling that I'm missing something that explains why so many people use autonumber fields as primary (artificial) key. If the BlueClaw approach is the best one, I'm considering to use it as a standard in new database design questions. JOPO (just one person's opinion) Any approach to this that claims to be the one and only appropriate way to do this is probably wrong! Use what works for you. Regards Jeff Boyce Microsoft Office/Access MVP Best regards, M. |
#5
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
"M." wrote in message
... My main question is: are there negative aspects associated with using a primary key based on data fields versus using a primary key based on an artificial primary key as generated with an autonumber field? In both cases the autonumber field would be used for defining relations between tables. There's no reason to have an Autonumber field AND a "natural" primary key. If you're going to create relationships, they will always be based on the primary key. You cannot change that. As Jeff said, this really is a religious war, so I won't say any more. g -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) |
#6
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
A primary key is a SINGLE field used as the record identifier. It really
should have no meaning to the user and in most cases never seam by users. In all the databases that have worked with the have multiple fields for a primary key have made it a lot harder to do anything, even things that should be simple. I would never create one that way. I probably am glad the there are people that recommend it (like Blue Claw) or actually do it so that I will get lots of work fixing the issues it causes. -- Boyd Trimmell aka HiTechCoach http://www.hitechcoach.com http://www.officeprogramming.com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200712/1 |
#7
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
On Tue, 4 Dec 2007 17:53:43 -0500, "Douglas J. Steele"
wrote: "M." wrote in message ... My main question is: are there negative aspects associated with using a primary key based on data fields versus using a primary key based on an artificial primary key as generated with an autonumber field? In both cases the autonumber field would be used for defining relations between tables. There's no reason to have an Autonumber field AND a "natural" primary key. If you're going to create relationships, they will always be based on the primary key. You cannot change that. As Jeff said, this really is a religious war, so I won't say any more. g This works for me. It seems to go against what you are saying. Sub CreateTest() With CurrentProject.Connection ..Execute _ "CREATE TABLE Cities" & _ " (city_id IDENTITY (1,1) NOT NULL UNIQUE" & _ ",city_name VARCHAR (30) NOT NULL" & _ ",PRIMARY KEY (city_name));" ..Execute _ "CREATE TABLE States" & _ " (state_id IDENTITY(1,1) NOT NULL UNIQUE" & _ ",state_name VARCHAR (30) NOT NULL" & _ ",PRIMARY KEY (state_name));" ..Execute _ " CREATE TABLE Locations" & _ " (location_id IDENTITY (1,1) NOT NULL UNIQUE" & _ ",city_id INTEGER NOT NULL" & _ " REFERENCES Cities (city_id)" & _ ",state_id INTEGER NOT NULL" & _ " REFERENCES States (state_id)" & _ ",PRIMARY KEY (city_id, state_id));" End With End Sub |
#8
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
M. wrote:
BlueClaw setup for Employee table BlueClaw are full of cr*p. The middle sentence in the following is exceedingly arrogant. "You may look at this design and say you have always seen the Employee_ID set as the table primary key. No matter what your teacher or books say - this would be incorrect. See why you almost never use an artificially generated numeric ID as a primary key." As far as thier "Table Design Example - Detail Table" goes it's based on a very faulty assumption. That the employee only does one task throughout the day. And that's seldom the case. And they don't explain themselves very well either. As Jeff states this can become a religious war. In my opinion use autonumber primary keys on every table and unique and duplicate indexes as appropriate. 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 Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#9
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
A primary key does NOT have to be a single field.
A primary key is simply a special unique index, and therefore can contain up to ten separate fields. Perhaps you're thinking of an Autonumber field when you say "It really should have no meaning to the user and in most cases never seam by users." -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Boyd Trimmell aka HiTechCoach via AccessMonster.com" u14500@uwe wrote in message news:7c3433c66efd9@uwe... A primary key is a SINGLE field used as the record identifier. It really should have no meaning to the user and in most cases never seam by users. In all the databases that have worked with the have multiple fields for a primary key have made it a lot harder to do anything, even things that should be simple. I would never create one that way. I probably am glad the there are people that recommend it (like Blue Claw) or actually do it so that I will get lots of work fixing the issues it causes. -- Boyd Trimmell aka HiTechCoach http://www.hitechcoach.com http://www.officeprogramming.com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200712/1 |
#10
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
On Tue, 04 Dec 2007 17:58:20 -0600, Michael Gramelspacher
wrote: This works for me. It seems to go against what you are saying. Sub CreateTest() With CurrentProject.Connection .Execute _ "CREATE TABLE Cities" & _ " (city_id IDENTITY (1,1) NOT NULL UNIQUE" & _ ",city_name VARCHAR (30) NOT NULL" & _ ",PRIMARY KEY (city_name));" .Execute _ "CREATE TABLE States" & _ " (state_id IDENTITY(1,1) NOT NULL UNIQUE" & _ ",state_name VARCHAR (30) NOT NULL" & _ ",PRIMARY KEY (state_name));" .Execute _ " CREATE TABLE Locations" & _ " (location_id IDENTITY (1,1) NOT NULL UNIQUE" & _ ",city_id INTEGER NOT NULL" & _ " REFERENCES Cities (city_id)" & _ ",state_id INTEGER NOT NULL" & _ " REFERENCES States (state_id)" & _ ",PRIMARY KEY (city_id, state_id));" End With End Sub Well, sure, it works. You're creating autonumber primary keys. It *works*, everyone agrees with that; the question would be is it the *only* way that works, or even the *best* way? Many would disagree. In one particular case I'd really have to disagree with you. The US and Canadian postal services have defined unique, stable (well *almost* stable), short two-letter state codes. My States table would be more like ..Execute _ "CREATE TABLE States" & _ " (state_code CHAR(2) NOT NULL UNIQUE" & _ ",state_name VARCHAR (30) NOT NULL" & _ ",PRIMARY KEY (state_code));" As for making the city_name or state_name the primary key of its table... state_name you can get away with but certainly not city_name; there are many cities named Springfield, and New Mexico even has two towns named Los Alamos (the famous one with the rad lab, and a tiny village north of Las Vegas - no, not the one in Nevada). John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|