Storing i18n data in a database using XML

Question

Hello,

I may have to store some i18n-ed data in my database using XML if I don't fight back. That's not my choice, but it's in the specifications I have to follow. We would have, by example, something like following in a 'Country' column:

<lang='fr'>Etats-Unis</lang>
<lang='en'>United States</lang>

This would apply to many columns in the database.

I don't think it's a good idea at all. I tend to think that a cell in a database should represent a single piece of data (better for look-up), and that the database should have two dimensions maximum and not 3 or more (one request more would be required per dimension / a dimension here would be equal to the number of XML attributes).

My idea was to have a separate table for all the translations, with columns such as : ID / Language / Translation. However, I should admit that I'm really not sure what is the best way to store data in various languages in a DB...

Thanks for your advices :)

Answer 1

Agree with your vision of thinking.

In a previous project, we did the same thing (stored I18N values in DB), in addition to traditional I18N (resource files in solution).

The reason for this was for special dynamic data (not static strings) that required translation. We had sports - such as "Soccer" which needed to be translated to their language-specific description.

To do this, we had a simple lookup table, with columns (LCID - int, SportID - int, Description - nvarchar(256)).

The stored procedures/functions accepted the locale as an integer (e.g Spanish - 3082), then the SQL would return the appropriate culture-sensitive description based on the locale. This way the web server code had no knowledge of this behind the scenes.

So you might have the following few rows:

Lcid       SportID      Description
3081       1            Soccer
3082       1            Futbol

Then you simply join on this table in your SQL to facilitate the I18N.

The small drawback of this method though is that your SQL SP's will require the LCID as a parameter from the web server code.

The Sports were updated/imported via an excel sheet bulk import, that's why we needed the I18N in the DB.

This is the only way i can envision DB-based I18N from occuring.