Database - Multilanguage online dictionary

Question

I am implementing a database for a multilanguage online dictionary. At the moment I have only 2 languages (RO - Romanian, CS - Czech), but I think maybe in future I will want to add more languages (for example EN - english).

The website will be multilanguage too, so a romanian user can look for czech words and a czech user can look for romanian words.

I'm thinking how could I implement the TRANSLATIONS table for multiple translations. At the moment I have this implementation, but it's not the way to go, because in case I will add another 10 languages, I would have to add another 10 columns:

WORD (id,word,language_id,..)
TRANSLATIONS(id,ro_id,cs_id)
LANGUAGE(id,code)

LANGUAGE
id   code
1     ro
2     cs

WORD

id word language_id
1  xxx      1
2  yyy      2

TRANSLATIONS
id   ro_id   cs_id
1      1       2

I could make a table like:

TRANSLATIONS(id,word_id,translation_id)

but in this case I don't know how to search for translation and i would have to add both cases:

TRANSLATIONS
id   word_id   translation_id
1      1            2
2      2            1

Some ideas? I hope my description makes sense.

Thanks.

UPDATE: Another approach could be a table with JSON column like:

TRANSLATIONS(id,data)

id                            data
1          {"ro":1,"cs":2,"another_lang": "another_id"}

but is this a good idea?

francovici francovici · Accepted Answer · 2019-02-17T18:18:18

I think your database design may be a little off.

Maybe you could have something like :

WORD (id,word,language_id,..)
TRANSLATIONS(id,language_id_from,language_id_to,word_id_from,word_id_to)
LANGUAGE(id,code)

Then you would have records like:

    LANGUAGES
id  code
1   EN (english)
2   RO (romanian)

      WORDS
id    word   language_id
221   hi     1
4423  Bună   2


TRANSLATIONS

id      lang_id_from   lang_id_to   word_id_from   word_id_to
54512         1             2             221          4423

Then, for a given word in a given language, such as "hi" in english, you would look for the translations record that matches the id of the word (1) and target the language id you're looking for, such as Romanian (2).

Something like:

SELECT * 
FROM Translations 
WHERE word_id_from = 1 AND language_id_to = 2

And it would be scalable, because you can add new languages, words and translations as you want, and your search wouldn't be affected.

Database - Multilanguage online dictionary

1 Answers