Let's say you have a database of movies. The MySQL sakila database is
such a pubicly available sample schema.

In that database, there is a film table (
http://dev.mysql.com/doc/sakila/en/s...re-tables-film
)

Two of the columns in that table are title and description, defined as
VARCHAR(255) and VARCHAR(65535) respectively.

But obviously that only allows for title and description in one
language.

What approach would you use to 'scale' these fields to any number of
arbitrary languages?

metaperl, 31.03.2010 17:09:

film_title (film_id, language_id, localized_title, localized_description)

You might want to keep the title column in the film table to record the original title under which it was released in the originating country.

Regards
Thomas

On Wed, 31 Mar 2010 17:14:27 +0200, Thomas Kellerer
<OTPXDAJCSJVU (AT) spammotel (DOT) com> wrote:

I would go with Thomas's approach. Some additional
complications:


Consider character sets. Consider having to represent more than
one character set in your database.

There could be more than one title in a language.

At least one of the Harry Potter books has more than one title.
(UK and US versions)

"Il buono, il brutto, il cattivo" is usually known in English as
"The Good, the Bad, and the Ugly", but it is also known by the literal
translation of "The Good, the Ugly, the Bad".

That assumes that it was released there first. That is probably
the case but need not be so.

Sincerely,

Gene Wirchenko

On Mar 31, 3:15*pm, Gene Wirchenko <ge... (AT) ocis (DOT) net> wrote:


Well, I would imagine you would just go with a universal character
encoding, like utf8 or something.

On Wed, 31 Mar 2010 12:22:27 -0700 (PDT), "metaperl.santmat (AT) gmail (DOT) com"
<metaperl.santmat (AT) gmail (DOT) com> wrote:

Some DBMSs do not support it.

Sincerely,

Gene Wirchenko

Gene Wirchenko wrote on 31.03.2010 23:31:
Seriously? Which one?

On Thu, 01 Apr 2010 00:10:44 +0200, Thomas Kellerer
<OTPXDAJCSJVU (AT) spammotel (DOT) com> wrote:

I do not know the full list. Microsoft Visual FoxPro is one.
Microsoft strangled VFP. AIUI, VFP can handle different character
sets but has limited support for non-8-bit character sets.

VFP is a good mid-level DBMS, but this character set limitation
might be a dealbreaker for some. I have never had to do work with
other character sets, and people have worked around some of the
limitations. I do not know the details.

Similarly, VB6 has limitations with its built-in controls not
handling wider character sets.

Sincerely,

Gene Wirchenko

On Wed, 31 Mar 2010, Gene Wirchenko wrote:

You'd usually use locale rather than language, a locale being a language
qualified with a country. For instance, English is a language, but British
English is a locale. It can be further qualified with a variant, but the
only example i come across is Norwegian, where Norwegian is written (in
Norway) in two orthographies, Bokmal and Nynorsk. Although i have been
told (by a Dane - probably unreliably) nobody actually uses Nynorsk.

The (de facto, i think) standard encoding for locales combines ISO-639 for
the languages and ISO-3166 for the countries, joined with an underscore:

en_GB proper English
en_US colonial English
fr_CA Quebecois
no_NO Bokmal Norwegian
no_NO_NY Nynorsk Norwegian

And just to mix it up a bit:

nb_NO also Bokmal Norwegian!
nn_NO also Nynorsk Norwegian!

I don't know if nb and nn are properly standard, though. They are
definitely a mistake.

Anyway, those strings are unique, and small enough that they make
practical keys. Encode them as a CHAR(4), skipping the underscore, and
they're no bigger than an int. If you can forget about variants, anyway.

tom

--
The idiots are winning.