[AccessD] Code Tables

Debbie and Jim (and anyone else that thinks of this as good design),

Sorry, but it is PURE BAD design. And Jim, just because you
do it in all your apps does not make it right or good design.
Most of the money I make and the highest prices I charge are
for fixing poorly designed Access applications.

IT is NOT relational in any stretch of the imagination.
If you think so, then you need to go back and review
relational theory.

You can make a very customizable interface for the user
with good design also.

So if you want the ultimate in flexibility (and bad design)
here you go:

tblEntity

EntityID
EntityDescription

tblEntityAttribute
EntityAttributeID
EntityID
AttributeID
AttributeValue
AttributeComments

tblAttribute
AttributeID
AttributeDescription

tblRelatedEntities
RelatedEntityID
ParentEntityID
ChildEntityID

There, that is all you will need forever to define whatever
you need to store data for.  Here is an example:

tblEntity
EntityID        EntityDescription
    1            Robert
    2            Sales Order
    3            Address

tblAttribute
AttributeID     AttributeDescription
    1            AddressLine1
    2            City
    3            State
    4            PostalCode
    5            OrderNumber
    6            OrderDate
    7            CustomerNumber

tblEntityAttribute
EntityAttributeID  EntityID   AttributeID  AttributValue  AttributeComments
   1                   3           1        123 IDK St.
   2                   3           2        Bellville
   3                   3           3        TX
   4                   2           5        2007-02-12-01
   5                   2           6        2/12/2007
   6                   2           7        1

tblRelatedEntities
RelatedEntityID  ParentEntityID  ChildEntityID
   1                   1              3

Well, there you go, the ultimate in flexibility and bad design.

Now go back and review what the first 3 normal forms are before
you spout off that a single code table is not bad design and
does not violate normalization rules again. They are not the same
entity. You obviously do not know the definition of 'entity' either.

And Debbie, just because a commercial product has it in it, does
not make it good either.  I have a number of commercial products
out and NONE of them are given to this poor design concept.

Robert

At 07:47 PM 4/24/2007, you wrote:
>Date: Tue, 24 Apr 2007 13:12:35 -0500
>From: "Elam, Debbie" <DElam at jenkens.com>
>Subject: Re: [AccessD] OT a little - Code Tables
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID:
>         <573E90481C9F004C9E598D3A5A9DCDA001D206 at jgexch1.jenkens.com>
>Content-Type: text/plain
>
>I agree.  The only thing I would do is make sure the ID is unique regardless
>of the code type.  Having a multiple field key does cause more problems.  As
>I pointed out, if you want to add the functionality to the front end of
>being able to define some custom combo or list boxes, custom codes are often
>needed too.  This offers the ability to recycle a previous list (Yes/no is a
>common recycled list) or create a new one from scratch.
>
>I use a commercial product that uses a consolidated code table and party
>table for just that reason.  It has made the interface very customizable by
>a reasonably savvy end user with no programming experience and no access to
>the server to add new tables.
>
>The views of each code type are a convenience, not a necessity.
>
>Debbie
>
>-----Original Message-----
>From: Jim Dettman [mailto:jimdettman at verizon.net]
>Sent: Tuesday, April 24, 2007 12:48 PM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] OT a little - Code Tables
>
>
>
>   Just to weigh in; I do this myself in all my apps.  I see little sense in
>setting up a separate table for each one of the lookups when they are all
>the same.
>
>   And I don't agree with Arthur that this violates relational theory.  They
>are all the same entity.  Each one has a type, code, and a description.
>That's it.  No more and no less.
>
>   It would be a different matter if I had something like this:
>
>Type        CodeID       CodeName         Postal Mask   Phone Mask
>OnOrder Form
>Country     USA          United States     #####-####   (###) ###-####
>Null
>Country     CAN          Canada            ### ###      (###) ###-####
>Null
>Ethnicity       1              Puerto Rican       Null           Null
>1
>Ethnicity       2           Mexican            Null           Null
>2
>
>   Now there are multiple entities in the table as each instance does not
>have the same attributes and thus cannot be the same thing.
>
>   This is the point where I break out into a separate table.  But for a
>"lookup value", even though each lookup value may have a different type, I
>keep them all in the same table.
>
>   I find it simplifies things quite a bit and performs better as well.  Lot
>less overhead then having multiple tables, all of which need to be opened,
>closed, indexed, etc.  Coding is less complex overall as well.
>
>My .02
>Jim.