Re: Multilingual table design questions

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Mon, 20 Feb 2006 06:02:08 GMT
Message-ID: <AXcKf.2046$VI6.1757_at_newsread1.news.pas.earthlink.net>

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?

This is the approach I'd use, with a fall-back rule for dealing with missing translations (such as: use American if there is no French).

I'd like to see your justification of 'not normalized enough'. It looks to me as if the value in 'name' is functionally dependent on the id and the language - so the table is in 5NF to my eyes.

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

(a) The whole world is not necessarily a web browser.
(b) I would not use XML for this job without a lot more provocation.
(c) If I needed to use XML, I would use XML in layout 2.
(d) The ad hoc attempt to include a list of values in the extra field
     endangers 1NF for your table.  The MV camp will be happier with
     this design than the TRDBMS bigots (such as me).


-- 
Jonathan Leffler                   #include <disclaimer.h>

Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ Received on Mon Feb 20 2006 - 07:02:08 CET