Database schema organization for static lookup data

Question

I am building an web application, and the "static" data like Gender, Profession , etc will be presented in combo box.I will store the data in database table. All this "static" data will be stored in table with the following structure

static_gender 

    id ,
    token,
    description

static_profession
    id ,
    token,
    description

and there is a table with all the label and message resource translated to several languages like this one

table_message_resources
id,
token,
language,
message

So to get all the labels to present in the gender combobox i will make a query of this style

select token,message 
 from table_static_gender g inner join table_message_resources  mr on
 (g.token = mr.token)
 where language = 'En'

My question is : Is this a good schema to store the static data(one table per subject and all the tables have an equal structure) or should i have just one table to store all the static data.

Beyond the theoretical discussion, what I usually do with this depends on the application. Ex: do you need to restrict the professions? Or is a free text field enough? If you will never search on the profession, let the user type whatever, make it a free text field and store it in a VARCHAR in the same table as the user. Otherwise, you want a table with values, and setup a selector in your application. — Nic3500, Nov 06 '17 at 13:51

score 0 · Answer 1 · answered Nov 06 '17 at 12:04

There is a well known antipattern known as One True Lookup Table, abbreviated OTLT. You can search on database OTLT for a lot of arguments against this pattern. There are a few circumstances where it turns out to be helpful to do this, but avoiding complexity is not one of them.

Your second option looks like OTLT to me, so I would avoid it.

Database schema organization for static lookup data

1 Answers1