Multilanguage Database: which method is better?

Question

I have a Website in 3 languages.

Which is the best way to structure the DB?

1) Create 3 table, one for every language (e.g. Product_en, Product_es, Product_de) and retrieve data from the table with an identifier:

e.g. on the php page I have a string:

$language = 'en'

so I get the data only

SELECT FROM Product_$language

2) Create 1 table with:

ID  LANGUAGE   NAME    DESCR

and post on the page only

WHERE LANGUAGE = '$language'

3) Create 1 table with:

ID  NAME_EN   DESCR_EN   NAME_ES   DESCR_ES   NAME_DE   DESCR_DE

Thank you!

Answer 1

I'd rather go for the second option.

The first option for me seems not flexible enough for searching of records. What if you need to search for two languages? The best way you can do on that is to UNION the result of two SELECT statement. The third one seems to have data redundancy. It feels like you need to have a language on every names.

The second one very flexible and handy. You can do whatever operations you want without adding some special methods unless you want to pivot the records.