Death to Lookup Fields (am I right?) 3

[smandoli9]

Our database has many tables that have many lookup fields. I am the old-style purist who hates lookup fields, but I can't convince Upper Admin how evil they are. My peer at work who maintains the dba structure seems completely unaware of their dangers and liabilities.

For my part, I constantly struggle with needing to determine the primary key datum and only having the queried text-based result visible.

Also, I believe all the hidden little queries embedded in our tables' lookup fields result in an overall cost in run speed. This is a critical issue as our dba is already overstretched (should be MSDE, but instead is running on Access 97).

I want knowledge-ammo to convince Upper Admin we should dismantle all lookup fields in tables. But I can't find this opinion against lookups anywhere on the Web at this time (I've searched a lot). And I don't have the time or knowledge to set up benchmarks to prove the runtime issue. Anyway our dba is so bloated and fragile, poking at is perilous business.

Is there an article on the Web that takes apart lookup fields? Or would someone like to write one on the spot?

Or, am I wrong about this whole issue?

S. Mandoli.

 

[Steve101]

Well, I have got to cross the floor. I have just found my own VERY good reason why to NOT use Lookup fields; Actually its related to an earlier comment made by LittleSmudge. The comment was:

"If Lookup fields were such great and wonderful things then SQL Server would have the feature. It doesn't >>> They aren't"

Well folks, you really come into problems when you try to rehost Access data (with lookup fields) to SQL Server. The Upsizing exercise suddenly becomes a real PITA, as the concept of simply re-connecting your frontend to your new data backend via ODBC and you're back in business just DOESNT WORK any more.

Fortunately I've only got a couple of these little demons hanging around in one system, but I'm going to have to review all of my forms and reports in this system because of them.

Good enough reason for me never to touch the little gremlins again (at lease until MS makes SQL Server fully compatible at the data definition end!!). The lesson here: If you have any future possibility of having to upsize from Access to any other industrial strength database, then DONT USE LOOKUPS!

Cheers,
Steve

 

[Blackshark]

Erm Gents,

A very interesting discussion, one that I think I agree with if I only I could 100% understand what is meant by a lookup field. Any chance of posting a sample DB file?

Thanks Tim

 

[Quehay]

Thanks for that tip Steve--I'm going to do SQL-Server migrations soon, and it's good to find out about that the EASY way!!

 

[smandoli9]

Steve101:

Now THAT'S the kind of dirt I was looking for. Thank you! I feel very privileged, not only to now have the 2 main thread contributers in "my corner", but also to have generated so much traffic.

(Minus the discussion on macros. Yes, crew, please start a separate thread, or 2 threads for all that ventilating. However Darrylles' comments on the true topic were much valued.)

Blackshark, I think "Help" will get you where you want to go on this one better than anything.

Grateful to the community
Smandoli

 

[LittleSmudge]

Blackshark - I think if you look at the 14th post in this thread the explaination there will cover it.

In the table design, create a text field then at the bottom of the table design window click on the Lookup tab instead of the general field. Play around in there long enough to understand what we're talking about then NEVER go there again .



G LS

 

[Steve101]

Smandoli,

Glad to provide the feedback, but I'm a little redfaced, as I think I may have over re-acted to the size of the upsizing problem caused by the lookup fields; what happened on one of my forms, is that for some strange reason, I had to change the ORDER BY clause associated with the Lookup field, for the control to appear populated in the form. The lookup field itself was not entirely to blame.

That being said, I think upsizing itself will not necessarily be an (immediate) problem, as the mdb frontend itself will continue to use the inherrited combo/listbox control properties originally inheritted from the lookup field definition, where ever the field is referenced on reports or forms. Further development practices will however of course have to be changed.

Just thought that I needed to clarify this situation. I know it depletes the ammunition a little; sorry about that.

Regards,
Steve

 

[basil3legs]

Surely you are missing the main merits of look-up tables. They allow the user to easily enter data from a list but actually link via an index of some sort. The idea of this is to maintain data integrity. If everyone is typing in their choices, you have the problem of people not being able to spell or type or just put different words in for the same thing. If you use a lookup table, you get none of these problems. You also don't have several copies of the same information across your database tables again destroying data integrity. They are a key feature of relational databases, simply setting up relationships between data tables.

I am trying to convert an old paradox database to access and there is customer datails in each record and you have the same company over and over again spelt differently each time. I have now put the customers in a seperate table and used this as a lookup table linked to the main data just by the reference number and it cures this problem. Every record now has the same customer details for the same customer.

Surely this is a big plus, maybe you aren't using the lookup tables correctly or don't realise the true use of them or are just putting too much emphasis on the awkwardness in setting up and maintaining them without considering the real benefits!! You need to look at them from the users point of view as well.

 

[LittleSmudge]

Sorry basil3legs - you've missed the point.

What you say in your first paragraph is all very valid but lookup fields is NOT the way to do that.
To do what you are talking about use COMBO BOXES on FORMS.

Keep users away from tables. - They can do far too much damage there.
If your users are getting to see the actual tables that underly your database then you have NO CONTROL whatsoever over your data integrity.


You say You need to look at them from the users point of view as well.
Well I'd say USERS should NEVER see the £$&%£& things. And professional developers never WANT to see them. They hide the true functionality of the schema and make maintenance a real nightmare.



G LS

 

[basil3legs]

Sorry LittleSmudge, you are right, I have misunderstood. The database I was talking about does indeed use Combo Boxes on forms rather than in the table itself. I do have a database which does use lookup tables but this is only used by myself and normally through a form anyway. You are right that it is very messy in the table view!!