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
|
|||
|
|||
Setting Keys for Lookup Tables
I am setting up a series of look up tables. Users will be restricted to the
lookup table list via a combo box in a form. the data being listed is text. On my lookup tables is it better to have an auto generated primary key and the text in a 2nd column or is it ok to make the text list the key? Does either option make it easier to set up queries and reports? |
#2
|
|||
|
|||
Setting Keys for Lookup Tables
On Sep 26, 3:34*pm, Adrian wrote:
I am setting up a series of look up tables. *Users will be restricted to the lookup table list via a combo box in a form. the data being listed is text. *On my lookup tables is it better to have an auto generated primary key and the text in a 2nd column or is it ok to make the text list the key? *Does either option make it easier to set up queries and reports? Either way. If you have an autonumber PK, it just means that the related tables are a little smaller. I would say that not using the autonumber would make setting up queries and reports easier, because it means you could eliminate another table and join. |
#3
|
|||
|
|||
Setting Keys for Lookup Tables
On Sat, 26 Sep 2009 13:34:01 -0700, Adrian
wrote: I am setting up a series of look up tables. Users will be restricted to the lookup table list via a combo box in a form. the data being listed is text. On my lookup tables is it better to have an auto generated primary key and the text in a 2nd column or is it ok to make the text list the key? Does either option make it easier to set up queries and reports? It depends to some extent on the nature of the data being stored. A Primary Key MUST be unique, and it should ideally also be stable and short. If the field is something that changes very rarely, and is not "too big" (for whatever definition of "too big" makes you happy) by all means store the text only. An example might be US States - two-character fixed length, unique, defined state codes, or even the (longer but not huge), stable state names. People's names, corporate department names, etc. are more problematic, since they're more subject to change. I worked in CRO, CBR, and CBI in my corporate history - with the same boss and most of the same coworkers, doing basically the same job. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Setting Keys for Lookup Tables
Hi John,
Even States aren't that stable. West Virginia was carved out of Virginia during the Civil War. In the '70s there was a movement to break California into 3 States. Just a few years ago North Dakota almost became just Dakota! As you might guess, I'm for autonumber primary keys for almost everything. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "John W. Vinson" wrote: On Sat, 26 Sep 2009 13:34:01 -0700, Adrian wrote: I am setting up a series of look up tables. Users will be restricted to the lookup table list via a combo box in a form. the data being listed is text. On my lookup tables is it better to have an auto generated primary key and the text in a 2nd column or is it ok to make the text list the key? Does either option make it easier to set up queries and reports? It depends to some extent on the nature of the data being stored. A Primary Key MUST be unique, and it should ideally also be stable and short. If the field is something that changes very rarely, and is not "too big" (for whatever definition of "too big" makes you happy) by all means store the text only. An example might be US States - two-character fixed length, unique, defined state codes, or even the (longer but not huge), stable state names. People's names, corporate department names, etc. are more problematic, since they're more subject to change. I worked in CRO, CBR, and CBI in my corporate history - with the same boss and most of the same coworkers, doing basically the same job. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Setting Keys for Lookup Tables
On Mon, 28 Sep 2009 07:05:02 -0700, Jerry Whittle
wrote: Hi John, Even States aren't that stable. West Virginia was carved out of Virginia during the Civil War. In the '70s there was a movement to break California into 3 States. Just a few years ago North Dakota almost became just Dakota! As you might guess, I'm for autonumber primary keys for almost everything. And my CONtblStates table has all the Canadian provinces in it... and Nunavut wasn't in existance until pretty recently. It's a judgement call. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|