Setting Keys for Lookup Tables

[Adrian]

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?

 

[Piet Linden]

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.

 

[John W. Vinson]

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.

 

[Jerry Whittle]

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.

 

[John W. Vinson]

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.