Jim Dettman
jimdettman at verizon.net
Wed Apr 25 10:38:11 CDT 2007
John, <<These are the pro side arguments of this type of design that I've heard in the past (and well put BTW).>> Thanks. <<When is the last time anyone here worked on a completely normalized (per Codd) data structure?>> Not in recent history I think<g> <<MO as long as you know which road you're taking, practical is OK.>> That is a great point. I used to be very hung up about getting it "right". It took me quite a while to learn that "right" is not always practical or best for a given situation. You need to know the pros and cons of the various approaches and what the ramifications will be. As long as you know what your working with and can make it clear to others why that route was chosen, then I see nothing wrong with using a more practical approach. That's why even though I now understand that a single lookup table is technically wrong, I probably won't change anything. What I have works and it works pretty well. The only real issue I have to deal with is to remember to use a view rather then binding directly to the table when working with the database directly. For the things I gain in the app, that seems to be a good trade off and a fairly safe one. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Bartow Sent: Wednesday, April 25, 2007 10:07 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] OT a little - Code Tables Hi Jim, These are the pro side arguments of this type of design that I've heard in the past (and well put BTW). As in all things relational - theory is theory and practical is practical. Theory can be taken too far for practicality. When is the last time anyone here worked on a completely normalized (per Codd) data structure? IMO as long as you know which road you're taking, practical is OK. If I hired you as a programmer and you wanted to take this approach I'd be OK with it. But then I've seen a lot reasons to be confident in your abilities. Same with a number of people here. I'd certainly want documentation on it so that the next person who worked on it could understand it without spending a lot of time on it. On the other if some newbie came up with this idea I'd not be quite so accepting. Talent and experience go a long way in determining flexibility ;o) -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Wednesday, April 25, 2007 8:10 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] OT a little - Code Tables 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. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com