Re: Multilingual table design questions

Guy wrote:

> 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?

Personally, I believe translation is purely a presentation function, rather than a data/business function. I would store the text in whatever language you want as a base level (English, probably), then when displaying the text, translate it. This cuts down on the database size, and leaves all the translation in the Presentation layer. Also, your database schema does not need to change every time a new language becomes supported.

I'm not sure how your UI is implemented, though, so I am unable to give specifics for your implementation.

--