Lookup table(s) (1 Viewer)

Opinions/advice please.

I have a new personal project to start. It is to keep track of the progress of organising a college reunion.

Now I have tended in the past to have a generic lookup table for my combos where there are not a lot of choices?, with a category field to identify what the lookup is for.

Presently I know I will need a lookup for
Shipping Company
Preferred Contact Method
Search media used eg Facebook, LinkedIn, BT etc

Have the experts ever designed like this, or do you prefer to have many small tables in the DB.?
I know I may have problems having to bring a lookup into a relationship more than once as an alias, but have managed that in the past.

Is there anything else I should be aware of?

As always, I'm keen to learn the best practice of doing something, and as this is at the design stage, I now have the ability to follow best practice.

Whilst this will only involve around 100 main records, I am always thinking that I might need to apply a method/process in another situation, that is if I ever get another job.

This view is pretty much the opposite of user Dangerous's view.

TIA

Now I have tended in the past to have a generic lookup table for my combos where there are not a lot of choices?, with a category field to identify what the lookup is for

Click to expand...

I do this if it makes sense. With demographic information like you are likely doing, that is where I often do it. As long as you are saving single values, not a lot of related information it makes sense. It is a table of 'value lists"

Category CatValue
Title Mr.
Title Mrs.
Title Dr.
....
Relation Wife
Relation Husband
Relation Child
....
Color Red
Color Blue
...
Size S
SIze L
Size M
....

I also usually have one more column "Sort". Which allows me to sort in a non alpha order. S, M, L

Hi MajP,

Yes, I have another field to sort in the order I require as well.
Going to go with this for now, as I want to get started on it, but can change later, if I think it will benefit me in this project.

For me, it depends on whether the selections in the combo are likely to change over time so it is easy for the user to add a new selection.


For example, you cited Search media used and gave some examples. It's likely that other categories will be added. However for some data models, the choice will always be limited ie Adult/child where I would not bother with a lookup table. Same used to be with gender, wher the choice was M/F.

Hi Cronk,
Yes, they are likely to increase and one or two have actually done so already just today, but there will not be too many more.

I realise I can hard code items like Male/Female/Transgender when there is only a small number of items, but my main question was whether to use one table or many small discrete tables.
Eg for shipping companies, I have 19, but they are never going to increase as it is for a set number of persons, and the number of persons is not going to increase either. If I was creating some system for all measure of shipping companies, then I would defnitely have a tblShippingCompany.

For this particular project it is not crtitical I admit, but as I mentioned I like to think of the future and not get stuck with one particular method.

I realise I can hard code items like Male/Female/Transgender

Click to expand...

LOL, that is the most ironic example. A couple years ago I would have recommend a simple hard coded list for the two choices, but now that there is like 71 possibliities (I think that is what Facebook provides) you need a full on table.