I'm about to start working on an app which will include words and their translations in various languages.

What is my best option for a database?

Queries will be made instantly. So if someone selects "Russian" as a second language and starts typing "Please pass the salt", each of those words will have to be looked up in Russian. And maybe just listing each word won't be the exact translation of the phrase which will lead to another query which will maybe look up the phrase based on the words in the sentence.

So I can use MySql, or any other non sql database Firebase, Mongo, CouchDb ... etc. But I don't know if not using a relational database is my best option.

Can I do something like this which seems ok but the database will get huge when considering all languages:

  "english": {
    "word1": {
      "russian": "word1inrussian",
      "spanish": "word1inspanish",
      "korean": "word1inkorean"
    "a phrase": {
      "russian": "phrase in russian",
      "spanish": "phrase in spanish",
      "korean": "phrase in korean"
  • I am confused by "translation" instead of "dictionary". Translation needs very different algorithms, than the simple lookup you're suggesting. What are you aiming at ultimatively? – Grimaldi Mar 5 '17 at 20:48
  • I believe that it will be something like a translation app. I edited the example in my question. I wasn't aware that there will be phrases which need to be matched. So words will be mixed with phrases which for me is confusing. – Ciprian Mar 6 '17 at 20:44
  • Check this here, how state of the art translation works: arxiv.org/abs/1609.08144. – Grimaldi Mar 9 '17 at 6:30

You need a relational database to look up one word to get a word or phrase. That is not "translation".

The main table in that RDBMS would be something like

CREATE TABLE Transliterate (
    in_phrase VARCHAR (255) NOT NULL,
    language CHAR(5) CHARACTER SET ascii NOT NULL, -- 'en-us', 'ru-ru', ...
    out_phrase VARCHAR (255) NOT NULL,
    PRIMARY KEY(language, in_phrase)

This assumes that in_phrase is always in English. If not, then there needs to be column for the incoming language, and it would be part of the PRIMARY KEY.

For real "translation", you need lots of serious algorithms, backed up by this trivial lookup tool.

| improve this answer | |
  • Are you saying that NoSQL databases don't allow lookups? – mustaccio Mar 9 '17 at 15:46
  • <snarky>NoSQL, as I understand it, is for people who have not taken the time to learn what SQL does for them. Then they proceed to re-invent the wheel.</snarky> – Rick James Mar 9 '17 at 21:52
  • In case the in_phrase will vary, will the language column become language_out and another column language_in be added? So a row might look like en-us , dog, es-es, perro ? – Ciprian Mar 11 '17 at 4:50
  • And why isn't the out_phrase added PRIMARY KEY as well? – Ciprian Mar 11 '17 at 4:59
  • 2 language columns -- sure. The PRIMARY KEY must be unique. If you add out_phrase to the PK, then you are saying there can be multiple out-phrases for one lang_in & in_phrase combo. I don't think that is what you want. – Rick James Mar 11 '17 at 21:34

You don't typically handle translation inside of the database. This is for no specific reason. It certainly could be done, and has been done but that's not the way things are done now.

There are two huge sources of internationalization data,

Unicode CLDR is absolutely gigantic. Normally you use something like Globalize.js which wraps these and pulls down data for the features that it supports.

In order to generate most simple strings, a globalization library needs this much crap... (English only shown for simplicity)

> var msg =
  '{GENDER, select, male{He} female{She} other{They} }' +
  ' found ' +
  '{RES, plural, =0{no results} one{1 result} other{# results} }' +
  ' in the ' +
  '{CAT, selectordinal, one{#st} two{#nd} few{#rd} other{#th} }' +
  ' category.';

You'll get these results:

> var mfunc = new MessageFormat('en').compile(msg);

> mfunc({ GENDER: 'male', RES: 1, CAT: 2 })
'He found 1 result in the 2nd category.'

> mfunc({ GENDER: 'female', RES: 1, CAT: 2 })
'She found 1 result in the 2nd category.'

> mfunc({ GENDER: 'male', RES: 2, CAT: 1 })
'He found 2 results in the 1st category.'

> mfunc({ RES: 2, CAT: 2 })
'They found 2 results in the 2nd category.'
| improve this answer | |
  • By the way, Postgres 10 gains support for ICU collations. See this blog post by Peter Eisentraut. – Basil Bourque Aug 13 '17 at 5:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.