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 |
#21
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
I seem to have really stirred things up here! :-)
It's so interesting to read the articles suggested and all of your responses. I have come up against something that may just have forced me to use a question number in a survey database I am creating. I have the questions in one table and now am creating the answers table. The kicker is that the answers have been input in Excel and need to be imported into Access. I will be relating the answers to the question number. I had initially used an autonumber questionID, but I'm not sure this will be useful at this point because there is no such thing in the Excel file. What there IS in the Excel file is a unique question number (combination of letters and numbers) for each and every question and these will never change. Opinions or suggestions? Thanks! -- Thanks! Dee "Jamie Collins" wrote: On Aug 6, 12:21 pm, "BruceM" wrote: Then they have a problem that they might have avoided with a more "natural" EmployeeID used as the key, until they decide that since everybody is part of one company everybody's ID should be the same format. There is no single best answer I agree but I think many people see "autonumber primary key" as always being the answer, the panacea. I'm not sure whether the blame lies with the "I tend to use Autonumber PKs for most of my tables" brigade or whether it's Access's "A table must have a primary key for you to define a relationship between this table and other tables in the database [incorrect]. Do you want to create a primary key now?" but, if proposed designs posted in these groups are anything to go by, consideration of candidate keys tends to go no further than "autonumber primary key" Jamie. -- |
#22
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
On another note, I just read something else interesting.
If the human beings responsible for input of the question number, etc. that will be the PK make a mistake, it will be difficult to update. If, however, an autonumber is used for the PK, then the change can be made and cascading to all related tables. Any input would be appreciated. -- Thanks! Dee "Jamie Collins" wrote: On Aug 6, 12:21 pm, "BruceM" wrote: Then they have a problem that they might have avoided with a more "natural" EmployeeID used as the key, until they decide that since everybody is part of one company everybody's ID should be the same format. There is no single best answer I agree but I think many people see "autonumber primary key" as always being the answer, the panacea. I'm not sure whether the blame lies with the "I tend to use Autonumber PKs for most of my tables" brigade or whether it's Access's "A table must have a primary key for you to define a relationship between this table and other tables in the database [incorrect]. Do you want to create a primary key now?" but, if proposed designs posted in these groups are anything to go by, consideration of candidate keys tends to go no further than "autonumber primary key" Jamie. -- |
#23
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
On Tue, 7 Aug 2007 14:04:01 -0700, dee wrote:
What there IS in the Excel file is a unique question number (combination of letters and numbers) for each and every question and these will never change. Well, why not use that field as the Primary Key of the questions table? It's unique; it's stable; it's probably a perfectly good PK. John W. Vinson [MVP] |
#24
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
On Tue, 7 Aug 2007 15:00:02 -0700, dee wrote:
If the human beings responsible for input of the question number, etc. that will be the PK make a mistake, it will be difficult to update. If, however, an autonumber is used for the PK, then the change can be made and cascading to all related tables. You have it backwards. Cascade Update is completely useless for an Autonumber, since an Autonumber field cannot be edited. If instead you use a Text field as the PK, it *can* be edited and cascade updates will take care of the related tables. John W. Vinson [MVP] |
#25
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
Hi John,
I value your advice, help and input. I will use the text fields that make sense. I know that there is a possibility of human error when inputting. Any suggestions on how to handle that? I guess just the validation rules, field size, etc.? Perhaps part of the process will be having the data entry verified after input. I believe you had also guided me recently to the survey database by Duane Hookom. Thanks so much. It has helped to a degree, except that the audience is really those who wish to just modify the questions and survey titles, etc. I am trying to create my database from scratch. Quite a challenge. If you have any other resources or suggestions regarding this type of database, I'd really appreciate hearing about them! Thanks again. -- Thanks! Dee "John W. Vinson" wrote: On Tue, 7 Aug 2007 15:00:02 -0700, dee wrote: If the human beings responsible for input of the question number, etc. that will be the PK make a mistake, it will be difficult to update. If, however, an autonumber is used for the PK, then the change can be made and cascading to all related tables. You have it backwards. Cascade Update is completely useless for an Autonumber, since an Autonumber field cannot be edited. If instead you use a Text field as the PK, it *can* be edited and cascade updates will take care of the related tables. John W. Vinson [MVP] |
#26
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
On Tue, 7 Aug 2007 16:32:02 -0700, dee wrote:
Hi John, I value your advice, help and input. I will use the text fields that make sense. I know that there is a possibility of human error when inputting. Any suggestions on how to handle that? I guess just the validation rules, field size, etc.? Well, to the extent that you want the user to select from a set of existing values (question numbers say), use the builtin tools that do so: Combo Boxes and Listboxes. It's much easier to make an error typing blindly into a blank textbox than selecting from a list. Perhaps part of the process will be having the data entry verified after input. The Form's BeforeUpdate event is the place to do so. You can check for valid values and combinations of values on the form, and set Cancel to True with a warning to the user if there are errors. I believe you had also guided me recently to the survey database by Duane Hookom. Thanks so much. It has helped to a degree, except that the audience is really those who wish to just modify the questions and survey titles, etc. I am trying to create my database from scratch. Quite a challenge. If you have any other resources or suggestions regarding this type of database, I'd really appreciate hearing about them! I've *adapted* Duane's At Your Survey a couple of times to meet different needs. It's hard to top. John W. Vinson [MVP] |
#27
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
On 7 Aug, 12:49, "BruceM" wrote:
EmployeeID, FormID, and Date are not sufficient in combination to verify uniqueness. It sounds to me like the author used a real life example where a real life domain expert said they would be challenged as 'duplicates' by a real life auditor. But, hey, you know best, eh g? Perhaps you could suspend disbelief and concentrate on the point: would the meaningless unique number (petty cash form number) satisfy an auditor that no duplicates had been entered? I can't tell you how to think but would encourage you to open your mind (perhaps it would help if you imagined and Access MVP had written the article g?) My point about EmployeeID is [that] it can be quite inconvenient when the EmployeeID number format changes. In my company some of the EmployeeID numbers went up by 12 I just know that it is a nuisance. If they decide to add a letter prefix to the number (which is now long integer), there is yet another hassle in that the related field also needs to change to a text field. So a meaningless unique number (such as autonumber) solves a potential problem that may never happen in reality. Big deal. I could say, "Autonumbers make the data less readable, necessitating a join to the referenced tables rather than examining just the referencing table itself", noting that I look at a table far more often than a client changes an enterprise key that has been mutually agreed to be considered stable, and you could say, "Big deal." It's a balance. I realize that autonumber is a type of long integer. Last time I looked autonumber could be a 'Replication ID' which is not a 'long integer'. Jamie. -- |
#28
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
On 7 Aug, 16:04, "Jeff Boyce" wrote:
It would appear you've "read in" a lot more than I stated (or feel). You didn't say why you tend to use Autonumber PKs for most of your tables. I don't know how you are feeling. I assume the same goes for the OP. I genuinely don't want to stifle discussion. Please tell us your views. Jamie. -- |
#29
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
"Jamie Collins" wrote in message
oups.com... On 7 Aug, 12:49, "BruceM" wrote: EmployeeID, FormID, and Date are not sufficient in combination to verify uniqueness. It sounds to me like the author used a real life example where a real life domain expert said they would be challenged as 'duplicates' by a real life auditor. But, hey, you know best, eh g? Perhaps you could suspend disbelief and concentrate on the point: would the meaningless unique number (petty cash form number) satisfy an auditor that no duplicates had been entered? I can't tell you how to think but would encourage you to open your mind (perhaps it would help if you imagined and Access MVP had written the article g?) The point of the article seems to have been that a combination of fields would provide adequate demonstration of uniqueness to satisfy the auditor that the record is not a duplicate. My point is that it would not. The meaningless number would not satisfy the auditor, nor would any combination of the fields used in the example. No, I do not claim to "know best", but a natural key argument based on flawed logic does not convince me that the author does either. I expect there is a valid point in the article, but the example gets in the way. The logic would have been flawed no matter who had written the article. On the subject of open-mindedness, do you make any particular claims? ;-) My point about EmployeeID is [that] it can be quite inconvenient when the EmployeeID number format changes. In my company some of the EmployeeID numbers went up by 12 I just know that it is a nuisance. If they decide to add a letter prefix to the number (which is now long integer), there is yet another hassle in that the related field also needs to change to a text field. So a meaningless unique number (such as autonumber) solves a potential problem that may never happen in reality. Big deal. I could say, "Autonumbers make the data less readable, necessitating a join to the referenced tables rather than examining just the referencing table itself", noting that I look at a table far more often than a client changes an enterprise key that has been mutually agreed to be considered stable, and you could say, "Big deal." It's a balance. I prefer to stay away from situations that would involve updating all of the records in a related table. Perhaps this is a result of inexperience, and you would not give such an update a second thought. Companies are bought, sold, merged, and so forth all the time, so even if somebody today says the number is stable, that person may be out of work in a month when the company is bought out and a new system is put in place. I would rather guard against that not improbably circumstance. You are not daunted by the prospect of what to me looks like a massive update. As you said, it's a balance. I realize that autonumber is a type of long integer. Last time I looked autonumber could be a 'Replication ID' which is not a 'long integer'. In table design view the Data Type is listed as Autonumber, and the Field Size as Long Integer. There is probably a semantic reason why the terminology is inaccurate, but these are the terms Access uses. An autonumber can be related only to a Long Integer field. I don't know what a ReplicationID is, or how it differs from other use of an autonumber field. I was responding to your reminding me that autonumber is not a format. Jamie. -- |
#30
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
Hi again, John,
When you adapted his database, did you do so from scratch or modify what was there? I have a few areas that I'm not sure of and don't know if you could provide some guidance: 1. He has all of his answers in the answers table, listed by question number and response, i.e. 1. first choice answer 1. second choice answer 1. third choice answer, etc. I have some about 3 questions that require a choice from about 15 - 25 choices. Should I simply continue his way of doing this, or create sub-tables? 2. On my forms, I have the respondent code and the interviewer code, plus the date and time the survey was filled in. I decided to use the question code as my PK because it is unique - each has its own combination of letters and numbers - however, these other fields aren't numbered. Should I add a fictitious number and have the interviewer code, date, etc. as just another answer, or should I treat these differently? 3. I also have a lot of information about the respondents in a separate table, but I guess this is just additional information that I would add to the one in Duane's db. 4. I have two sets of surveys - one for adults and one for children - the names of the surveys indicate which is which. I suppose there is no need to separate these at all... Just have a table with questions, a table with answers, a table with responses. Sound correct? Thanks! -- Thanks! Dee "John W. Vinson" wrote: On Tue, 7 Aug 2007 16:32:02 -0700, dee wrote: Hi John, I value your advice, help and input. I will use the text fields that make sense. I know that there is a possibility of human error when inputting. Any suggestions on how to handle that? I guess just the validation rules, field size, etc.? Well, to the extent that you want the user to select from a set of existing values (question numbers say), use the builtin tools that do so: Combo Boxes and Listboxes. It's much easier to make an error typing blindly into a blank textbox than selecting from a list. Perhaps part of the process will be having the data entry verified after input. The Form's BeforeUpdate event is the place to do so. You can check for valid values and combinations of values on the form, and set Cancel to True with a warning to the user if there are errors. I believe you had also guided me recently to the survey database by Duane Hookom. Thanks so much. It has helped to a degree, except that the audience is really those who wish to just modify the questions and survey titles, etc. I am trying to create my database from scratch. Quite a challenge. If you have any other resources or suggestions regarding this type of database, I'd really appreciate hearing about them! I've *adapted* Duane's At Your Survey a couple of times to meet different needs. It's hard to top. John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|