Multilingual DB design with similar translatable fields

multilingual database design approach
internationalization database design
storing multiple language data in sql server
mysql multi language support
codeigniter multi language database
multi-language database design
sql multilingual
language table mysql

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
  • Each Item has a translatable description_60, description_180 text, (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 apiSourceNameetc.

There are two options that I see:

1
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

2
descriptions_60_translations
  Id
description_60
apiSourceName
languageId
...
...
3 Other?

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 Languages table, like

Languages(id, name)

Also, you will need an ItemDescriptions table, like

ItemDescriptions(id, itemId, languageID, content)

You will insert values into the Languages table, like

60, 'English' 
180, 'Hungarian'

records into the Items table, like

1, 'Toothpaste'

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 Items table for each item, a single record in the Languages table for each language and as many records in the ItemDescriptions table as many Languages are they translated to.

EDIT

It turns out that there are multiple languages and multiple descriptions per language. So, we need to change the definition of ItemDescriptions to

ItemDescriptions(id, itemId, languageID, content60, content180, content300)

so each record will hold all the respective descriptions.

EDIT2

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):

First Solution

ItemDescriptions(id, itemId, languageID, content, maxLength)

where maxLength can be 60, 180, 300, respectively. Your additional values will be columns inside the ItemDescriptions table. If you use varchar(300) for content, then you will not use unnecessarily bytes to store your values in content.

Second Solution

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.

Comparison

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 insert or 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 trigger on insert or update.

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`.

For example:

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 .