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
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With