How do you guys feel about one central lookup table, as opposed to several individual tables? I'm trying to decide whether I should use one, or many.

Thanks for any input.

I'd say you need a seperate table for each entity.........

I agree with Alan. I've seen discussions on the topic, and the general consensus (though not unanimous), was against the one-table solution.

I use both techniques in my apps ...

Click Here for a thread regarding the topic ... the post I directly linked you to has a sample utility that really helps maintaining the lists ... the entire thread is a good read and, IMO, expresses some stuff you should know ...

Click Here for another thread on the topic ..

And this thread I think is the first time I participated on the topic here at UA ...

... The first link has the most recent version of the utility ... but all the links may help add to your decision making process ...


EDITS ADDED ...

I'm always the odd ball!! ... ...

Edited by: datAdrenaline on Mon Jul 14 22:54:00 EDT 2008.

Like illegal drugs, you should just say no to one true lookup tables.
No matter who is enticing you.

ROFLMAO!! .....

... I know I am in the minority ... but I DO admit the short comings of the technique ... I beleive in the first link I provided I give caution putting my "umpf" in the tables than there should be.

I manage almost 40 lists this way ... in the long run it has made my life easier, BUT in the beginning when I tried to make the lists more than just an ID, TextValue, Description ... I got into workarounds and a ton of difficulty... that is when I decided that if my need goes beyond that which I have listed, the list becomes its own table (again managed by code)

From a data modelling perspective, it is advisable to use individual tables for each lookup entity.

This is particularly important when joining on multiple lookups, because of the need for the query engine to create virtual tables, not to mention the performance hit when having to filter such tables twice; once to get the category, and once to get at the specific value.

Multi-purpose lookups are never used in data warehouses or other VLDBs.

The OP seems to be asking: -

“How do you guys feel about one central lookup table, as opposed to several individual tables? I'm trying to decide whether I should use one, or many.”

I have read this thread many times and fail to see the logic in any of the replies so far.

It may also mean a central lookup table placed on a BE serving the FE’s, or it may also mean the lookup table being placed on the FE’s locally.

The latter (local FE) implies a local configuration table for that machine whereas the former (BE table) implies a ‘follow me configuration’ to a logged in user on the network.

May I ask the question, what does the OP mean?

Regards,
Chris.

The term 'one true lookup table' or 'OTLT has a meaning in the
relational database world. That's why there is a name for it.

Google it.

Hello Graham! ... Great to see you here! ...

With respect to performance, I have not seen a performance hit. For comboboxes/listboxes I use the "Category Specific" queries (sel_lkupSystemList_* using my sample as a point of referal to common context) as my primary source in the RowSource SQL ... so there is not a double hit there with every record, however, one hit to populate the rows of the combo with the category upon form load, then the once to find the value ....

When extracting data from database, I do not use the category specify queries. Since in my table of lists there is a UniqueID for each value in it, I just JOIN up to the main list table (tblListValues), that way the execution of the final query just has the value to find, so only one hit .... although the one hit is against a larger table.

... But ... in logical agreement, I can definately see where the OTLT method can be more db engine intensive ... but in most cases I can't see that being a noticible factor if things are set up in an optimal fashion ... I have not tested it though.

-----

I would think in VLDB's hardly use ANY lookups are used ... but I am not versed in VLDB's ... they are just figments of my imagination!

Lewis ...

From an "advice" point of view ... I have advised both ways (as you have probably gathered from my previous post), however (as you can see from the posts thus far!) the individual table method is more commonly implemented. There are many reasons for that veiw point. One such reason is that its easier to "jump in" and follow what has been done. So if/when your successor takes over, or if you have someone creating reports from your source or ???, it will be easier for that person to follow your structure without specific guidance. I have also advised a multi-purpose lookup (aka: "embedded tables"), but I personally consider it an advanced method simply because the developer needs to fully understand the ramifications for using the technique. As I stated, I have had GREAT success and enjoy using the embedded table technique ... its ease of updating data, ease of adding a list, less clutter in the table view of the db window (but more in the query view!).... I'll likely not stop using it! But, it is definately not for everyone! Ultimately, I think the single table per entity approach has a more universal appeal, but I do beleive the embedded table method has its place and when done right can be a big benifit for the developer.

G’day Ace.

The reason I didn’t notice the title contained ‘One True Lookup Table’ is that I’ve never heard of it before.

Doesn’t look like I’ll need to spend too much time on it.

Thanks for the info and regards,
Chris.

I replied with a view as it related to data modelling. I have used single table lookups before, with no problem against a Jet backend, but they are an abomination in highly normalised (DKNF) data warehouses where we're talking about hundreds of millions of records. Since I typically build Access frontends against this sort of backend, the consideration is very relevant here. VLDBs (very large databases) DO use lookups, just like any other database, but they do not use OTLTs because of the massive performance hit and the problems associated with table keys.

Whoa! .... hundreds of millions!!! .... OUCH!!! .... my largest table has 3million records and I thought that was getting pretty big! ... apparently not! ... I think I would "feel" it then too! As of this moment I have somewhere close to total of 1500 rows in my "embedded table" ... and that is for about 40 lists ... so ... thats like a blip compared to your scenario!! ..

Also .. I think its pretty cool that you use Access as a front end to these! ... I guess is all in the connection eh?

Thanks for that bit of information Graham.

I have a database for special needs schools, it has a table with categories (columns):
Sensory Loss
Epilepsy
Learning Difficulties
Communication
Feeding
Autism
ADD
SLD
Physical handicap
etc

But these are basically constant and cannot be changed by the user. So I am not sure it can be classified as an OTLT. The problems using OTLT I believe far outway the benefits especially if, in the future, some other developer takes over the development / maintenance of your dB.

my 2c worth!

Bernie ...

You are correct .. that is not an OTLT . If you D/L the attachment in the link I provided, I think that will show you what one is ... at least my rendition of it!! ...