Using a single table for multiple comboboxes

[xBirdman]

Hi gang, opinions requested.

Maybe I just haven't figured out how to search correctly for this idea...

The short: Because of serious constraints on my ability to make structural changes to a database I'm responsible for I'm trying to build ways to modify my data as needed. In my current restructure I want to add some lookup tables to constrain values available for users to enter into fields because the error rate historically has been ridiculous. So, a list of states, cities, providers, service elements, etc... to use as basis for comboboxes on the forms. Because adding tables is :bangheadI have to go before a board to request permission for any structural change) I'd like to add one table for all of these general lookups. I envision three fields - the value to be stored (e.g., the state code "NY"), a description if needed, and a third field to use as a filter so that the "city" combobox only gets cities, the state box only gets states, etc.

Has anyone any general experience, thoughts, or warnings with setting something like this up? I can get permission to add the table which is a pain, but once done, I can edit information in the table however I see fit. I just want to make sure before doing a bunch of work that I'm not digging myself a big dangerous hole.

Thanks for your thoughts.

 

[pbaldy]

It's up for debate, I stick with single tables. The concept is often called One True Lookup Table, here's one discussion:

https://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html

 

[jdraw]

I opt for 1 table per Subject, not 1 table encompassing several disjoint topics.
You'll find lots of info on Lookup tables by Joe Celko --here's one and another.

 

[theDBguy]

Hi Birdman. If you don't have any choice, you have to do what you can. A single table is fine, you will probably just have to store everything as Text and then create some way to convert the values to their proper data types when you use them.

 

[pbaldy]

I've used one table for years!

I did say it was up for debate! I don't have a strong feeling either way, I just learned to use multiple tables and haven't found a compelling reason to change.

 

[isladogs]

Although familiar with the concept, I'd never heard the OTLT phrase before and found the articles an interesting read. Thanks.

With one exception, I normally use separate lookup tables for each subject as I find that easier to work with.

The exception is a settings table for each app with fields ID, ItemName, ItemValue, ItemDescription where I store a wide range of data such as version, version date, folder location for images etc. Typically this will have 10-20 records but in some of my large schools apps it may contain 100-150 records.
This is useful for keeping disparate info in one place but suffers the problems of the OTLT as described by Joe Celko. To handle that I use a series of functions GetVersion, GetVersionDate, GetImageLocation etc.

To me the idea of doing this for all lookups would be a lot of unnecessary work akin to the extra coding used with entering data using unbound forms. Which of course is exactly the method I sometimes use for my settings form! At other times I use a DS form bound to that table.

 

[xBirdman]

...you will probably just have to store everything as Text and then create some way to convert the values...

Exactly - and I've done this already in another context and it wasn't a headache to pass a short conversion after the filter and selection is made, before applying the value wherever I'm taking it. Definitely one of the drawbacks of the approach.

 

[theDBguy]

Exactly - and I've done this already in another context and it wasn't a headache to pass a short conversion after the filter and selection is made, before applying the value wherever I'm taking it. Definitely one of the drawbacks of the approach.

Hi. It definitely isn't insurmountable; but as I said earlier, if you're left with no choice, you'll have to make do. Good luck!

 

[xBirdman]

I've used one table for years! I have a a form which loads, and can edit any of the look up Groups. The names of the lookup groups are also stored in the single lookup table...

Thanks for the feedback. I don't quite follow everything here, but appreciate the overview as it allows me to think about best ways to achieve my desired outcomes. Cheers!

 

[xBirdman]

A last thought to those of you who provided really helpful materials and feedback here. I've decided to keep the tables separate and just deal with the hassle of building out multiple lookup tables. My main concern isn't that I can't make it work for me, but I'm not 100% certain if I'll still be in this job in a year, and I don't want to leave someone else something complicated to figure out. Thanks again for an excellent resource providing helpful answers, suggestions, and discourse, as opposed to (as I've seen elsewhere), demeaning and insulting responses detailing not only how stupid you are for even suggesting something, but how impossibly poorly you've asked the question itself. This forum is a breath of fresh air.