[AccessD] Access 101 question (I think)

<<Should I have an individual table for each descriptor? This is the way
I mostly do it now. I put in value lists for something like "gender,"
which will most likely stay the same (I don't want to think about the
"what ifs" here). But for other things, I like to leave it open for the
user to input. I then give them a button for "Program Administration"
and a bunch of little forms, which are basically prettied-up continuous
forms, to enter these options. >>

  That is one way.  Another is a single lookup table:

tblLookup
LookupID - Autonum PK
LookupType - Long FK to tblLookupTypes
Value


tblLookupTypes
LookupTypeID - Autonum PK
Description
UserModifable - True/False

 I do most of my systems this way.

 But another way to design something like this is:

tblPeople - One record per person
PersonID - Autonumber PK
FirstName
LastName
etc.

tblAttributeTypes - One record per possible attribute type
AttributeID - Autonumber PK
Description


tblPeopleAttributes - One record per person/attribute
PersonID - Long FK to tblPeople
AttributeID - Long FK to tblAttributeTypes
Value


 Now on the above, *some* might like to add a PeopleAttributeID - Autonum PK
and if so, knock yourself out, but I think it's rather silly.

  With the above a user can add a new attribute they want to keep track of
i.e. "Nose pierced" or "Distance between eyes" and then assign a value for
each person.

  You can also extend this design by adding additional fields to
tblAttributeTypes that defines the allowed value (i.e. Y/N, 1 - 99999,
Lookup list, etc).  If a lookup list you would add a table
(tblAttributeTypeValues) that defines a list of allowed values for a given
attribute.

HTH,
Jim.






-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Clark
Sent: Thursday, April 28, 2005 2:36 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Access 101 question (I think)


I am whipping up a quick program, and there are many "descriptors" in
the main table (i.e. hair, eyes, status, race, etc.). My question is
this:

Should I have an individual table for each descriptor? This is the way
I mostly do it now. I put in value lists for something like "gender,"
which will most likely stay the same (I don't want to think about the
"what ifs" here). But for other things, I like to leave it open for the
user to input. I then give them a button for "Program Administration"
and a bunch of little forms, which are basically prettied-up continuous
forms, to enter these options.

This actually came in handy, on one of my previous programs. Their
"Race" designations were altered, about a year after they began using
the program...asian was split into sub-groups...and they were able to go
in and change this without my help.

I was just wondering whether this is considered acceptable practice, or
if I am being a bit retentive here.

TIA

John W Clark
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com