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.

Now we add a new language nl_NL as column, automatically creating every required key with value 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?

CodePudding user response：

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.