Charlotte Foust
cfoust at infostatsystems.com
Tue Apr 24 10:21:03 CDT 2007
Have you been singing its praises lately, John? ;-} I use a very similar 2 field table for the same purposes in Access apps. In .Net, we generally stuff the info into a table in an xml file instead of in the database. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Tuesday, April 24, 2007 8:13 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] OT a little - Code Tables Which little ditty is that? Are you referring to SysVars? 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 John Bartow Sent: Tuesday, April 24, 2007 10:39 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] OT a little - Code Tables Hi Ed, My ponderings... I've seen codes like this before - in a USDA database. I've seen arguments on both sides as to its compliance with relational theory. In any case IMO it is a confusing way to handle lookup values as it (obviously) contains more than one type of value. As long as you retain the same programmer it probably doesn't matter - but who can do that anymore? So all I can advise is to document, document, document! The one exception IMO for holding more than one type of value in a lookup table is if the item will _never_ have more than one value - then JC's little ditty works nice. In that case it is handled via code not relational structure. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tesiny, Ed Sent: Tuesday, April 24, 2007 9: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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com