13

Good day!

I'm searching for the best way to implement multilanguage database schema, the approach I use is the same as here: What are best practices for multi-language database design? (one table with language neutral data and one for all translation). It seems to be good and clean and doesn't limit number of possible languages.

But I want to use a simple ORM (like LINQ in C#, Outlet for PHP etc) where each table is mapped to entity class. This can work, but queries became more complex.

May be there are some special techniques to use such DB shema with ORM? Or I can benefit from more complex ORMs which support more complex mappings?

Thanks in advance!

Improve this question

1 Answer 1

Reset to default
21

Using only one table for all translations may quickly lead to severe performance and complexity issues for any operation (select/insert/update/delete) ; just try it to understand what I mean.

I would then go for the following method (two tables per "translatable" object), which seems a good balance between performance, complexity, and maintenance issues.

product - id (PK) - number1 - number2 - date1 - date2 ... product_i18n - id (PK) - product_id (FK) - language_id (FK) - string1 - string2 ... language - id (PK) - name

Check how it's done with Propel ORM (PHP) : http://propelorm.org/blog/2011/01/11/propel-gets-i18n-behavior-and-why-it-matters.html

HTH

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks, that the same DB scheme I use, but unfortunatelly ORMs that I use (LINQ2SQL on .NET and Outlet for PHP) doesn't support such scenarios directly. I will definitelly look into Propel.
how would you do a fallback in this case?
Why would you need a fallback?
Fallback is needed in cases the preferred language does not exist. We do it with (up to) two lookups: if first lookup found nothing, then select the oldest-timestamp language for the product.
Ok, I did not understand the meaning of 'fallback' here. I guess that a first query in <tablename>_i18n would be necessary to check if a row with translated values exist. However, that should be avoided when a lot of data is retrieved, as this could lead quickly to perf problems. I would then suggest to select the language once for all rows to retrieve + do a simple LEFT JOIN which will return NULL values if no translation is available, as that would less hurt performance.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.