Multilingual Database with Entity Framework 4 Guidance

Question

We are creating a large e-commerce database that needs to allow for data in multiple languages. For example, the product table will need one or more translations for Name, Description, MetaTitle, MetaKeywords, MetaDescription and so on.

There are several ways to accomplish this from a relational database design standpoint. But Entity Framework 4 adds a few constraints and performance is a big concern.

Similar issue as in Multilingual Database Design

Here is an example set of tables 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 could also add a LanguageCulture lookup table.

This method has it's pros and cons like other methods. We don't want to have to create additional tables for each table column that may require translation (no ProductName, ProductDescription tables for instance) as that would make our data model too large.

In the example above, a Product Name will have zero or one Phrases with one or more translations. As I recall, Entity Framework requires 1 to 1 relationships to have the same primary key on the tables, I don't know if that's the same case with 0 or 1 relationships but that may be a dealbreaker for the method above.

I've had a really hard time finding good information about Entity Framework and Multilingual database/model design guidance. I would greatly appreaciate advice and guidance with emphasis on good design and the best possible performance.

Thanks in advance!

Answer 1

Given EF4's POCO support, I'd say the design of your database should have less to do with EF and more to do with good Multilingual design.

According to the documentation, EF supports "zero or one" relationships. Simply make the PhraseId in your Translation table nullable and that should result in a 0..1 relationship.

Answer 2

Do you find something for this issue, I'm in the same situation.