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