When not to normalize?

I have been working on a little database and have created lookup tables for
just about everything. (payment type, city, membership type, etc.) I built
them each with an autonumber key. So I have a lot of tables that look like:
CustomerID 1 MembershipType PaymentType
101 1 1

I could have just had one field in the lookup tables and no autonumber id.
Would that be advisable?

Am I getting carried away with the normalizaiton? Won't this make reporting
much harder? I'm not going to have millions of records in my tables.

Thanks for the advice.
Steve

On Sat, 18 Feb 2006 18:25:47 -0800, "scs"
wrote:

I have been working on a little database and have created lookup tables for
just about everything. (payment type, city, membership type, etc.) I built
them each with an autonumber key. So I have a lot of tables that look like:
CustomerID 1 MembershipType PaymentType
101 1 1

I could have just had one field in the lookup tables and no autonumber id.
Would that be advisable?

Am I getting carried away with the normalizaiton? Won't this make reporting
much harder? I'm not going to have millions of records in my tables.

It's part science, and part art; as with anything where aesthetics get
involved, tastes differ.

If some of your lookup tables are just a word for each record, and you
can count on that word being pretty stable, and can ensure that it is
unique, then you could very well just use a single-field lookup table.
E.g. your PaymentTypes table could have one six-letter Text field as
its primary key, with values Cash, Check, VISA, MC, AMEX, DISC. It's
short, it's stable, and it's unique; and yes, it will save you one
more join in your reports. Another example is US States (and Canadian
provinces) - they have a unique two-letter Postal Service code which
is unique, short, and *almost* completely stable (Canada changed the
codes for Nunavut and Labrador a few years back).

Cities, on the other hand, are iffier. I believe that there are cities
named Springfield in almost - or every - state in the US, so city
names per se are NOT unique. (There are even two cities named Los
Alamos in New Mexico).

Where "surrogate keys" like Autonumbers shine is for entities like
people's names: names are not unique, they are not stable, and they
are not particularly short, so they fail on all three criteria for a
good key.

But you're right - it is certainly NOT essential to have an autonumber
PK for every table!


John W. Vinson[MVP]

Excellent advice! Thank you. I think I'll simple things up a bit.

"John Vinson" wrote in message
...
On Sat, 18 Feb 2006 18:25:47 -0800, "scs"
wrote:

I have been working on a little database and have created lookup tables
for
just about everything. (payment type, city, membership type, etc.) I
built
them each with an autonumber key. So I have a lot of tables that look
like:
CustomerID 1 MembershipType PaymentType
101 1 1

I could have just had one field in the lookup tables and no autonumber id.
Would that be advisable?

Am I getting carried away with the normalizaiton? Won't this make
reporting
much harder? I'm not going to have millions of records in my tables.

It's part science, and part art; as with anything where aesthetics get
involved, tastes differ.

If some of your lookup tables are just a word for each record, and you
can count on that word being pretty stable, and can ensure that it is
unique, then you could very well just use a single-field lookup table.
E.g. your PaymentTypes table could have one six-letter Text field as
its primary key, with values Cash, Check, VISA, MC, AMEX, DISC. It's
short, it's stable, and it's unique; and yes, it will save you one
more join in your reports. Another example is US States (and Canadian
provinces) - they have a unique two-letter Postal Service code which
is unique, short, and *almost* completely stable (Canada changed the
codes for Nunavut and Labrador a few years back).

Cities, on the other hand, are iffier. I believe that there are cities
named Springfield in almost - or every - state in the US, so city
names per se are NOT unique. (There are even two cities named Los
Alamos in New Mexico).

Where "surrogate keys" like Autonumbers shine is for entities like
people's names: names are not unique, they are not stable, and they
are not particularly short, so they fail on all three criteria for a
good key.

But you're right - it is certainly NOT essential to have an autonumber
PK for every table!


John W. Vinson[MVP]

Steve:

I'd just add one thing to what John has said:

If you do use 'natural' keys then be sure that you enforce a referential
cascade update operation in the relationship between the referenced table and
the referencing table(s). That way if you change the value of a key in the
referenced table, e.g. John's example of the Canadian provinces, the values
in the referencing table(s) will automatically change.

Ken Sheridan
Stafford, England

"scs" wrote:

Excellent advice! Thank you. I think I'll simple things up a bit.

"John Vinson" wrote in message
...
On Sat, 18 Feb 2006 18:25:47 -0800, "scs"
wrote:

I have been working on a little database and have created lookup tables
for
just about everything. (payment type, city, membership type, etc.) I
built
them each with an autonumber key. So I have a lot of tables that look
like:
CustomerID 1 MembershipType PaymentType
101 1 1

I could have just had one field in the lookup tables and no autonumber id.
Would that be advisable?

Am I getting carried away with the normalizaiton? Won't this make
reporting
much harder? I'm not going to have millions of records in my tables.

It's part science, and part art; as with anything where aesthetics get
involved, tastes differ.

If some of your lookup tables are just a word for each record, and you
can count on that word being pretty stable, and can ensure that it is
unique, then you could very well just use a single-field lookup table.
E.g. your PaymentTypes table could have one six-letter Text field as
its primary key, with values Cash, Check, VISA, MC, AMEX, DISC. It's
short, it's stable, and it's unique; and yes, it will save you one
more join in your reports. Another example is US States (and Canadian
provinces) - they have a unique two-letter Postal Service code which
is unique, short, and *almost* completely stable (Canada changed the
codes for Nunavut and Labrador a few years back).

Cities, on the other hand, are iffier. I believe that there are cities
named Springfield in almost - or every - state in the US, so city
names per se are NOT unique. (There are even two cities named Los
Alamos in New Mexico).

Where "surrogate keys" like Autonumbers shine is for entities like
people's names: names are not unique, they are not stable, and they
are not particularly short, so they fail on all three criteria for a
good key.

But you're right - it is certainly NOT essential to have an autonumber
PK for every table!


John W. Vinson[MVP]