CODEWITHSUNDEEP
phpmysqldatabase
drozdo
drozdo

Reputation: 515

Better way of storing translations in MySQL

What is the best way of storing multilanguage fields in database and updating/accessing to it?
I am handling 6 languages.

Examples with 2 rows and 2 languages

Option 1:
Database - 1 table design:

table1.products
id --- parent -- product_name
1  ---    5   -- a:2:{s:2:"en";s:3:"dog";s:2:"cs";s:3:"pes"}
2  ---    5   -- a:2:{s:2:"en";s:3:"cat";s:2:"cs";s:5:"macka"}

Updating this option 1 is too easy
1. Merge old translations array with updated or new language fields (PHP array_merge)
2. Serialize merged array
3. Simple update database.

Advantages:



Option 2:
Database - 2 table design:

table1.products
id --- parent
1  ---    5  
2  ---    5  

table2.products_translations
product_id --- lang -- product_name
    1      ---  en  -- dog
    1      ---  cs  -- pes
    2      ---  en  -- cat
    2      ---  cs  -- macka

Updating this option 2 is a bit harder.

Advantages:



What do you mean to be the best database solution? Each product will contain ifinite blocks of texts (descriptions) in all languages.
Thanks

Upvotes: 7

Views: 2631

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The best way to store data should be defined by the access patterns on the data, as well as the requirements for data integrity.

Your second solution is the more typical way of storing data in a database. It is normalized. It allows you to access any particular language for any product. More importantly, you can add more languages and easily make changes. This would be the more desired method.

I wouldn't worry about the performance of the joins. With proper indexing, this should be quite efficient.

There are some circumstances where the first method might be desirable. If your underlying code is always using all the languages at the same time, for instance, then returning the structure as a JSON'ish array of arrays of strings may be desirable, with the additional parsing going on in the application layer. However, returning all products in one particular language would have a lot of performance overhead, particularly if you wanted all products that started with 'A'. It is also hard to update one particular language value for one product.

My strong inclination would be the second method, unless you have a really, really good reason for the first method.

Upvotes: 7

Related Questions