Data modeling for Multi-language support

[satishjayan]

I m trying to design a set of tables to handle the multi-language. Our's is a hiring application and based on the candidates language of preference, the application should appear in that language. I proposed the below design, However, other architects are not inclined to the below solution as there is a need for adding additional attributes for each new language in the Question table. Can you please suggest if there is a better modeling approach to avoid adding columns for a new language.

Language table - To store a list of available languages.

ID Name Encoding
en English UTF-8
sp Spanish UTF-8

ID – Identifier for each language.
Encoding – Character encoding for each language.

Questions table – To store translated strings for each question in different languages.

Question_id Page_id en sp
daysQuestion availabilityMsg Are you available to work days? Es available operari diebus?
nightsQuestion availabilityMsg Are you available to work nights? Es available operari noctibus?
WeekendsQuestion availabilityMsg Are you available to work weekends? Es available operari weekends?
HolidaysQuestion availabilityMsg Are you available to work holidays? Es available operari feriarum?
overnightsQuestion availabilityMsg Are you available to work overnights? Es available operari overnights?
startQuestion availabilityMsg When are you available to start working? Quando es available committitur operandi?
pharmTrnQuestion educationMsg Have you done any pharmacy training? Have vos aliquid egissent pharmacy disciplina?
licenseQuestion educationMsg Are you a licensed pharmacy technician? Esne licentiati pharmacy TECHNICUS?

Question ID – Unique String identifier for each Question
Page ID – Non-Unique identifier for each page. This ID will also enables to group the set of related questions in a given page.

[hang]

I guess other architects are correct. I would never model a table using key/ID value as column name.

In your case, a bridge table for question language might be the best option, where a question ID would repeat for each language ID. You should also avoid lengthy string as key. Create surrogate key if proper natural key does not exist. Page belongs to question dimension as a repeating group if a question only appears on one page, otherwise it's m-m relationship and you need another bridge for it.

Now this is only for your dimensions and bridges to relate them. I imagine you would need fact table for measurements where you store the question and date dimension keys.