How to make database for Multilanguage list app?

Question

E.g. the app is in 3 langs (en, fr, de). I have two lists. If I change app language I want to show the values with correct translations.

I have an equipment list:

EQ_LIST

eq_id | group_id | title | lang
1, 1, tv | en
2, 1, la télé, fr
3, 1, Fernseher, de

QTY_LIST I have another list, where I want to "log" any changes in quantity

qty_id | groupd_id, qty
1, 1, 3
2, 1, 6
3, 1, 8

group_id refers to the "tv" equipment, without to know the lang. Embed these two tables and filter by lang, is a good way to make?

Answer 1

I would suggest

• 1 the addition of a table for the language(s) and
• 2 also extending the eq table. To incorporate a map to the language and also a map to what would be the "base" entry.

So perhaps have a language table such as :-

DDL would be :-

CREATE TABLE language (languageid INTEGER PRIMARY KEY, languagename TEXT UNIQUE, languageshortname TEXT UNIQUE);

An eq_list table such as :-

• i.e. titleenglishmap points to a specific "base" (this would allow conversion say from le tele to television as it links all the translations).

DDL would be :-

CREATE TABLE eq_list (eqid INTEGER PRIMARY KEY, title TEXT, groupid INTEGER, titleenglishmap INTEGER, languageidmap INTEGER REFERENCES language(languageid));

The qty_list table could be as it is :-

using the above you could have queries such as :-

SELECT qty_listid,title,qty_list,languagename  FROM qty_list 
    JOIN eq_list  ON qty_list.qty_listgroupid = eq_list.groupid
    JOIN language ON eq_list.languageidmap = language.languageid
WHERE languagename LIKE 'french'
;

SELECT qty_listid,title,qty_list,languagename FROM qty_list 
    JOIN eq_list  ON qty_list.qty_listgroupid = eq_list.groupid
    JOIN language ON eq_list.languageidmap = language.languageid
WHERE languagename LIKE 'German'

Which would respectively produce results:-

For French :-

and

For German :-

SQLite Demo (includes adding Spanish and Italian)

First the Table Creation DDL's :-

CREATE TABLE IF NOT EXISTS language (languageid INTEGER PRIMARY KEY, languagename TEXT UNIQUE, languageshortname TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS eq_list (eqid INTEGER PRIMARY KEY, title TEXT, groupid INTEGER, titleenglishmap INTEGER, languageidmap INTEGER REFERENCES language(languageid));
CREATE TRIGGER IF NOT EXISTS AFTER INSERT ON eq_list WHEN new.languageidmap = -9999 BEGIN UPDATE eq_list SET titleenglishmap = new.eqid WHERE new.titleenglishmap = -9999; END;
CREATE TABLE IF NOT EXISTS qty_list (qty_listid INTEGER PRIMARY KEY, qty_listgroupid INTEGER, qty_list INTEGER);

• Note the TRIGGER isn't essential an @Query running an UPDATE can do what it needs.
• When you insert a new "Base" eq_list (e.g. English Television) you may not know the eqid that the titleenglishmap should point to so the TRIGGER (or UPDATE) reconciles this based upon a value of -9999 i.e. it will set the titleennglishmap value to the eqid value.
• Room doesn't support TRIGGERS via annotation, this could be added be via the onOpen Callback.

Inserting The Languages

To insert the 3 Languages English, French and German :-

INSERT INTO language (languagename,languageshortname) VALUES ('English','en'),('French','fr'),('German','de');

• Note that the language id's will very likely be 1,2 and 3. However, the following DOES NOT rely upon this assumption.

Adding a Base (English) eq_list

/* Add the main (English equipment note the -9999 so UPDATE (could be a TRIGGER))*/
INSERT INTO eq_list (title,groupid,languageidmap,titleenglishmap) VALUES('television',1 /* the groupid */,1,-9999);
/* make english / base title point to itself (not needed for TRIGGER)*/
UPDATE eq_list SET titleenglishmap = eqid WHERE titleenglishmap = -9999;

• as can be seen the UPDATE reconciles the titleenglishmap value (although the trigger has already done this)
• only one update would be needed for multiple base eq_list rows (e.g. for Telephone .... )

Adding the translations (French and German)

INSERT INTO eq_list (title,groupid,languageidmap,titleenglishmap) VALUES
    ('le tele',(SELECT groupid FROM eq_list WHERE title LIKE 'television'),(SELECT languageid FROM language WHERE languagename LIKE 'French'),(SELECT eqid FROM eq_list WHERE title LIKE 'television' )),
    ('fernsehen',(SELECT groupid FROM eq_list WHERE title LIKE 'television'),(SELECT languageid FROM language WHERE languagename LIKE 'German'),(SELECT eqid FROM eq_list WHERE title LIKE 'television' )),
    ('tv',(SELECT groupid FROM eq_list WHERE title LIKE 'television'),(SELECT languageid FROM language WHERE languagename LIKE 'English'),(SELECT eqid FROM eq_list WHERE title LIKE 'television' ))
;

• note that tv is effectively an alias of television (however the potential use of this has not been covered, so for all intents and purposes tv can be ignored).
• the groupid is taken from the base (television) according to the title
• the languageidmap is determined from the name of the language
• the titleenglishmap is determined according to the eqid of the base according to the title
• note that by using LIKE names (title and language) are not case dependant, so french would get French* etc.

Add the qty_list rows

INSERT OR IGNORE INTO qty_list VALUES (1,1,3),(2,1,6),(3,1,8);

• The results shown above were driven from the above code.

The model is adaptable. Say you wanted to add Spanish and Italian.

Then :-

Add the New Languages :-

INSERT INTO language (languagename,languageshortname) VALUES ('Spanish','es'),('Italian','it');

Add the translations

/* And the new equipment transalations */
INSERT INTO eq_list (title,groupid,languageidmap,titleenglishmap) VALUES
/* Spanish */
    ('televisión'/* ARG1 1 name in the language*/,(SELECT groupid FROM eq_list WHERE title LIKE 'television'),(SELECT languageid FROM language WHERE languagename LIKE 'Spanish' /* ARG 2 language */),(SELECT eqid FROM eq_list WHERE title LIKE 'television' /* ARG3 The english translation */ )),
/* Italian */
    ('television',(SELECT groupid FROM eq_list WHERE title LIKE 'television'),(SELECT languageid FROM language WHERE languagename LIKE 'Italian' /* ARG 2 language */),(SELECT eqid FROM eq_list WHERE title LIKE 'television' /* ARG3 The english translation */ ))
;

DONE that's it.

Now the following queries could be used (i.e. no different to above bar the name of the language change):-

SELECT qty_listid,title,qty_list,languagename  FROM qty_list 
    JOIN eq_list  ON qty_list.qty_listgroupid = eq_list.groupid
    JOIN language ON eq_list.languageidmap = language.languageid
WHERE languagename LIKE 'Spanish'
;

SELECT qty_listid,title,qty_list,languagename  FROM qty_list 
    JOIN eq_list  ON qty_list.qty_listgroupid = eq_list.groupid
    JOIN language ON eq_list.languageidmap = language.languageid
WHERE languagename LIKE 'Italian'
;

and :-

Adding additional eq_list rows e.g. for Telephone

INSERT INTO eq_list (title,groupid,languageidmap,titleenglishmap) VALUES('telephone',1 /* the groupid */,1,-9999);
/* make english / base title point to itself (not needed for TRIGGER)*/
UPDATE eq_list SET titleenglishmap = eqid WHERE titleenglishmap = -9999;
/* Add the euipment title translations  (note that groupid is irrelevant for)*/
INSERT INTO eq_list (title,groupid,languageidmap,titleenglishmap) VALUES
    ('téléphone',(SELECT groupid FROM eq_list WHERE title LIKE 'telephone'),(SELECT languageid FROM language WHERE languagename LIKE 'French'),(SELECT eqid FROM eq_list WHERE title LIKE 'telephone' )),
    ('telefon',(SELECT groupid FROM eq_list WHERE title LIKE 'telephone'),(SELECT languageid FROM language WHERE languagename LIKE 'German'),(SELECT eqid FROM eq_list WHERE title LIKE 'telephone' )),
    ('teléfono',(SELECT groupid FROM eq_list WHERE title LIKE 'telephone'),(SELECT languageid FROM language WHERE languagename LIKE 'spanish'),(SELECT eqid FROM eq_list WHERE title LIKE 'telephone' )),
    ('telefono',(SELECT groupid FROM eq_list WHERE title LIKE 'telephone'),(SELECT languageid FROM language WHERE languagename LIKE 'italian'),(SELECT eqid FROM eq_list WHERE title LIKE 'telephone' ))
;

SELECT qty_listid,title,qty_list,languagename  FROM qty_list 
    JOIN eq_list  ON qty_list.qty_listgroupid = eq_list.groupid
    JOIN language ON eq_list.languageidmap = language.languageid
WHERE languagename LIKE 'Spanish'
;

SELECT qty_listid,title,qty_list,languagename  FROM qty_list 
    JOIN eq_list  ON qty_list.qty_listgroupid = eq_list.groupid
    JOIN language ON eq_list.languageidmap = language.languageid
WHERE languagename LIKE 'Italian'

• used groupid 1 so results are :-

and