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.

Is there a reason that these "several options" aren't just stored as codes in the DB and translated at the app presentation level? Anyway I think option one is much cleaner, because a giant table with lots of different translations for many diff. models can turn hellish quickly — Java Drinker, Jun 29 '10 at 13:32
I think the main objection to just storing codes in the database is that the user then has to go to two different places to set this up. That's fine for our larger customers who send the localization files off for translation but not ideal for smaller customers. Even the larger companies would have to copy the keys from the UI into the file for translation. One possible solution to that might be to update the UI to modify the localization files as needed. Does anyone have any thoughts on that? — Ross, Jun 29 '10 at 16:15
Do option #1 and implement a simple import/export tool for your customers to use to translate the custom content outside of the app. — Mike Sickler, Aug 18 '11 at 03:21

score 1 · Answer 1 · answered Mar 14 '13 at 04:43

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.

localization of dynamic data

1 Answers1