Multilingual Dynamic Website Architecture

[pablowerk]

I have developed a multilingual website, currently available in German and English, and I was curious how other people have set up their multilingual dynamic website.
(www.optek.com if you want to check it out)

Here is the gist of my site’s architecture, languages, how it works, etc.
Technology used: ASP and MS SQL 2000

When I created the site the requirements where simply:
1. The site must be able to be translated into many languages.
2. People translating the site will not have any knowledge in HTML code

I created a DB structure that basically breaks the content from each page into small tidbits, to circumvent any HTML editing. At the heart of my DB I have a Content table that has the following fields:
1. Language ID
2. Page ID
3. Item Number
4. Content

This table allows me to store 90% of all my content for my website. Using a custom 404 page I create a virtual directory for each language. This allows me to only have to manage 1 file per page for all languages.

For example mysite.com/about.asp and mysite.com/de/about.asp use the same about.asp file, but my 404 page uses the /de/ in the URL to set the language to German. The about.asp file then queries the DB looking for all the content for its page in German and sorts the content by its item number.

So far this system has worked out great. Now, there is a need to start adding Russian text. Ultimately it would be great if I could continue to use only 1 DB and 1 set of files, but so far I haven’t been able to properly store the Russian characters in the DB. When I do pull them out, I run into conflicts with the German character set.

So I am looking for how other people have setup their Multilingual Dynamic Website to determine if there is a better way of organizing my multilingual content.

[lpoulsen]

One of the "delightful" complications of multi-lingual text handling is the interplay between the many ways of representing character sets. It gets especially "interesting" because Microsoft products have tended to implement ad-hoc non-standard solutions, ignoring the standards that everyone else has worked out. (Think of "codepages" and the tendency to use numeric encodings for special characters instead of the character names defined for use in HTML.)

Working in a US-English-only environment, you get to ignore all of this and take advantage of the fact that the 7-bit USASCII core character set is a subset of most of the expanded character sets.

If you use multiple "Western" languages, you will typically be using the ISO-8859/?? character sets. For most west European countries, the preferred set is now ISO-8859/15, which contains the codes for German as well as Finnish and French. For Russia, you need ISO-8859/5 which covers the basic latin alphabet along with the cyrillic alphabet.

If you need to cover Russian and German at the same time, or if you need to support Asian languages or Arabic, you will need to use UNICODE.

If you did not design for unicode from the beginning, or if your content management system does not support Unicode, you will have a lot of challenges ahead.

[trancehead]

If you are going to be using different character sets (i.e. east European, etc) you need to change the codepage to 65001 (UTF-.

We created a directory for each language and then used include files for all the bits that stayed the same between the pages. This also allows us to give them individual titles, meta tags, etc.

To handle the language issue we created an XML file of phrases that were translated into the site in each include page. We only have about 100 phrases though so I don't know if this would scale that well.

[bockereyer]

I would suggest for your next website you take a look at the open source CMS Drupal. It has multi-language capabilities build in right from the start. I must warn you the learning curve is somewhat steep but it's worth the effort. Maybe you can look around at drupal.org | Community plumbing and see how they dealt with the problem. The next version, Drupal 6, will be full multi-language from core if I'm correctly informed.

[venividi]

Originally Posted by pablowerk

<snip>
Now, there is a need to start adding Russian text. Ultimately it would be great if I could continue to use only 1 DB and 1 set of files, but so far I haven’t been able to properly store the Russian characters in the DB. When I do pull them out, I run into conflicts with the German character set.

So I am looking for how other people have setup their Multilingual Dynamic Website to determine if there is a better way of organizing my multilingual content.

Almost all my sites are multilingual, but really multi (up to 12 languages). As a former translator I have the advantage of being able to adress most problems regarding Est European and Arabic languages. First of all the fact thjat you've extracted the text from the html is, in my opinion, the best way, even if there are special softwares (CATs) for translating HTML.

I do this myself (too long to explain at the moment, but I'll give you all the details if you want) automatically from the files in the original language, in my case Italian. Extraction also provides a three-column Word file (translators usually prefer using Word), with a code number and the Italian bit of text, the third column is empty for the translation.

Once I get my file back I am able to "reintegrate" the translated text into the html file and that's that.

I used to create the files online dynamically, but since I encountered some security problems, which would have required a lot of time (and experience) to solve, I decided to do the conversion on my computer then upload. The whole operation takes less than five percent more time which is a good price for real html files.

I use PHP, MYSql and 1. Arachnophilia by Paul Lutus (Arachnoid.com ), Word plus a cpouple of macros, and
2. Caterpillar, an excellent text extractor by Antenna - make your own web site - site creation - web design software - free trial (They also have Catscradle, a localization software for websites, and Antenna Studio)
3. MS Word

To avoid problems with Russian and other 2-byte languages, I have my Translation Agency in Hungary (Logon. Logon Hungarian Translations) convert the "difficult" characters to Unicode. A couple of converter softwares are also available.

Should you need more details please ask

Regards

Valeria