Re: Multilingual table design questions

Guy wrote:

> I am using the ASP.NET framework and C#. Their recommendation as to how
> to deliver translations seems to concurr with your idea. Have the UI do
> it, not the database. They use "satellite assembly" for each langage
> supported. A satellite assembly is just a DLL which contains an XML
> file. The XML file contains name+value pairs, the name identify the
> string to translate (Ex: MSG_ERROR_1) and the value the translation.
> This works great when each message can be identified: BUTTON_1_TEXT,
> LABEL_2_TEXT....as GUI elements, but in my case the users of the site
> will be entering the value in the table row, so no name value pairs
> there.
>
> I think now the simplier solution will be to maintain a language table:
> lang = { id, code } ("fr", "en"...), and add force the insertor of a
> row to specify the lang code: cars = {id, lang_id, name}, and use
> another table to store the various translation: cars_tr = {id, lang_id,
> name}, then I need to join only if a Web user specifies a particular
> language, or a translation.

This will work, I agree, but I would question whether a [cars_tr] is a totally separate entity, distinct from the [cars] table. Translating text, in most situations, does not alter the information that is actually being stored in the database, but it will change the information that is displayed to the user.

Another example would be currency. If the database was for products, and the user requirement be that prices be available in USD, AUD, Yen, English Pounds, and Euro, would you have 5 additional columns for each of the prices, and select the appropriate one? The correct solution would be to store a single price in the most common currency (USD, for example), and then convert the amount on the fly.

--