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?

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.

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]

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]

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]