Multilingual Database with Entity Framework 4 Guide

Question

We are creating a large e-commerce database that should allow data in several languages. For example, a product table will require one or more translations for a name, description, meta tag, meta text, metadata, etc.

There are several ways to achieve this in terms of a relational database. But Entity Framework 4 adds a few limitations, and performance is a big concern.

A similar question, as in the Multilingual database

Here is an example of the set of tables that we are considering:

[Product] - Id (PK) - CreateDate - NamePhraseId (FK) - DescriptionPhraseId (FK) - Price - ... [Phrase] - id (PK) - Invariant [Translation] - id (PK) - PhraseId (FK) - LanguageCulture (Example: en-US) - Translation 

We can also add a LanguageCulture lookup table.

This method has pros and cons, like other methods. We do not want to create additional tables for each column of the table, which may require translation (for example, there are no product names, ProductDescription tables), as this would make our data model too large.

In the above example, the product name will have zero or one phrase with one or more translations. As far as I remember, the Entity Framework requires 1 to 1 relationships to have the same primary key in the tables, I don’t know if the same case was with 0 or 1 relationships, but it can be a parser for the above method.

It is very difficult for me to find good information about the Entity Framework and the multilingual database / model development guide. I would love to get tips and tricks with an emphasis on good design and best performance.

Thanks in advance!

Answer 1

Given the support for EF4 POCO, I would say that the design of your database should have less in common with EF and much more with a good multilingual design.

According to the documentation, EF maintains a zero or one relationship. Just make PhraseId in your Translation table null, and this should result in a ratio of 0..1.