localization of dynamic data

Question

Hi All,

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.

Thanks,
Ross