Multilingual database: which method is better?

Question

I have a website in three languages.

What is the best way to structure a database?

1) Create 3 tables, one for each language (for example, Product_en, Product_es, Product_de) and extract the data from the table with the identifier:

eg. on the php page I have a line:

$language = 'en' 

so I only get data

 SELECT FROM Product_$language 

2) Create 1 table with

 ID LANGUAGE NAME DESCR 

and publish only on the page

 WHERE LANGUAGE = '$language' 

3) Create 1 table with

 ID NAME_EN DESCR_EN NAME_ES DESCR_ES NAME_DE DESCR_DE 

Thanks!

Answer 1

I would prefer to use the second option .

The first option seems to me not flexible enough to search for records. What if you need to look for two languages? The best way to do this is the UNION result of two SELECT . The third has data redundancy. It seems that you need to have a language in all names.

The second is very flexible and comfortable. You can do whatever operations you want without adding special methods if you do not want to rotate records.

Answer 2

I would choose option one or two. Which one really depends on your application and how you plan to access your data. When I did similar localization in the past, I used a single table approach.

My preference for this approach is that you do not need to change the database schema at all if you add additional localizations. You also do not need to change your associated code in this case, since the language identifier simply becomes a different value that is used in the request.

Answer 3

Thus, you will kill the database at any time.

Just execute a table like:

 TABLE languages with fields: -- product name -- product description -- two-letter language code 

This will not only allow you to have a better structured database, but you can even have products that have only one translation. If you want, you can even show the default language unless otherwise specified. Of course, you will do it programmatically, but I think you understand.