Multilingual table design questions

I've been reading a few posts on multilingual table design. For a simple table like:

cars = { id, name }

...here are the design approaches I've seen:

1. Adding columns for languages: name_fr, name_ru, name_ch..etc
   • rejected: adding a language would required changing the db schema and the extracting sql or procedures.
2. Adding a language id column: id, lang_id, name
   • rejected: not normalized enough. The column name should contain the name of the products, not the various translations of the name. If I have not french translation for a cars, which language do I display to a user set up with a french preference?
3. Adding a translated table to each table: cars_transl
   • rejected: I would have to create and maintain two tables instead of one in my schema, and will be forced to join two tables continuously en every SQL.
4. Adding a global transation table: text_translation
   • rejected: this table could become very big and I would be forced to join with this monster at every sql operation, degrading the performance.

So in the light of these examples I am considering another approach. Store the translation of the name field in another field containing XML metada:

cars = { id, name, name_tr }

If the car name is entered in any language, then the translation would be put in name_trm in XML format:
ex:

   name = "volks wagen"
   name_tr = "
    <en>People's trolley"</en>
    <fr>Voiture du peuple</fr>
    ..."

This way, a browser of my table would quickly see all the cars defined in whatever language the creator of the row used originaly (my web site will be used by int'l users). On a second step, if a translation is required, it can be done by the UI without a second DB hit. Just parse th name_tr field with an xml parser, extract and display the translation using the pref of the user.

The only problem is I don't know the actual language used by the creator of the row.

Anybody has a better approach? Received on Sun Feb 19 2006 - 10:16:17 CST