Your architecture may vary depending on how your application is accessed/used/deployed.
I tend to prefer c- key/lang-id/translation With perhaps a few additional fields for version management ( so I can keep translations for all versions of the software in one place.) Usually the translation field is not the translation but a key identifying the translation. This makes the look up very fast, with many keys per block and generally they are held in memory cache. The actual translations can then be close together on disk by language, or by frequency of use, to improve performance. For example, at 8am EST the US wakes up and English gets busy. by 8pm English gets quiet but Japanese gets busy. It's possible to organize information to optimize performance. (Assuming you have large quantities of text, where it makes a difference.) I don't use b) (table with many columns by language) because access or modification to one language may be impacted by the association with many other fields. (Depends on the database implementation and for example whether you use fixed width or varying fields.) Also it makes adding or removing a language more traumatic. Where each language is its own record, you just create the lang-id and add records as you translate. I use a fallback heirarchy for missing records. This also uses less disk space. Since you are accessing by the full key, I don't see how collation is relevant. In fact with these keys binary collation is fastest. Often I do not use the english text as the key, but a generated identifier. Otherwise, insignificant changes to a message requires many record updates, even though the change does not require a change in translation. As for the URL, I prefer to have language in the URL. Many users are multilingual and don't trust the translations to their language. So they will visit the source language to confirm. If you use a cookie or some hidden language identifier, they cannot easily switch between languages. Granted their should be a way to move back and forth between translations of the same page, but as this often is not implemented, making it clear in the url, gives the (more computer-literate) users a way to do so. language-Cookies or forcing the user to go to the home page, change language and walk the tree to see a page in another language discourages users from using the site. Cookies are useful for defining default behavior not for controlling the language continually. tex > -----Original Message----- > From: Allan Kolk [mailto:[EMAIL PROTECTED] > Sent: Friday, April 08, 2005 1:37 AM > Cc: Denis Gerasimov; PHP Internationalization List > Subject: Re: [PHP-I18N] Multilingual Web application - how to? > > > > Jochem Maas wrote: > > >> 1. Storing multilingual data in a database. Possible solutions I > >> know: a. many tables, one per each supported language, > e.g. news_en, > >> news_de. b. one table having many columns with translations, e.g. > >> (id, date, text_en, > >> text_de) > >> We use MySQL 4.1 as a back-end. > > what about 1 table, 3 columns: > > translatekey (varchar) - string to translate > > lang - lang code/id/name > > translation (varchar/text) - translated string > > I have used c) for a long time but as of recently I've become a > supporter of b). Reasoning: firstly, select-s are fastest > this way due > to lower number of joins needed and secondly, MySQL as of 4.1 now > supports different collations for different columns. I use > Unicode for > all the data but sorting order and such still depends on > collation used. > > >> 2. Multilingual HTML templates, possible solutions: > >> a. one generic template for everything, one per each > language, like > >> contents_en.tpl.html, contents_de.tpl.html b. many localized > >> templates for each page, e.g news_en.tpl.html, news_de.tpl.html > >> Template engine is Smarty. > > using Smarty I sometimes assign an assoc array of > translated strings: > > $Lang['yes'] = 'yessiree'; $Lang['no'] = 'nocando'; > > then in Smarty: > > <div class="LangExample"> > > 'yes' is {$Lang.yes}, > > 'no' is {$Lang.no} > > </div> > > I completely agree with Jochem there. Unless your base layout is > different for each language involved, you should use common templates > for all languages and have localized texts displayed via > {$lang.yes} and > similar. Clean and swift. And you can have your localized > texts stored > in whatever format you wish - database, XML or PHP variable files. > > >> 3. Storing current language variable, possible solutions: > >> a. inside the URL like /en/news/ > > best in terms of SEO. > > And therefore it is best used on informative sites that are mainly > visited via public side. Keeping the current language inside the URL > means that the customers can easily copy-paste links while > keeping the > language selected. If the site is intended for internal use > (intranet, > CRM, ERP etc) you might opt for storing the language in a > session instead. > > >> b. using cookies > > handy for presistence. > > I would personally use it as an addition to a) and c). Upon the first > visit to a site (session getting set up) the language is > retrieved from > the cookie. Later on either session or URL are used. > > >> c. using sessions > > provides cleaner URLS. > > And I would use this solution on sites intended for internal use, as > stated above. > > > HTH, > Allan Kolk > > -- > PHP Internationalization Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > -- PHP Internationalization Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php