Good database table design for storing localized versions of data [closed]

Question

I'm trying to design some tables to store some data, which has to be converted to different languages later. Can anybody provide some "best practices" or guidelines for this?

Thanks

Answer 1

Let's say you have a products table that looks like this:

Products
----------
id
price

Products_Translations
----------------------
product_id
locale
name
description

Then you just join on product_id = product.id and where locale='en-US'

of course this has an impact on performance, since you now need a join to get the name and description, but it allows any number of locales later on.

Answer 2

Can you describe the nature of the 'dynamic data'?

One way to implement this would be to have 3 different tables:

• Language Table
  • This table would store the language and a key :

    [1, English], 
    [2, Spanish]

• Data Definition Table
  • When dynamic data is first entered make a record in this table with and identifier to the data:

      [1, 'Data1'], 
      [2, 'Data2']

• Data_Language Table
  • This table will link the language, data definition and translation

      So: [Data_Language, Data_Definition, Language, Translation]
          [1, 1, 1, 'Red']
          [2, 1, 2, 'Rojo']
          [3, 2, 1, 'Green']
          [4, 2, 2, 'Verde']

          etc ...

When the dynamic data is entered create the default 'English' record and then translate at your leisure.