DWUTKA at marlow.com
DWUTKA at marlow.com
Sun Mar 28 04:22:40 CST 2004
First, don't point yourself. John seems to be doing a fine job there, and seems to be relatively impartial. Next, this is going to be hard to reply to, without being derogatory. So take this with a grain of salt. First, you are talking about a database system written for the US. Someone takes that db, and modifies it for another country. Then, you want to merge the two systems. Now, if the ONLY problem you have are lookup fields, I would say you can just count your blessings. The entire point of this conversation is to determine if there is anything actually wrong with Lookup fields. That doesn't mean that something can't go wrong, but whether there is an inherent flaw in their abilities to perform their actual purpose. You can take practically any feature of practically any software package, and turn it into a flaw, if used outside of the scope of it's original design. You started out with 'it's a performance hit' and 'It takes up extra space'. I then showed that it wasn't a performance hit, and it didn't take up any extra space (if it's indexed, and a relation, you get the same space used whether you use a lookup field or not). You did mention that it is confusing, but my point (and other 'Sayers') is that you can't label it wrong to use if some users are confused. Once again, I am not arguing your preference. I am REALLY not arguing that you should use them. What I am trying to get at, is that we, as a development group, shouldn't label something as 'bad practice', if the only problem involved is just knowledge of how something works. I write a lot of code, and I can't tell you how many things I have coded, which involved code that would wreak total havoc, if it isn't done right. Take for example, subclassing forms. In Access it's bad enough that Access forms are already subclassed, but in VB, you will shoot yourself in the foot, if you run subclassed forms in debug mode, and DON'T un-subclass them before you stop your code. However, I wouldn't say that subclassing a form is bad practice, it's just something you have to use with caution. More importantly, it's something you have to use with an intimate knowledge of it. That actually applies to practically anything. One of my favorite quotes is: 'He who learns, but does not think is lost. He who thinks, but does not learn is in grave danger.' If you apply that to developing a database, if you read a help book, and follow it step by step, you're not really thinking about what you are using, and therefore, you're going to end up lost when it comes time to figuring out what is wrong. At the same time, if you think about what you are doing, but don't learn from the results, then you're REALLY going to shoot yourself in the foot. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com To: 'Access Developers discussion and problem solving' Sent: 3/27/04 4:57 PM Subject: RE: [AccessD] Lookup Fields in Table Design - Coup de Grace A little scenario: At the international company you work for, you have been given two customer databases to work with. One is for your US data, the other from Mexico. Inspecting both, you can clearly see that, years ago, the Mexican db was adapted from the US original. In each db, you see two tables you're interested in, tblAddresses and tblStates. While the Mexican tblAddresses has been modified somewhat, tblStates has remained unchanged, with two fields, StateID and Name, both text. The US version of tblStates contains Alabama to Wyoming, while the Mexican one naturally contains Aguascalientes to Zacatecas, with their abbreviations in the StateID field. Critically, you also notice that table field lookups were also defined for the StateID field in each tblAddresses table. So, you build your frontend and link the US tables. You also link the Mexican tables, which show up as tblAddresses1 and tblStates1. Next, you open the linked tblAddresses (US version). The table lookups do their job, showing 'Alabama' for AL, etc. Happy, you open the linked tblAddresses1 (Mexican version) ... wait ... all hell has broken loose! Instead of the Mexican state names, you see only the StateID codes, along with some US State names ... what the!?!? After some digging, you find the problem: the table field lookup in the Mexican version of tblAddresses. While it works correctly in the backend, it fails in the frontend, because the lookup SQL in the linked tblAddresses1 now refers to the *linked* tblState, which holds the US data, not tblState1, which holds the correct Mexican data! This demonstrates that table field lookups, by themselves, can cause incorrect query results. But it gets worse. The original programmer that setup the databases didn't know too much about table design, and did not establish a relationship between tblAddresses and tblStates, only indexes. This means that you now have a situation where you can enter incorrect values into a table from the wrong lookup table! If you were under pressure, and didn't catch this error, and allowed data entry on a frontend with this problem, the results would be disastrous. What has been shown here, and validated in my testing, is that table field lookups can cause inaccurate results, and an insidious form of data corruption. It is important to emphasize that table field lookups are the single point of failure in this scenario. Removing them from the backend tables solves the problem, by making you match the correct main/lookup tables in a frontend query. If you had used any method other than table field lookups initially, you would have avoided this fiasco completely. By violating the principle of client/server separation, and embedding client-side UI elements in data tables, table field lookups are data land mines, just waiting to bite you at some later date. When you use them, you are embedding an implicit external context assumption into your table. In this case, the assumption was 'my external name will always be tblState'. Anytime this assumption is violated, table field lookups will fail. For this, and the other reasons that I have outlined, table field lookups are a BAD IDEA. Match point: Naysayers. Game: Naysayers. Hey, Susan ... your marshmallows have grown cold!. Pass 'em over here, I'm in the mood for s'mores! -Ken -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com