CODEWITHSUNDEEP
phpmysqldatabasemultilingual
user2120569
user2120569

Reputation: 235

Multilanguage Database: which method is better?

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!

Upvotes: 6

Views: 914

Answers (3)

John Woo
John Woo

Reputation: 263723

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.

Upvotes: 4

Ivo Pereira
Ivo Pereira

Reputation: 3500

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.

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

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.

Upvotes: 1

Related Questions