Translation database schema for software (exemplified)

Question

I have a device with a GUI (menus etc) that I need translated. The translations are managed using a database.

I have looked at different answers to same issue:

As most of the examples explain mostly schemas, I have tried to make a small example, using SQLite:

Database shemas:

  -- language table to hold individual languages
  --  
  CREATE TABLE "languages" (
    "languageID"    TEXT NOT NULL,
    "langNativeName"    TEXT,
    "langISOCode"   TEXT
    PRIMARY KEY("languageID")
  )

  -- words table to hold the strings to be translated
  -- each string has an identifier used in software, such as "mainMenu", "label123" etc
  CREATE TABLE "words" (
    "wordID"    INTEGER NOT NULL,
    "wordKey"   TEXT,
    "wordDefault"   TEXT,
    PRIMARY KEY("wordID")
  )


  -- translations
  -- combining languages and words  
   CREATE TABLE "translations" (
    "keyID"         INTEGER NOT NULL,
    "langID"    INTEGER NOT NULL,
    "translation"   TEXT,
    PRIMARY KEY("keyID","langID")
   )

With some sample data:


Languages:
+------------+----------------+-------------+-------------+
| languageID | langNativeName | langEnglish | langISOCode |
+------------+----------------+-------------+-------------+
|          1 | English        | English     | en          |
|          2 | Francois       | French      | fr          |
|          3 | Deutsch        | German      | de          |
+------------+----------------+-------------+-------------+   

Words: (strings to translate). The wordKey will always be unique.
+--------+----------------+-------------+
| wordID |    wordKey     | wordDefault |
+--------+----------------+-------------+
|      1 | tileProduction | Start       |
|      2 | tileJobs       | Job         |
|      3 | tileGoto       | Go To       |
+--------+----------------+-------------+

Translations:
    word (1) is not translated for English
    word (3) is not translated at all
+-------+--------+--------------------+
| keyID | langID |    translation     |
+-------+--------+--------------------+
|     1 |      2 | Produccion         |
|     1 |      3 | Produktion         |
|     2 |      2 | Seleccion de tarea |
|     2 |      3 | Jobauswahl         |
+-------+--------+--------------------+

My proposed SQL to get a translation

As I have understood from examples, the following SQL should be used to get a translation. An extra field has been added to get a star on untranslated items.

    select 
       wordKey,
       coalesce( translation, wordDefault) displaytext,
       case coalesce( translation, wordDefault)
          when translation then ""
          else "*"
        end remark 
    from 
        words
     
    left join translations 
       on words.wordid = translations.keyID
       AND
       translations.langid  = 3
       
    left  join languages on 
       languages.languageid = translations.langID
       AND
       translations.langID = 3

Which will give me:

+----------+-------------+--------+
| wordKey  | displaytext | remark |
+----------+-------------+--------+
| tileProd | Produktion  |        |
| tileJob  | Jobauswahl  |        |
| tileGoto | Go To       | *      |
+----------+-------------+--------+

This works.. but what I am in doubt about before I start inserting larger amounts of real data:

is this model correct, and if not, what may I have missed ? There will be some metadata for each language, which I have left out for clarity.
is the query to get a translation correct, or can it be done better ?

You should test it in your environment to see if it is correct. And asking for whether something can be done 'better' is off-topic on Stack Overflow because it's an opinion-based matter. — TylerH, Jan 27 '23 at 19:39

Translation database schema for software (exemplified)

Database shemas:

My proposed SQL to get a translation

0 Answers0