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.

 

[Uncle Gizmo]

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

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.

I've recently improved on this idea. I have created a form which will identify text fields in a table "Table With Candidates" "TWC" fields that are candidates to be added to the lookup table. The form contains routines that Extract the data from the text field into a temporary table, along with an ID number. The ID number is only there to give them an ordered list of 1 to 20 or whatever. Another routine appends the data into the lookup table "tblLookup", creates a new "Long" field in the "TWC" table, matches it against the existing text field and then copy's the new lookup values for each text item from the lookup table and then deletes the original text field in the "TWC". Finally a routine renames the new long lookup field to the do the original name of the string field...

I've just processed a customers database with this Tool and added 20 or so lookups in to the single lookup table! Now on the customers form(s), the combo-box's that access a look up information. I can place a command button next to each combo, the user can press the command button, a pop-up form shows the lookup table list and the user can hide items, add items (if they are allowed)... All this is an automated process, all I do is check a checkbox next to the fields I want placed in the single lookup table...

 

[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.

 

[Uncle Gizmo]

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.

I'm not a stickler for it Paul, sometimes it feels a lot of work for little benefit.

Sent from Newbury UK

 

[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.

 

[Pat Hartman]

While I do use single tables for some things, for ANY lookup table that has no ancillary data or relationships, I use my lookup table manager (which I am including). I want to tell you that this is a very OLD idea. I developed the first version of this application almost 40 (yep 4-0) years ago using COBOL and IMS ( a hierarchical database - think of an org chart. Each box is a child "table"). I recreated it later with COBOL and DB2 (a RDBMS similar to SQL Server and Oracle) and the final implementation is Access. I import all the objects into every database I make so I don't have to reinvent the wheel. The best part is I can allow the users to manage the items in the vast majority of tables. The only ones they can't manage are tagged (you'll see if you look at the app). So things that ultimately need code behind them have to be managed by IT even though we let the user assign the values to a particular record.

I also included an old PPT file which showed how an earlier version worked. There might be some slight differences but the concept hasn't changed in 40 years