A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

PK - To AutoNumber or Not To AutoNumber - That is the Question! :-



 
 
Thread Tools Display Modes
  #21  
Old August 7th, 2007, 10:04 PM posted to microsoft.public.access.tablesdbdesign
Dee
external usenet poster
 
Posts: 644
Default 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  
Old August 7th, 2007, 11:00 PM posted to microsoft.public.access.tablesdbdesign
Dee
external usenet poster
 
Posts: 644
Default 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  
Old August 8th, 2007, 12:20 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old August 8th, 2007, 12:21 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old August 8th, 2007, 12:32 AM posted to microsoft.public.access.tablesdbdesign
Dee
external usenet poster
 
Posts: 644
Default 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  
Old August 8th, 2007, 01:39 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old August 8th, 2007, 09:34 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old August 8th, 2007, 09:48 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old August 8th, 2007, 12:51 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default 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  
Old August 8th, 2007, 02:22 PM posted to microsoft.public.access.tablesdbdesign
Dee
external usenet poster
 
Posts: 644
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.