How to make database for Multilanguage list app?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

I would suggest having the database as follows:

EQ_LIST

eq_id | group_id | title_en | title_fr | title_de 
  1,       1,         tv ,    la télé,   Fernseher

and also store the user selected language or default language on shared preferences or maybe another table, and according to user selected language you fetch accordingly