1

I am attempting to introduce multi-language support into the back end of an application, and trying to figure out a strategy to efficiently implement this into the current schema.

I currently have multiple tables, all of which include standard english values.

My idea is in each table w/ english values, include a foreign key that relates to a single "Language" and multiple "Language values" in the table holding translations

So for ex.

Table-1

Value     |    Lang_ID
"This is a sentence"    |    1
"This is also a sentence"    |    2
"Translate this"    |    3

Table-2

Value     |     Lang_ID
"This is a sentence from another table"    |    4
"This table is different from table-1"    |    5

Language-Table

Lang_ID    |    Lang_Code    |    Value
1    |    "ZHO"    |    "这是一句话"
1    |    "SPA"    |    "esta es una frase"
2    |    "FRA"    |    "c'est aussi une phrase"
3    |    "SPA"    |    "traduce esto"
4    |    "FRA"    |    "ceci est une phrase d'un autre tableau"
....

My thinking is you would then just need to query Language-Table by WHERE Lang_ID=? AND Lang_Code=? to get the translation for that specific value.

I'm wondering a couple things

1) Is this a good practice?

2) How do I generate foreign keys that don't exist yet over multiple tables and keep each one unique (so as not to have two translations of different text values fall under the same Lang_ID)

pjad
  • 11
  • 2

1 Answers1

1

Using another table to store the translations is a good idea. I have used it in my work as much as for personal projects. In my case, I designed it translation opened from the begining therefore I have no string value in my tables, even the default english values are in the translation table.

I have also put the language codes in a separate table, though I have never found a situation where I needed it, therefore I suggest you keep them where they are.

For the unique id, I suggest using a sequence like so (shamelessly stolen here):

create sequence sequence_name
    start 1
    increment 1
    NO MAXVALUE
    CACHE 1;

And use next_val('sequence_name') whenever inserting a new field to translate.

TheWildHealer
  • 1,546
  • 1
  • 15
  • 26
  • So just to reiterate in regards to the sequence, when inserting a new value in my backend code, in the Lang_ID field (no matter the table) I can use this sequence to generate the unique key for that particular value? – pjad May 09 '19 at 15:22
  • each time you will call `next_val('sequence_name')`, it will give you the next int you can use as a key for that string field. The first call will give you 1, the second 2 ... – TheWildHealer May 09 '19 at 15:23