How to design tables when using multiple languages

Question

How do you suggest to Design tables when working with multiple languages on a site? Say for example that we have a table called product. That one might contain stuff like SKU, price, sort order and other settings. The name, and description however will be available in several languages. should these texts be in individual tables like:

tbl product_lang_en
product_id | name | description
tbl product_lang_de
product_id | name | description

Or should everything be collected in one table
tbl product_lang
product_id | lang | name | description

The latter feels more right and will keep the integrity of the database if a new language is added. Are there any difference in performance to keep in mind when the tables are growing large? Ease of maintenance etc

Cheers

Answer 1

The second design is definitely better. Clear tip-offs about the first: the two tables have the same columns, and facts about the data are in the names of the table ("en", "de").

As far as performance goes, it might even be better with one table than two (or more). If you create the proper indexes (you'll want one on lang, for example), then only one table will need to be accessed to find the description of any product.

Answer 2

I want to suggest another solution:

product
--------------------------------------
id    SKU(or any other internal name)   
//depending on the structure, you can skipp the id and replace it with the SKU/...

lang
--------------------------------------------------
id    product_id   name     lang     description

This might seems like a drawback but for me it would have some advantages:

• There can be more info related to the product that really belongs to the product e.g. price, VAT, ....
• When you remove one product, you can have constraints that remove also the matching languages for it
• The SKU from my example can be virtually anything that help internal (meaning not exposed to a user) number, name whatever. This way, there is no need for someone to look through all languages for a certain product. It can get pretty hard in your second design for someone to remove products in many different languages.
• A application accessing the data can decide on it's own what language it should deliver. When doing the UI, you just need to worry about the unique identifier from the products table, and the code can decide what language is the proper one and select the rows accordingly
• As Ned Batchelder mentioned above, indexes are very important and the performance difference (this approach will be slower of course) will be very small, but the maintainability will be given more easily IMO since there is no need to know what*s going on in lang (literally speaking, I think you get the point).

This may seem a little over the top, but I think I would go with such an approach.