[AccessD] OT a little - Code Tables

Charlotte Foust cfoust at infostatsystems.com
Tue Apr 24 13:14:15 CDT 2007


I think your approach is the cousin to John's SysVars and my
USysSettings tables, Jim.  In my USysSettings table, every value is a
string that gets translated to the appropriate datatype in the public
function that retrieves it.  I break out my multi-record lookup values
that have different structures, i.e., fields that don't apply to other
types, etc., or where they would require a multi-field key to insure
uniqueness.  If the CodeID is numeric in one set of values and a string
in another, they go in different tables anyhow.

I can see using your approach in a class to wrap the separate tables,
but I'd still want the tables broken out. :-}

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Tuesday, April 24, 2007 10:48 AM
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