John Bartow
john at winhaven.net
Sat Mar 27 17:12:45 CST 2004
Ken, Do you play poker, were you just holding that in reserve!? ;o) Actually this scenario assumes an awful lot of bad practices to blame it solely on the lookup property. But I get your point. 4 points! Score Stands at: nay-sayers 8 / sayers 8 John PS: However, if the developer had followed my idea of setting all of the table lookup properties back to the default after completing their form development this wouldn't have happened. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Ken Ismert Sent: Saturday, March 27, 2004 4:57 PM To: 'Access Developers discussion and problem solving' 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