Good database table design for storing localized versions of data

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

I beleve that more information on what you are doing would be helpful. CAn you give some samples of the data? And what do you mean by dynamic? That there will be lots of data inserted over time and lots of changes to the data or that the data only needs to be available for a small period of time.

Answer 2

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 3

In general, you should probably be looking at a parent with common non-localized data, and a child table with the localized data and the language key. If by dynamic, you mean that it changes frequently, you may want to have a look at using triggers and something like a 'translationRequired' flag to mark things that are in need to translation after a change is made.

Answer 4

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.