[AccessD] OT a little - Code Tables

I pretty much have to agree Jim.  Given a LookupID as you use to relate the
record back to other records then you are good to go.  The one thing I might
do is actually break the table down into a LookupType table (charge,
shipping, state etc) the reason being simply that people are typing in to
this table to fill it.  If they typed:

Chrg		Visa

and 

Charge	MC 

Then if you simply filtered in Charge you would not pick up Chrg.

If they filled in a second table with Charge and then used that in a combo
to select the lookup type, you could use the PKID of the LookupType table
and always pick up all Charge records.

Of course that now becomes TWO tables to maintain, but still less than N
tables.

tblLookupType
LT_ID		LT_Type
1		Charge
2		Shipping
3		State

tblLookupDetail
LD_ID		LD_LTID	LD_Details
1		1		MC
2		1		Visa
3		1		Am Ex
4		2		Fed Ex
5		2		UPS
6		2		USPS
7		3		AZ
8		3		CA


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Wednesday, April 25, 2007 9: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.

FWIW
Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tesiny, Ed
Sent: Tuesday, April 24, 2007 7:52 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] OT a little - Code Tables

Many thanks to all who responded, you've given me a lot to think about.
Spent most of the day trying to get the data organized in a way I can use it
productively.  Made some good progress today and I hope this will continue.
I hope the new programmer I requested goes through and we can all sit down
and work this out.  In closing for the night, all I can say is "This list is
ALL GOOD" and I mean that sincerely.  Thanks for all your help and comments!
Ed