Query's & Design of Look Up Tables

[Toga]

As I have progressed with a project I am working on, I've discovered that I have accumulated quite a few Look Up tables. My question is, and it really from the perspective of doing Look Ups & query's against, does it make sense to have an array of many Look Up tables or could my application be just as efficient having one, but with many columns of different data groups. In most instances, I don't have a Look Up table that's over 25 lines of data in it anyways..... It just seems like it would be a little easier having everything in one place. I don't see any issues on the surface but was wondering if anyone had tried this before and what were found to be the downsides if any...

Thanks..Toga

 

[tlbroadbent]

A single table with many columns would probably be highly denormalized with unrelated data in the same table. Normalization is highly desirable because query design, efficiency, ease of maintenance, etc. are usually impacted negatively by denormalization. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Amiel]

Moving the look up tables from individual tables to a single table sounds like a bad idea on the surface;

however if you were, for example, to move that single table to the client machine (it's just a thought), you will find that you have improved performance significantly, while reducing complexity and the maintenance hastles too!

The real answer to this is probably is more like, "what will work well for your users the application, and you".

First of all, don't burden yourself with over designing look up tables. Look up tables typically contain 'misc.' entries that for reasons of convience and functionality alone, are stored in a table.

Performance is enhanced when tables are not 'too wide', fewer columns means better performance, and of course the number of rows in the table (you've already looked at that) should be considered. There are no 'pat' rules to follow where this stuff is concerned really.

'Look up tables' and the associated list box are a major performance 'hit' for an Access application. Not to mention the development and maintenance side of things. So sure, why not combine rows from records that very likely do not fit, and were not necessarily designed to fit, into the relational scheme of the database into one or two tables.

Doing this, for the purpose of reducing complexity and/or increased performance is perferctly valid in the world of software design.


No problem. Amiel
amielzz@netscape.net