CODEWITHSUNDEEP
databaset-sqldatabase-designmultilingual
Barış Velioğlu
Barış Velioğlu

Reputation: 5827

Multilanguage database design approach

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 ?

Upvotes: 1

Views: 4139

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52147

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

Upvotes: 4

Related Questions