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!

• Floris: Option 4: Have one table with descriptions in English. Have another table that translates phrases. If a phrase already exists, you have the translation. If not, then add it. This creates a "dictionary". Is that more efficient? Depends a bit on whether you re-use lots of strings or not (much, ever, ...) - which in turn depends on how big the database is, really. It does make things fractionally more maintainable, I think.

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.

• Cano64: #2 is the way to to go. Keep in mind to create a separate product table for attributes that are not language specific, like price, qty, UPC, ... This table will be your master table for products.

Answer 2

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

My preference to this approach is that you don't need to change the DB schema at all should you add additional localizations. You also should not need to change your related code in this case either, as language identifier just becomes another value that is used in the query.

• user2120569: Thank you, I'll go for the second

Answer 3

That way you would be killing the database in no-time.

Just do a table like:

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

This will allow you, not only to have a better structured database, but you could even have products that only have one translation. If you want you can even want to show the default language if no other is specified. That you'll do programmatically of course, but I think you get the idea.

• user2120569: This is what I wrote on the second method! Thank you
• Ivo Pereira: For me, this is the most optimized method :) it will allow you to handle it how you really want to. You decide on the code, and you keep intact a clean database :)