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.

 

[DonQuichote]

What do you mean by lookup field? Is it a field pointing to another record in another table? There's absolutely nothing wrong with that...

 

[Steve101]

S. Mandoli,

I think that you're possibly not using the Lookup field capability properly. Used correctly, it should be an aid to performance and make applications easier to develop.

Lookup fields which are based on associated other tables, indexed appropriately, and which "hide" the identity type primary key and expose a more meaningful value in the user interface/reporting are a definate PLUS.

Sounds like you and your managers / workmates might need to sit down and have a pow wow about some of the other real underlying issues. This might just be the catalyst for exposing some other things.

Thats my opinion, though its hard to make conclusions without seeing the design/code.
Cheers,
Steve

 

[LittleSmudge]

Well I feel like adding my support SMandoli on this one Steve.

SMondoli,
You have to realise that Access has evolved as a Relational Database system that is capable of some extremely complex tasks. However, one of the markets that MS are aiming at with this product is the UnTechnical Office User. There have been a lot of 'Make things easy for the user' features that have been added to Access that are not, in my opinion, BENEFITS. And this Lookup feature fits firmly into that category.

Lookup fields enable the builder to construct the database 'on the fly', without doing the formal Data Modeling, Data Analysis, Data Structure, Normalsation, etc, etc,. If a developer has done the 'development' correctly then the addition of Lookup fields is an un-necessary additional task that adds nothing to the performance of the finished product. So - I conclude that if a database has tables with Lookup fields in them then either:-
A) It was built by an amateur
B) It was built by someone with time and effort to waste

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

If you are a competent DBAnalyst and you are trying to look into the functionality of a database they are a pain. They make ongoing maintenance much more diffecult because they artificially hide the REAL data that exists.
They do not add to the performance in any way - in a runtime sense - and as such can only be a drain on resources for an already large and over weight database.


However, having said all of the above I do agree that Steve has a point when he says:-
Sounds like you and your managers / workmates might need to sit down and have a pow wow about some of the other real underlying issues. This might just be the catalyst for exposing some other things.


'ope-that-'elps

G LS

 

[Steve101]

LittleSmudge,

Actually, Access has a lot of great things that SQL Server does not have (yet?; eg. pivot tables). Its also built from the ground up to support both extremes of users; ie. total novices and professional programmers. This is of course a double edged sword. Ive seen Access blamed for all sorts of things in the past because of systems which were poorly designed and built and maintained (in fact I make a living fixing some of these systems, so Im not complaining).

The best one I saw was a "programmer" who was trying to convince a company to migrate to SQL Server because of poor Access performance over the network. This individual had embedded (quite innocently I think) a 3 MB matchbox sized image (display size) at the top right of every form, and was then blaming Access for the poor performance. Fortunately I succeeded in saving the company about $20K and more importantly, helped to demonstrate to senior management that Access is more than a toy database if used appropriately. The solution was to reduce the 3MB image to 35K.

Sorry for the digression. Back to lookup fields. Like everything, I think they have their place. I think that their intent is to allow cryptic autonumber type primary keys to be hidden, whilst exposing more meaningful info to the user interface. They should only be used on tables that have made their way into the schematic via a good design process.

Anyway, we've probably done this to death (no relationship to the title intended). S. Mandoli, take heart. I think that voicing strong opinions now and then, done in the right spirit helps to get things moving.

All the best,
Steve

 

[LittleSmudge]

We seem to be in the same business Steve.

The message for SMandoli to take from both our comments is that regardless of the use of Lookup fields or not - the careful structured design of the database schema is the vital foundation of any stable, reliable and easily maintained database application.

G LS

 

[Steve101]

Concur LittleSmudge. Enjoy "talking" to you,
Be well,
Steve

 

[smandoli9]

I think the most relevant comment out of all of the above is from LS:

"If you are a competent DBAnalyst and you are trying to look into the functionality of a database they are a pain. They make ongoing maintenance much more difficult because they artificially hide the REAL data that exists. They do not add to the performance in any way - in a runtime sense - and as such can only be a drain on resources for an already large and over weight database."

Good database design became the biggest theme out of the debate overall, but I take that as a given. (Not that it often is in real life.) And our DBA is not well designed. I remain persuaded that the use (indiscriminate use, perhaps) of Lookup fields is one aspect of the bad design.

About the resource drain aspect, I think it's very significant in my situation.

I have enjoyed the discussion. Gosh, you guys are fastidiously courteous to each other. Well done everybody.

 

[LittleSmudge]

We're just plain nice guys that like each other's company - honest

Actually - the more time you spend on these boards - the more you realise that NO ONE has all the answers and you just stick your two pennyworth in based on your own experience and opinions. But the nature of the place means that you need to leave space for others to air their views. - that way we ALL learn.


Learn lots and prosper, smandoli.

G LS

 

[Steve101]

Yes, this is a FANTASTIC environment to learn and teach, and enjoy the company of like minded people.

One final point on lookup fields, which I'm actually finding quite useful at the moment. I'm developing a prototype of an application which has quite a complex data model, quite a few tables with composite keys; I'm actually using lookup fields to hide the cryptic table autogenerated primary keys in favour of the exposed lookup values.

Seems to work OK in a development environment, pre writing proper front end data entry screens to get some proper data into the model, without compromising design.

Anyway guys,
Till next time ...
Steve

 

[Steve101]

... and another thing; its easier to get stars here, than from my wife.

 

[Quehay]

OK this has gone on forever and on into backslapping, etc. ;-)

But here's some extra input, and I'll be succinct:

-Yeah it's a pain when you want to see the fkey value not the interpretation. You can do this with a query though.

-Yes the lookups are additional overhead. I don't use them typically. Or if I do I save the lookup as a separate query object. Why? So it can be edited while users are in the tables.

*The overhead is probably not that great though relative to other items such as forms bound to entire tables, tab page controls, embedded graphics, excessive joins, etc.

BUT: If you want a "datasheet" subform in a parent form for any reason you need table lookups to have a useful combo box.

 

[LarryDeLaruelle]

My two cents worth.

If by lookups you mean something similar to having a zip code table and looking up the relevant city/state based on the user supplied zip code then I would have to vote in favor of lookups.

Several reasons.
Limits the amount of date the user has to enter and, thus, the number of errors they are likely to make. (Although usually explain that it is a time savings for them.)

In the case of zip codes, removes the need to store redundant data over and over.

If lookups are not used the alternative is combo/list controls that are more difficult to maintan than tables or allowing the user to enter the data freeform (which makes reporting a real pain).

As for overhead, at least in terms of storage space, I would think that storing a couple of digits as an FK is more efficient that storing repeating data.

I agree, however, that this is probably the best site for help, assistance and discussion for Access. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[LittleSmudge]

No Larry that not at all what we are talking about.

If it was I would agree with you.

We are talking about a feature in the design of the TABLE itself whereby you can tell a table that a particular field is a Foreign Key and tell it which table the Foreign Key points to.
THEN in the table's datasheet view the Foreign Key field acts just like a combo box that has had the bound column set to zero width.

It is a wizzy little feature the first few times you come across it - but when you spend a lot of time modifying, updating & generally working with other people's ( usually undocumented ) databases then become a REAL PAIN because you cannot see the ACTUAL data - yet you cannot easily tell that it is not the real data that you are looking at.


G LS

 

[LarryDeLaruelle]

LittleSmudge:

Thanks for the clarification. I'll take back my 2 cents and agree with those who do not like look-ups.

I remember tinkering with them when I first started using Access and thinking they were pretty cool. That wore off fast.

Thanks again. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[Darrylles]

Hi,

I have the same 'old-style purist' attitude as smandoli9 - not because of snobbery or elitism, but because such features don't make for easily maintainable database applications - period.

'Easily maintainable' is the crux of the question that this thread is based on.

When I finish and leave an application, someone that has had 'standard' RDBMS training and 'standard' app. development training will pretty well instantly pick up where I left off. I'm not 'needed' any more.

Relational database theory has only evolved because it has been successful, the 'lookup' won't ever enhance it.

I agree totally with all of Littlesmudge's statements, except for his agreement about a remark about 'a pow-wow with management'.
I personally would have indicated my reason for agreeing with it. The statement '..this might be the catalyst for exposing some other things..' means what?

What other things?

Are you saying that the thread owner can't communicate, or that the management are too thick to understand simplified facts of RDBMS?

Further...
Macros should be cut out of MS Access - they encourage unstructured and unmaintainable code. Macros kill me.
I might start a thread on that one - any disagreement?

You've kicked a right old wasps nest with this one smandoli9, but you're right as far as I'm concerned - no problem.

Regards,

Darrylle









"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[LittleSmudge]

Nice one Darryl,

My comment '..this might be the catalyst for exposing some other things..' was really as open ended as it seems.
I think the thread owner is rather adept at communicating, however, how good is 'the management ' at listening ?
Have they been 'strung along' by the lookup loving peer that smandoli refers to ?
Do they have any clear idea about what's going on ?
Are they even 'technical' management ?
etc..



As for MACROS ! Well there's another 'hornet's nest'.
I started a thread a few months ago on that one
( just been looking for the link but I can't seem to find it ) asking if anyone ever used Macros any more and why does MS still keep it available when they seem so keep to dump anything else that is ever so slightly 'out of fashion'

I'm with you one that one too.


G LS

 

[Steve101]

Microsoft is a 'marketing' company, and Access is marketed to all types of user, from the total novice, to the professional developer.

Novices have always liked macros; they've been around forever (since Lotus123), and for some strange reason, are perceived as being easier to learn and use than VBA programming. They're a curse when you know better, but I suspect their removal would upset quite a lot of people (and existing Access 'legacy' systems). Old habits die hard (sigh!)

 

[Darrylles]

Steve,

Microsoft is a software company - officially.

You are right to label it as a 'marketing' company however, because it has led the world in changing the 'perception' of buyers as opposed to changing it's 'product' to suit what the buyer wants.
Better to change the user to suit the product, rather than the product to suit the user.

World politics has followed Microsoft. USA copied it and the UK has copied it.

PERCEPTION is now the 'keyword'.

What a downer for us all - we do as we are 'told'.

Regards,

Darylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Steve101]

Darylle,

Yes, concur with what you say. What an imperfect world we all live in, though we strive to do the best we can within it.

Incidentally, as well as subscribing to DeBono, I do enjoy a good read of Dilbert (notwithstanding all of the nonsence that its a Communist inspired plot).

All the best,
Steve