Database Design Pattern (MySQL) of multilingual web application

Question

we are developing an web application which is supposed to support multi languages the issue is when it comes to dynamic fields and how to design the database for them

let say we have these tables :

  product(id(Primary),name(text),description(text))

  Category(id(Primary),name(text),description(text))

when it comes to inserting data in database we should have name, description in 3 different languages so far I decided to implemented the database as follow: 1. Create a table as an intermediate table with following structure :

 language_itme(id)

then create another table language with following structure like :

 language(id(Primary),field_name(text),en(text),fr(text),it(text),language_item_id(INT))

the language item_id refers to id of language_item table then adding the following fields to the table product and Category,

  product(id(Primary),name(text),description(text),language_item_id(INT))

  Category(id(Primary),name(text),description(text),language_item_id(INT))

So for inserting a product we are going to insert these rows in database:

   Auto_Generated_num -> language_item;

Then,

   ( Auto_Generated_num, productName,productDescription, last_id of language_item)
    -> product

and then

  ( Auto_Generated_num, name, productName in English, productName in French,
   productName in Italian, last_id of language_item) -> language

  ( Auto_Generated_num, description, productDescription in English,  
     productDescription in French, productDescription in Italian,
       last_id of   language_item) -> language

Does anyone here can help me with modification of this structure to make it better from design and performance point of view, or even have the experience of multi language web sites and provide me with a better solutions

thanks in advance

DrabJay DrabJay · Accepted Answer · 2014-03-12T20:16:53

Not withstanding the other answers to similar questions on this site I came up with the following schema for multi-lingual support:

     +---------------+
     | PRODUCTS_B    |
     +---------------+
     | product_id    |
     | product_code  |
     | ...           |
     +---------------+
             |
             |
+-------------------------+
| PRODUCTS_T              |    +---------------+
+-------------------------+    | LANGUAGES     |
| product_id              |    +---------------+
| translation_language_id |----| language_id   |
| product_name            |    | language_code |
| product_desc            |    +---------------+
| ...                     |          |   |
+-------------------------+          |   |
             |                       |   |
             |                       |   |
+-------------------------+          |   |
| PRODUCTS_L              |          |   |
+-------------------------+          |   |
| product_id              |          |   |
| language_id             |----------+   |
| translation_language_id |--------------+
+-------------------------+

LANGUAGES contains all the languages that you wish to support in your application, it has a primary key of language_id:

1 ENG
2 FRA
3 ITA

PRODUCTS_B contains the non-translatable attributes of each product, it has a primary key of product_id:

1 PROD/0A

A record would be inserted into PRODUCTS_T for each available translation, it has a compound primary key of product_id and translation_language_id:

1(PROD/0A)  1(ENG)  Computer  A programmable device

A record would be inserted into PRODUCTS_L for each supported language; indicating this was the translation to use; it has a compound primary key of product_id and language_id:

1(PROD/0A)  1(ENG)  1(ENG)
1(PROD/0A)  2(FRA)  1(ENG)
1(PROD/0A)  3(ITA)  1(ENG)

PRODCUTS_L should contain every value in the Cartesian Product of PRODUCTS_B and LANGUAGES.

As the translations became available the records in the PRODUCTS_T and PRODUCTS_L tables would be amended as appropriate:

1(PROD/01)  1(ENG)  Computer    A programmable device
1(PROD/0A)  2(FRA)  Ordinateur  Un dispositif programmable

1(PROD/0A)  1(ENG)  1(ENG)
1(PROD/0A)  2(FRA)  2(FRA)
1(PROD/0A)  3(ITA)  1(ENG)

And eventually:

1(PROD/01)  1(ENG)  Computer    A programmable device
1(PROD/0A)  2(FRA)  Ordinateur  Un dispositif programmable
1(PROD/01)  3(ITA)  Computer    Un dispositivo programmabile

1(PROD/0A)  1(ENG)  1(ENG)
1(PROD/0A)  2(FRA)  2(FRA)
1(PROD/0A)  3(ITA)  3(ITA)

I used this method as I did not have every translation available at the time the product information was to be inserted. It also indicated, when multiple but not all translations were available, which translation should be used for each language.

Database Design Pattern (MySQL) of multilingual web application

2 Answers