Multilingual DB design with similar translatable fields
I am not in the process of designing the DB schema for a new project that I am working on.
So, the challenge is as follows:
- There is a table
Itemhas a translatable
description_180text, (the suffix number stands for the type of description that is stored, e.g 60 means 60 char long) and some fields associated to each of those such as
There are two options that I see:
descriptions_translations Id description_60 description_180 description_300 apiSourceName_60 apiSourceName_180 .... ...
which does not look very good as we might end up with lots of NULL fields and
descriptions_60_translations Id description_60 apiSourceName languageId ... ...
I am totally open to other recommendations!
Also, another challenge is that I want to store in the main
Item table the
description_60 text. Is this possible without duplicating data?
Update Leaning more towards this based on answers:
descriptions_translations ========================= id itemId description_type =>60, 120, 180 etc `description` => 'This video is ...' apiSourceName => youtube, dailymotion etc languageId => en, es etc ... ...
Any cons against using the same column type for texts of 60 chars and 1000 chars long?
You will need a
Also, you will need an
ItemDescriptions(id, itemId, languageID, content)
values into the
60, 'English' 180, 'Hungarian'
records into the Items table, like
and records into the ItemDescriptions table, like
1, 1, 60, 'Best Toothpaste' 2, 1, 180, 'Legjobb Fogkrém'
so you will have a single record in the
table for each item, a single record in the
table for each language and as many records in the
table as many
Languages are they translated to.
It turns out that there are multiple languages and multiple descriptions per language. So, we need to change the definition of
ItemDescriptions(id, itemId, languageID, content60, content180, content300)
so each record will hold all the respective descriptions.
Since you described that you will need additional data for each description, it becomes clear that a given description will no longer be an attribute, but a record. This means that we have two possible solutions (for both solutions I avoid the definition of additional data due to lack of information, but you will be able to define their respective columns):
ItemDescriptions(id, itemId, languageID, content, maxLength)
maxLength can be 60, 180, 300, respectively. Your additional values will be columns inside the
table. If you use
content, then you will not use unnecessarily bytes to store your values in
ItemDescriptions80(id, itemId, languageID, content)
ItemDescriptions180(id, itemId, languageID, content)
ItemDescriptions300(id, itemId, languageID, content)
These separate tables will store the separate values and in this case you will need separate columns in each tables with the additional data.
If you use
varchar as a type for
content, then the first approach seems to be superior to the second one, in terms of simplicity of handling, however, whenever you
update a value there, you will need to make sure that
maxLength is valid (equals 60, 180 or 300, respectively) and that
content is not longer than
maxLength. You can do this from your application, or via a record-level
Multilingual DB design with similar translatable fields, I am not in the process of designing the DB schema for a new project that I am working on. So, the challenge is as follows: There is a table Items Suppose we are asked to design a data model for a multilingual e-commerce application. We’d need to store text fields like product_name and the description of products in various languages. We’d also need to store text fields in other tables, such as the customer table, in all these languages.
A good way to do this and avoid displaying rubbish to users:
Put, in your Items table, an actual description field. For example, the US (where we are backward on weights and measures) it might be:
Bread, brown, 1 pound loaf
Then build a translation table with three columns:
lang original translated es Bread, brown, 1 pound loaf Hogaza de pan integral, 450g fr Bread, brown, 1 pound loaf Miche de pain brun, 450g de Bread, brown, 1 pound loaf Laib Schwarzbrot, 450g
Then do a query like this to fetch the translation:
SELECT COALESCE(t.translated, i.name) as name FROM Items LEFT JOIN Translation t ON t.lang = 'se' AND i.name = t.translated
That way, your Swedish customer will get the original item name (until you provide the Swedish translation), and your Mexican customer will get an appropriate translation. The trick is the
COALESCE ... LEFT JOIN query pattern.
You may want to match translations on name id values rather than the names themselves. But, for what it's worth the localization in common systems like WordPress match on the text of the names like I suggest.
Edit About efficiency of using text to match rather than ids.
Let's say you have ten million items in your translation table. That will be, on average, 200 bytes per item. With indexes, let's say 400 bytes per item. That's 4 gigabytes for the table. That will cost something like USD 0.11 to 0.14 per month in a high quality cloud machine. Using an ID would be a little less than half that. Say 1.5 gigabyte. So the difference is around USD 0.06 per month. Plus, cloud machines come with minimum storage sizes.
Lookups: If you index your tables correctly, text matching isn't hugely slower than id matching. And, it will happen not in bulk, but when people look up information.
An approach to multilingual db design, I would suggest the following structure due to my experiences in some other The translation table should hold back all translations for every translatable term. which all means the same but in a different context and all can have the same The LocalizedDesc table's Guid field, in turn, corresponds to any guid used in tables throughout the db, making it a parent table to every table in the system. In rare cases where a table record needs another localized resource, an additional field is used in the table that will also point to a LocalizedDesc record.
Vertabelo Database Modeler, Suppose we are asked to design a data model for a multilingual Here, the translation table id remains the same for a field value in any Multilingual Fields. WordPress stores all the site content in a database, which, in a simple way, can be seen as a collection of tables, with rows and columns. Each row stores a unit of information with labels attached to each field. For example, the title of the post or page is stored in the column “post_title” of table “posts”.
Best Practices To Localize Your Data Model for a Multi-language , To begin with localization, content translation is the most important point. The second most important point is to design an efficient and robust database model to store To design a data model for a multiple language travel portal, you will You will also need to store text fields in subsequent/ other tables, Setting the Field Groups (acf-field-group) post type to be translatable. To translate the Field Group, navigate to Custom Fields-> Field Groups and click on the Edit link of the field group you want to translate.
Tutorials, Column Approach; Multirow Approach; Single Translation Table Approach is may be a number of such columns in your table, like: title, name, description etc.). new language requires schema changes (and special access rights for db user) for querying for multilingual data options 3 or 4 would be a possible solution. Teams. Q&A for Work. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.
Database Internationalization(I18N)/Localization(L10N) design , In this article, I am going to present different database design patterns for content localization. We will manage data in the rows based on a column like language_code. Separate translation table approach for Internationalization. Pros:-. You’ve now learned how to add multilingual support to an Eloquent model. I’ve created a repository containing all examples of this post on GitHub . If you want to know more, I recommend reading the examples in the readme of Dimitris’ package .