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!

Community
  • 1
  • 1
artvolk
  • 9,448
  • 11
  • 56
  • 85

1 Answers1

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

Maxime Pacary
  • 22,336
  • 11
  • 85
  • 113
  • 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. – artvolk Jan 21 '11 at 09:01
  • how would you do a fallback in this case? – GorillaApe May 01 '12 at 10:13
  • Why would you need a fallback? – Maxime Pacary May 01 '12 at 11:06
  • 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. – Thunder Rabbit Oct 16 '12 at 00:54
  • 1
    Ok, I did not understand the meaning of 'fallback' here. I guess that a first query in `_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. – Maxime Pacary Oct 16 '12 at 10:06
  • Dead link. Content at http://propelorm.org/blog/2011/01/11/propel-gets-i18n-behavior-and-why-it-matters.html – AaronSantos Jun 18 '13 at 10:35