1

I need to create website that will support multiple languages, however I never done so and I need help with creating DB for my application.

Website will be commerce, so let's take ITEM table as an example. Only thing that I could quickly come up to is this model, but I don't think it's good since Language table is tied to Item table but I will have Company table as well and others maybe too.

Item
    ID
    Price

Language
    ID
    ItemID
    Language (example: en-US)
    Field (example: title)
    Value (example: Good Title)

Can someone help me design good database that will support multiple languages?

Stan
  • 25,744
  • 53
  • 164
  • 242

2 Answers2

2

There are multiple ways on how to store multilanguage data in the database, I usually do it like this:

Item
  ID
  price
  title_translation_key_id (is a TranslationKey foreign key)
  desc_translation_key_id (is a TranslationKey foreign key)

TranslationKey
  ID
  key (string)

Translation
  ID
  translation_key_id (is a TranslationKey foreign key)
  content (string)
  language_id (is a Language foreign key)

Language 
  ID
  code

How to retrieve the data?

You can either work with sub-selects or if you database doesn't perform well, you can generate language specific tables out of it.

Community
  • 1
  • 1
Andre Bossard
  • 6,191
  • 34
  • 52
  • I think I will use http://stackoverflow.com/a/929430/440611 approach after all, it seems to be a lot easier to make `ProductTranslation, CategoryTranslation etc..` thanks for answer – Stan Sep 06 '12 at 14:51
0

Another possible way is to keep a translations as a json object. For example 

Item
  Id   Price                       Name
   1    100     {"en":"Car", "fr":"voiture", "am":"մեքենա"}

It's very simple and you don't need to implement any join to retrieve the data. However, you always have to get all translations. If you support a lot of languages this might be not so good.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Gagik Ohanyan
  • 1