localization of dynamic data

Question

I'm looking for advice on best practices for localizing data stored in the database. I'm working on a web application in which all of the static text is localized using files. We have several options the administrator can configure using the UI which are stored in the database and need to localized these values.

We have come up with a couple of possible ideas. What are your thoughts on these solutions? Is there a better option altogether or even a standard best-practice?

Per Field Specialized Localization

This is the solution proposed for best practices for multilanguage database design. We would create a separate table for each localized field. For example, suppose we had the table colors with color_id, color_name and color_description columns, we might break it out into a color table with the non-localized data and a color_translations table color_id, locale, color_name and color_description fields.

However, our customers often send the localization files to a third party to do the translation which becomes tricky.

Single Table Localization

Another option would be to create a single table to represent all of the database localization:

CREATE TABLE localized_text
(
    key          VARCHAR(256) NOT NULL,
    locale       CHAR(5) NOT NULL,
    value        VARCHAR(256),
    PRIMARY KEY (key, locale)
);

This would be easier to export for off-site localization but adds a level of indirection.

Answer 1

We would create a separate table for each localized field. For example, suppose we had the table colors with color_id, color_name and color_description columns....

Assuming your colors table is only static text, the obvious choice is to add a column to it, perhaps named locale and add rows for every locale you care about. Then join to the customer's locale to produce the single description you want.

For this to work, you have to separate static descriptions from locale-independent data, because localized descriptions introduce a many-to-one relationship. As a stopgap you could leave the English descriptions in the main table and drop them once all references to them are gone.