Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

  • Less MySQL JOIN procedures
  • One table, one row
  • Easy handling multiple rows, f.e.: updating X products with Y languages



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:

  • Better access to one language
  • Better database view



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

like image 473
drozdo Avatar asked Jan 23 '15 20:01

drozdo


People also ask

What are best practices for multi language database design?

For an application and its database to be truly multi-lingual, all texts should have a translation in each supported language – not just the text data in a particular table. This is achieved with a translation subschema where all data with textual content that can reach the user's eyes is stored.

What is a translation table in SQL?

A translation table is a template you define in Configuration that outlines how the system translates letter grades into numeric grades and numeric grades into letter grades.

What is translation database?

Welcome to the Translation Database The only resource of its kind, the Translation Database was founded in 2008 by Three Percent and Open Letter Books at the University of Rochester to track all original publications of fiction and poetry published in the U.S. in English translation.


1 Answers

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.

like image 191
Gordon Linoff Avatar answered Sep 29 '22 02:09

Gordon Linoff