Need advice on multilingual data storage

Question

This is more of a question for experienced people who've worked a lot with multilingual websites and e-shops. This is NOT a database structure question or anything like that. This is a question on how to store a multilingual website: NOT how to store translations. A multilingual website can not only be translated into multiple languages, but also can have language-specific content. For instance an english version of the website can have a completely different structure than the same website in russian or any other language. I've thought up of 2 storage schemas for such cases:

// NUMBER ONE

table contents // to store some HYPOTHETICAL content
    id         // content id

table contents_loc // to translate the content
    content, // ID of content to translate 
    lang,    // language to translate to
    value,   // translated content
    online   // availability flag, VERY IMPORTANT

ADVANTAGES:
- Content can be stored in multiple languages. This schema is pretty common, except maybe for the "online" flag in the "_loc" tables. About that below.
- Every content can not only be translated into multiple languages, but also you could mark online=false for a single language and disable the content from appearing in that language. Alternatively, that record could be removed from "_loc" table to achieve the same functionality as online=false, but this time it would be permanent and couldn't be easily undone. For instance we could create some sort of a menu, but we don't want one or more items to appear in english - so we use online=false on those "translations".

DISADVANTAGES:
- Quickly gets pretty ugly with more complex table relations.
- More difficult queries.



// NUMBER 2

table contents // to store some HYPOTHETICAL content
    id,        // content id
    online     // content availability (not the same as in first example)
    lang,      // language of the content
    value,     // translated content

ADVANTAGES:
1. Less painful to implement
2. Shorter queries

DISADVANTAGES:
2. Every multilingual record would now have 3 different IDs. It would be bad for eg. products in an e-shop, since the first version would allow us to store different languages under the same ID and this one would require 3 separate records to represent the same product.

First storage option would seem like a great solution, since you could easily use it instead of the second one as well, but you couldn't easily do it the other way around. The only problem is ... the first structure seems a bit like an overkill (except in cases like product storage)

So my question to you is:

Is it logical to implement the first storage option? In your experience, would anyone ever need such a solution?

Answer 1

The question we ask ourselves is always:

Is the content the same for multiple languages and do they need a relation?

Translatable models

If the answer is yes you need a translatable model. So a model with multiple versions of the same record. So you need a language flag for each record.

PROS: It gives you a structure in which you can see for example which content has not yet been translated.

Separate records per language But many times we see a different solution as the better one: Just seperate both languages totally. We mostly see this in CMS solutions. The story is not only translated but also different. For example in country 1 they have a different menu structure, other news items, other products and other pages.

PROS: Total flexibility and no unexpected records from other languages.

Example We see it like writing a magazine: You can write one, then translate to another language. Yes that's possible but in real world we see more and more that the content is structurally different. People don't like to be surprised so you need lots of steps to make sure content is not visible in wrong languages, pages don't get created in duplicate etc.

Sharing logic So what we do is most time: Share the views, make the buttons, inputs etc. translatable but keep the content seperated. So that every admin can just work in his area. If we need to confirm that some records are available in all languages we can always trick that by creating a link (nicely relational) between them but it is not the standard we use most of the time.

Really translatable records like products Because we are flexible in creating models etc. we can just use decide how to work with them based on the requirements. I would not try to look for a general solution which works for all because there is none. You need a solution based on your data.

Answer 2

Assuming that you need a translatable model, as it is described by Luc, I would suggest coming up with some sort of special-character-delimited key-value pair format for the value column of the content table. Example:

@en=English Term@de=German Term

You may use UDFs (User Defined Functions in T-SQL) to set/get the appropriate term based on the specified language.

For selecting :

select id, dbo.GetContentInLang(value, @lang) from content

For updating:

update content set value = dbo.SetContentInLang(value, @lang, new_content) where id = @id

The UDFs:

a. do have a performance hit but this also the case for join that you will have to do between the content and content_loc tables

and

b. are somehow difficult to implement but are reusable practically throughout your database.

You can also do the above on the application/UI layer.