The goal is to create a multilingual dictionary MySQL table of words that use a key => value structure for any desired language. Current solutions (and threads) on stackoverflow refer to adding new key => values pairs as rows to an existing table.
The problem is synchronizing keys for every available language. My application uses placeholders %{example} that refer to a specific key in a dictionary. When users switch to a different language that placeholder needs to be present in the dictionary.
Proposed solution:
Rather than adding a key => value pair per language as row, add a column per language and use every row as a unique key.
| key (pk) | en_EN | se_SE |
|---|---|---|
| apple | apple | apple |
| fish | fish | fisk |
Now we add a new language nl_NL as column, automatically creating every required key with value NULL.
| key (pk) | en_EN | se_SE | nl_NL |
|---|---|---|---|
| apple | apple | apple | NULL |
| fish | fish | fisk | NULL |
Retrieving the entire dictionary for a specific language would be as easy as:
SELECT en_EN FROM <table>
Since this approach feels a bit unusual namely dynamically adding/removing columns as opposed to rows, I was wondering if I’m missing something or if there is a better approach. What are your thoughts?
>Solution :
There is a much better approach using joins.
Create one table "terms" with the all the keys/terms and then another table "translations" with the term, locale and translation.
To get all english translation use
select term, translation
from terms
join translations using (term)
where locale = "en_US"
You can use a left join to find the null elements if you need to.