DWUTKA at marlow.com
DWUTKA at marlow.com
Fri Mar 26 14:08:33 CST 2004
Gustav, my issue is this, saying that something is bad, and not giving actual reasons, is pushing your preferences on to people, as fact, instead of what they are, preferences. Everyone has had to learn something new at some point in time. When you learn something new, it's very handy to listen to people that have been there, and done that. It makes the learning curve a lot less steep! In fact, IMNSHO, that is the very purpose of this list. However, when someone is new to a subject, and you are teaching them your knowledge and experience, they probably won't have the foundation set, to allow them to differentiate between what is fact, and what is preference. That is YOUR job, as the teacher. There is nothing wrong with passing along your preferences, especially if you back up why you do what you do. However, saying that Lookups are BAD PRACTICE, is NOT passing on a fact, it's passing on a preference. If the statements made were something along the lines of, 'When you use Lookups, you need to make sure that stuff is well documented, that you are smart in your relational design, and that you understand how they work', then I would not have said a word. But instead, the general comment was 'Lookups are bad, because they bloat and slow down a database, and they are confusing.'. Very broad reasoning, and to a beginner, that could easily cement the idea that they should never use them. However, the 'bloat' is just part of indexing, which is something you definitely shouldn't avoid, indexes are good. The slowdown has yet to be proven (haven't seen it myself at all, in fact I proved otherwise), and the 'confusion' part is a catch 22, so it can't really be used. What may be confusing to some users, can be very helpful for others. I use ADO far more then I use DAO. I use ADO because I like the flexibility to switch between datasources. It makes my code a lot more portable. Do I pass on to others that DAO is bad, because I prefer ADO? No. In fact, I readily admit that DAO is native to JET, so it is more powerful when dealing with an .mdb. It's faster too. Faster and More powerful definitely don't point to 'do not use'. However, I try to explain that unless I absolutely need speed and/or power, I go for portability. With today's computers, the speed isn't nearly as critical in most of the stuff I develop, and the power only comes into play when there is specific stuff I need to do, which is rare, that DAO can do, and ADO can't. I leave it up to the learner, to determine what their preference is going to be. Have you ever heard an 'expert' tell a 'newbie' that field sizes are important to prevent database bloat? I have. Not kidding. I have seen so many databases where you can tell someone went beserk trying to 'maximize' their field design by messing with text field sizes. Hopefully the expert wasn't a complete moron, and actually knew that text fields take up only the space they need, (data plus one byte), and that using the text field size option merely limits what the user can actually put in. If that's the case, the 'expert' just prefers to control data input at the table level, instead of at an interface level. That's just a preference. But to gloss over the reason, by saying something that 'makes sense', but isn't exactly true, is wrong. That 'newbie' is walking away thinking they have learned a fact, but instead, they were misled. I'm not pointing this at you Gustav, not at all, just on my soap box at the moment. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock Sent: Friday, March 26, 2004 11:04 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Lookup Fields in Table Design Hi all lookuppers Maybe I'm dense but - as mentioned by Ken - if you really need the feature of lookup fields (and I did once for some superuser administrative tasks performed once a year or never, thus not justifying design of eight forms) you simply - in a separate frontend database - create some queries retrieving exactly the fields you need (which excludes the ID), readable aliases for the fields, and lookups as needed. Then everybody is happy. You avoid fiddling with the table design and you can easily redesign the queries if and when needed and you take advantage of a feature of Access which can be handy. And this discussion becomes moot (isn't that the wording for such a situation you use "over there"?) /gustav > Here are "The Evils of Lookup Fields in Tables" from Dev's website: > A Lookup field in a table displays the looked-up value. For instance, if a > user opens a table datasheet and sees a column of company names, what is in > the table is, in fact, a numeric CompanyID, and the table is linked with a > select statement to the company table by that ID. > Any query that uses that lookup field to sort by that company name won't > work. Nor will a query that uses a company name in that field as a criteria. > If a user creates a combobox to select the company using a value list, the > data in the table can be over-written. > Another relationship is created which then creates another set of indexes > when a Lookup field is created, thus bloating the database unnecessarily. > If a combobox based on the lookup is used in a form, and a filter is > applied, the persistent filter effect of Access often saves the filter and > the next time the form is opened, there will be a prompt for the value > (which cannot be provided, thus creating an error). > Reports based on the lookup field need a combobox to display the data, > causing them to run more slowly. The underlying recordsource can also be > modified to include the table, however the index, (unless it was set up > within a proper relationship) may not be optimized. > Lookup fields mask what is really happening, and hide good relational > methodology from the user. > The database cannot be properly upsized to, or queried by, another engine > (without removing all the lookup fields) because no other engines use or > understand them. > If security is implemented, permissions to tables is usually denied, and > RWOP queries are used for data access. There will often be errors that there > are no permissions on a specific table that isn't even being used in a query > (because the lookup field is). If the queries are nested or complex, it can > take some time to track down the lookup that's causing the error (that is, > if it occurs to you). > http://www.mvps.org/access/tencommandments.htm -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com