JWColby
jwcolby at colbyconsulting.com
Wed Apr 25 10:53:14 CDT 2007
BTW, doing it this way once again makes it (more) normalized - object / object detail. The object is now the lookup type, the detail records apply equally to all objects. 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 Jim Dettman Sent: Wednesday, April 25, 2007 11:38 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] OT a little - Code Tables John, << The one thing I might do is actually break the table down into a LookupType table (charge, shipping, state etc) the reason being simply that people are typing in to this table to fill it. If they typed:>> Yes, that is what I do. I have one maintenance form to maintain those two tables. The tblLookupType is bound to a drop down, then a grid to handle the tblLookupValues, which is filtered by the combo. I also include a field on tblLookupType "UserModify", which is a yes/no indicating if the lookup values are modifiable by the end user. Certain types of lookups that I store in the table are coded into the app in some way shape or form and should not be changed. For example, a Transaction status of Open (0), Closed (1), and Archived (2). Rather then code that in as a property or doing it in code, I just stuff it in the lookup table. This makes it very easy to convert to different languages. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Wednesday, April 25, 2007 9:36 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] OT a little - Code Tables I pretty much have to agree Jim. Given a LookupID as you use to relate the record back to other records then you are good to go. The one thing I might do is actually break the table down into a LookupType table (charge, shipping, state etc) the reason being simply that people are typing in to this table to fill it. If they typed: Chrg Visa and Charge MC Then if you simply filtered in Charge you would not pick up Chrg. If they filled in a second table with Charge and then used that in a combo to select the lookup type, you could use the PKID of the LookupType table and always pick up all Charge records. Of course that now becomes TWO tables to maintain, but still less than N tables. tblLookupType LT_ID LT_Type 1 Charge 2 Shipping 3 State tblLookupDetail LD_ID LD_LTID LD_Details 1 1 MC 2 1 Visa 3 1 Am Ex 4 2 Fed Ex 5 2 UPS 6 2 USPS 7 3 AZ 8 3 CA 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 Jim Dettman Sent: Wednesday, April 25, 2007 9: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. 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 -- 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