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