Multilanguage database design approach

Question

I am trying to implement a multilanguage system and I came across a topic on stackoverflow that I didnt get the idea.

The full question can be found here

The option 3 in the question, It has a structure like below

CREATE TABLE T_PRODUCT (
  NAME_FK        int,
  DESCRIPTION_FK int,
  PRICE          NUMBER(18, 2)
)

CREATE TABLE T_TRANSLATION (
  TRANSLATION_ID
)

CREATE TABLE T_TRANSLATION_ENTRY (
  TRANSLATION_FK,
  LANGUAGE_FK,
  TRANSLATED_TEXT NTEXT
)

CREATE TABLE T_TRANSLATION_LANGUAGE (
  LANGUAGE_ID,
  LANGUAGE_CODE CHAR(2)
)

But I didnt get the idea why we need T_TRANSLATION table. What is the job of it ? This question has been asked some comments too, but they did not get an answer on that question.

How to INSERT and SELECT Products by this approach ?

score 4 · Answer 1 · answered Mar 18 '13 at 13:37

4

But I didnt get the idea why we need T_TRANSLATION table. What is the job of it ?

I'm guessing it identifies what field needs to be translated and provides the "target" for FOREIGN KEY from the T_TRANSLATION_ENTRY table. So for a particular field TRANSLATION_ID might be 1, for some other field (not necessarily in the same table) it might be 2 etc... This way, you can have a limited structure of only 3 tables covering translations for unlimited number of "translatable" tables in the rest of the model.

That being said, I'm against such a structure since it doesn't enforce foreign keys properly (the DBMS doesn't "know" what TRANSLATION_ID means and cannot ensure that only valid values are in it) and can be a performance hog to JOIN.

I think it's better to have a straight 1:N relationship between translatable tables and translations even if that means adding many new tables to the model (essentially one new translation table for each translatable table). For example:

enter image description here

answered Mar 18 '13 at 13:37

Branko Dimitrijevic

50,809
10
93
167

what if I have a table with many foreign keys that also need to be translated, for example ( Person [ NationalityId, MaritalStatusId, CourtesyId, FirstName, MiddleName, LastName] ) Nationality,MaritalStatus,Courtesy are tables, does the solution you provided still valid and suitable since the number of joins will be too many – Monah Jul 24 '15 at 15:36
Well, it essentially doubles the number of tables that need to be joined: instead of joining N "normal" tables, you now _also_ join N translation tables. Whether this is a problem must be measured in representative environment. But no other solution that _dynamically_ chooses the language can avoid some kind of extra "join", be it expressed in database or otherwise, and databases are generally good at joining... – Branko Dimitrijevic Jul 24 '15 at 16:34
Hi @Branko, As you said, there can be many tables. But worse, the number of rows in each translation table can be very large and those tables can grow quickly. Does this lead to performance issue? – Arash Dec 19 '21 at 16:47
1

Does that lead to performance issues? Well, only measuring in representative environment and with representative amounts of data can answer that. In principle, a well-indexed table will have O(log(N)) retrieval, so the performance will deteriorate much slower than N (the number of rows). – Branko Dimitrijevic Dec 20 '21 at 07:17
Thanks @Branko. Is it possible (if so, is it better) to have a core schema and multiple schema for multiple language? – Arash Dec 20 '21 at 15:38
Could you expand a bit on what you mean by that? Do you mean not having a LANGUAGE_ID and instead having TABLE1_TRANSLATION_EN, TABLE1_TRANSLATION_FR, TABLE1_TRANSLATION_RU etc...? – Branko Dimitrijevic Dec 20 '21 at 16:23
Yes, i mean that. – Arash Dec 20 '21 at 17:18
Well, that's unlikely to be more performant and has few downsides: you cannot add a new language through DML alone (you must use DDL), you can no longer use static SQL (and must use dynamic SQL - table names cannot be exposed through bound parameters), enforcement of foreign keys can become burdensome (e.g. having 100 languages means that deleting a row from the "main" table requires accessing 100 translation tables)... – Branko Dimitrijevic Dec 22 '21 at 07:47
Thanks @Branko. What if i define a standard for table naming. for example: "`mainTableName_en`" (IETF Tag)? Is "dynamic SQL" downside or adds more flexible? For deleting, i can use triggers and for inserting/updating, i can use batch processing(it's one to one relation and i have the id), right? (sorry if my questions are, how to say, bothering you) – Arash Dec 23 '21 at 19:30
Well, you can do all these things, but I'd recommend sticking to the database's built-in mechanisms (bound parameters instead of concatenating strings, foreign keys instead of triggers; I'm not sure I follow regarding "batch processing"). It's gonna be easier, correct and more performant. – Branko Dimitrijevic Dec 24 '21 at 13:58
Thanks @Branko, for your patience. – Arash Dec 24 '21 at 17:46

Multilanguage database design approach

1 Answers1