Re: Need some hints for a multi-language (mysql) site

I've seen it done several ways. I can't say which is best because I've never implemented a database for a company that needed multiple language descriptions.

(1) Create a separate table for each table that has globalized column and define its primary key to include all of the columns of the original table's primary key and a language identifier. Of course, this requires joins for every query that includes a globalized column.

(2) Embed the primary descriptions within your tables to speed up access, and create a separate table to contain alternate translations. "Primary" refers to the language that is most often requested and serves as the default, for example COALESCE(translated_column, primary_column) with a LEFT OUTER JOIN. This solution can improve overall performance, but that requires alternate code when translation is not needed.

(3) Create a separate table that includes all descriptive text and includes as its key the table id, column id, surrogate key value, and language id. In Microsoft SQL Server 2000, this table can be pinned in memory to boost performance. I don't know if mysql has that feature. I've seen this done once, but I personally don't think it's a good design.

"Martien van Wanrooij" <info_removethis_at_martienvanwanrooij.nl> wrote in message news:ZHYSf.7731$zc1.330_at_amstwist00...
> Hello all,
>
> I have been looking around at Google without too much success because I
> need some tips for a website I am making now. It is for a travel agency
> and the core of the website are, of course, the accomodations that can be
> rent. A table could have fields like an accomodation_id, the accommodation
> name(= page title), maximum number of persons, a picture_id that refers to
> another table with pictures but I am a little bit puzzled about the
> descriptions that come in several languages. I think it is NOT a good id
> to make fields like description_french, description_german,
> description_english etc, I guess there should be something like a table of
> descriptions that contains fields like the description itself, a
> language_id and an accomodation_id. However there are not only
> accomodations, the more general info pages will be also translated in all
> the available languages, they "behave" much the same like the accomodation
> pages as for the title and the pictures that are shown. Anyway some tips
> would be welcome, I am aware that there must be some info about it
> "somewhere" on the web but as I said probably I didn't "google" for the
> right keywords. FYI, the site is made with php and mysql but obviously my
> question refers to the "thinking" I have to do before starting to write
> the code.
> Thanks for any help
>
> Martien.
>
Received on Sun Mar 19 2006 - 12:59:20 CET