Jim Dettman
jimdettman at verizon.net
Wed Apr 25 08:10:29 CDT 2007
Ed, Well here's a little more to think about<g>. I started thinking about this more and more yesterday (after I made my last post) and took some time to dig around on this. Part of the reason was that in the past, I had always employed the multiple table approach, but when I moved into the FoxPro world, the single table approach was very common. I had been reluctant at first to use it, but found that the concept worked well and I never had a problem with it. The argument given to me was the one I gave to you and at the time, I didn't see what was wrong with it. As it turns out, a single lookup table does violate relational theory (it actually violates 2NF and I'm not sure why I never understood that before). But in a practical where the rubber meets the road view, there is little difference. It's the same thing with surrogate keys; they are a violation of relational theory, yet because we have to deal with computer systems, their use is widespread and accepted. I'll try and explain a bit. If you had this in your single lookup table: LookupID Lookup Type Lookup Code Lookup Description 1 Charge MC Master Card 2 Charge VISA Visa 3 Charge DISC Discover 4 Shipping FEDX Federal Express 5 Shipping UPSG UPS Ground 6 Shipping UPS2 UPS 2nd Day Air and the database stood on its own, with nothing to restrict the table by lookup type (just tables), you'd have a problem. If I were to place an order for example, it should be impossible to have a shipping type of "MC", which is a type of charge. The correct fix would be to break out the above into two tables; tblChargeTypes and tblShippingTypes. The real world short cut fix I employ is to always access the table by a parameterized view. It always works. I can't get a list of records back that are mixed. Nor with the way my code is written ever fail to provide the parameter (Charlotte hit it on the head yesterday; I have a pick list class, which requires the lookup type or it returns nothing). Through my app and coding, the view of the database is "normalized". On it's own however it is not. Yet it's obvious to anyone looking at the data in the table what is going on. Is this going to stop me from using the single table approach? Probably not. Just like using surrogate keys. Both solve practical problems and carry no real serious side affects. FWIW 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 7:52 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] OT a little - Code Tables Many thanks to all who responded, you've given me a lot to think about. Spent most of the day trying to get the data organized in a way I can use it productively. Made some good progress today and I hope this will continue. I hope the new programmer I requested goes through and we can all sit down and work this out. In closing for the night, all I can say is "This list is ALL GOOD" and I mean that sincerely. Thanks for all your help and comments! Ed ________________________________ From: accessd-bounces at databaseadvisors.com on behalf of Tesiny, Ed Sent: Tue 4/24/2007 9:59 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 <http://www.databaseadvisors.com/>