[AccessD] OT a little - Code Tables

Elam, Debbie DElam at jenkens.com
Tue Apr 24 13:12:35 CDT 2007


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.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tesiny, Ed
Sent: Tuesday, April 24, 2007 10:00 AM
To: dba-ot at databaseadvisors.com; Access Developers discussion and problem
solving
Subject: [AccessD] OT a little - Code Tables

Hi All,
I'm not familiar enough with SQL Server but I have a question regarding
what I call Code Tables.  I use them a lot when I develop an application
in Access, e.g., I'll have a table for counties i.e., county code and
county name or Providers, Provider code and Provider Name.  I have them
as separate tables.  I'm trying to make sense out of the tables and
relationships "my" programmer created.  He has one code table period!
Below is a look as to how it is setup.
 
dbo_tblCodes
CodeType	 CodeId	 CodeName	 OrderOnForm	
Ethnicity	 1	 Puerto Rican	 1	
Ethnicity	 2	 Mexican	 2	
Ethnicity	 3	 Cuban	 3	
Ethnicity	 4	 Other Hispanic	 4	
Ethnicity	 5	 Hispanic, Not Specified/Known	 5	
Ethnicity	 7	 Not of Hispanic Origin	 6	
Ethnicity	 9	 Don't Know/No Answer	 7	
Gender	 1	 Male	 10	
Gender	 2	 Female	 20	
Health	 1	 Poor	 5	
Health	 2	 Fair	 10	
Health	 3	 Good	 15	
Health	 4	 Very Good	 20	
Health	 5	 Excellent	 25	
Health	 9	 Don't Know/No Answer	 80	
 
This is just a little bit of the table but I think you can see his
"logic" here.  Is this a common convention that developers use?  Hate to
see what else I'm going to find as I try to wade through this.
TIA
Ed
 
 
Edward P. Tesiny
Assistant Director for Evaluation
Bureau of Evaluation and Practice Improvement
New York State OASAS
1450 Western Ave.
Albany, New York  12203-3526
Phone:  (518) 485-7189
Fax:  (518) 485-5769
Email: EdTesiny at oasas.state.ny.us
 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

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



More information about the AccessD mailing list