[AccessD] OT a little - Code Tables

Hi Jim,
These are the pro side arguments of this type of design that I've heard in
the past (and well put BTW).

As in all things relational - theory is theory and practical is practical.
Theory can be taken too far for practicality. When is the last time anyone
here worked on a completely normalized (per Codd) data structure?

IMO as long as you know which road you're taking, practical is OK. If I
hired you as a programmer and you wanted to take this approach I'd be OK
with it. But then I've seen a lot reasons to be confident in your abilities.
Same with a number of people here. I'd certainly want documentation on it so
that the next person who worked on it could understand it without spending a
lot of time on it. On the other if some newbie came up with this idea I'd
not be quite so accepting. Talent and experience go a long way in
determining flexibility ;o)

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Wednesday, April 25, 2007 8:10 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] OT a little - Code Tables

Ed,

  Well here's a little more to think about<g>.

  I started thinking about this more and more yesterday (after I made my
last post) and took some time to dig around on this. Part of the reason was
that in the past, I had always employed the multiple table approach, but
when I moved into the FoxPro world, the single table approach was very
common.  I had been reluctant at first to use it, but found that the concept
worked well and I never had a problem with it.  The argument given to me was
the one I gave to you and at the time, I didn't see what was wrong with it.

  As it turns out, a single lookup table does violate relational theory (it
actually violates 2NF and I'm not sure why I never understood that before).
But in a practical where the rubber meets the road view, there is little
difference.  It's the same thing with surrogate keys; they are a violation
of relational theory, yet because we have to deal with computer systems,
their use is widespread and accepted.

  I'll try and explain a bit.  If you had this in your single lookup table:

LookupID   Lookup Type    Lookup Code   Lookup Description
1          Charge         MC            Master Card
2          Charge         VISA          Visa            
3          Charge         DISC          Discover
4          Shipping       FEDX          Federal Express
5          Shipping       UPSG          UPS Ground
6          Shipping       UPS2          UPS 2nd Day Air


  and the database stood on its own, with nothing to restrict the table by
lookup type (just tables), you'd have a problem.  If I were to place an
order for example, it should be impossible to have a shipping type of "MC",
which is a type of charge.

  The correct fix would be to break out the above into two tables;
tblChargeTypes and tblShippingTypes.  The real world short cut fix I employ
is to always access the table by a parameterized view.  It always works.  I
can't get a list of records back that are mixed.  Nor with the way my code
is written ever fail to provide the parameter (Charlotte hit it on the head
yesterday; I have a pick list class, which requires the lookup type or it
returns nothing).  Through my app and coding, the view of the database is
"normalized".  On it's own however it is not.  Yet it's obvious to anyone
looking at the data in the table what is going on.

  Is this going to stop me from using the single table approach?  Probably
not.  Just like using surrogate keys.  Both solve  practical problems and
carry no real serious side affects.