Hi MySQL maestros,

I'm setting up a table containing artistic terms in a number of
languages. Here is a brief extract:


ID UK English French Italian

257 gouache gouache gouache
436 portrait portrait ritratto
575 watercolour aquarelle acquarello


As you can see, certain words are the same in several different
languages. Words like "portrait" are considered native to both
English and French. However "gouache" may be considered a term
borrowed from French, for which there is no real translation. Other
terms are different in every language.

If a term is missing, a user will be able to add it in his or her own
language. The term should then appear, in the language it was
originally entered, for searches in other languages. Suppose I add
the French word "galerie". When you do a search of English terms, you
should now see something like this:

613 galerie
257 gouache
436 portrait
575 watercolour

An English speaker may be quite happy to see "gouache" (especially if
it is displayed in italics), but would prefer to see "gallery" in
English. My client application will allow you to enter a translation
for the word. If you do so, the next time you run the search, you
should see:

613 gallery
257 gouache
436 portrait
575 watercolour

Until it is translated into Italian, an Italian should still see:

613 galerie
257 gouache
436 ritratto
575 acquarello


My questions are:
1) How should I structure the Terms table?
2) What SQL SELECT query should I use to return the localized term
if it exists, or the original term in a foreign language if the
localized term does not yet exist?


My current answer to the first question is:

CREATE TABLE `Term` (
`id` smallint(5) unsigned NOT NULL,
`languageID` tinyint(3) unsigned NOT NULL,
`string` varchar(63) NOT NULL,
`translated` tinyint(3) NOT NULL default '0'
);

`languageID` refers to the id field in a separate Language table.
This structure allows me to have multiple records with the same
Term.id but different languageIDs. `translated` will be 1 (true) if
the term was created in the current language, or if a translation was
subsequently supplied.

However, this means that when a new term is created in one language, I
have to copy it across to all the other languages. For a word like
"watercolour", this is not an issue, since we will need a separate
term in each language eventually. For words like "gouache" it is
wasteful.

I would rather not have duplicate entries where the same word is
attributed to different languages.

How should I structure the Term table and the SELECT statement so that
duplicate entries are not necessary?

The optimal solution would be one that distinguishes between:
* a term (like "gouache" in English) for which there is no translation
* a term (like "galerie" in my example above) for which the translation
has yet been provided
* a term (like "portrait" in French and English) which is considered
native in more than one language.

Thanks in advance,

James

On Sun, Mar 16, 2008 at 8:16 AM, James Newton
<james.newton@openspark.com> wrote:
> Hi MySQL maestros,
>
> I'm setting up a table containing artistic terms in a number of
> languages. Here is a brief extract:
>
>
> ID UK English French Italian
>
> 257 gouache gouache gouache
> 436 portrait portrait ritratto
> 575 watercolour aquarelle acquarello
>
>
> As you can see, certain words are the same in several different
> languages. Words like "portrait" are considered native to both
> English and French. However "gouache" may be considered a term
> borrowed from French, for which there is no real translation. Other
> terms are different in every language.
>
> If a term is missing, a user will be able to add it in his or her own
> language. The term should then appear, in the language it was
> originally entered, for searches in other languages. Suppose I add
> the French word "galerie". When you do a search of English terms, you
> should now see something like this:
>
> 613 galerie
> 257 gouache
> 436 portrait
> 575 watercolour
>
> An English speaker may be quite happy to see "gouache" (especially if
> it is displayed in italics), but would prefer to see "gallery" in
> English. My client application will allow you to enter a translation
> for the word. If you do so, the next time you run the search, you
> should see:
>
> 613 gallery
> 257 gouache
> 436 portrait
> 575 watercolour
>
> Until it is translated into Italian, an Italian should still see:
>
> 613 galerie
> 257 gouache
> 436 ritratto
> 575 acquarello
>
>
> My questions are:
> 1) How should I structure the Terms table?
> 2) What SQL SELECT query should I use to return the localized term
> if it exists, or the original term in a foreign language if the
> localized term does not yet exist?
>
>
> My current answer to the first question is:
>
> CREATE TABLE `Term` (
> `id` smallint(5) unsigned NOT NULL,
> `languageID` tinyint(3) unsigned NOT NULL,
> `string` varchar(63) NOT NULL,
> `translated` tinyint(3) NOT NULL default '0'
> );
>
> `languageID` refers to the id field in a separate Language table.
> This structure allows me to have multiple records with the same
> Term.id but different languageIDs. `translated` will be 1 (true) if
> the term was created in the current language, or if a translation was
> subsequently supplied.
>
> However, this means that when a new term is created in one language, I
> have to copy it across to all the other languages. For a word like
> "watercolour", this is not an issue, since we will need a separate
> term in each language eventually. For words like "gouache" it is
> wasteful.
>
> I would rather not have duplicate entries where the same word is
> attributed to different languages.
>
> How should I structure the Term table and the SELECT statement so that
> duplicate entries are not necessary?
>
> The optimal solution would be one that distinguishes between:
> * a term (like "gouache" in English) for which there is no translation
> * a term (like "galerie" in my example above) for which the translation
> has yet been provided
> * a term (like "portrait" in French and English) which is considered
> native in more than one language.
>
> Thanks in advance,
>
> James

Perhaps something like:
CREATE TABLE terms (
term_id INT AUTO_INCREMENT PRIMARY KEY,
default_lang INT
);

CREATE TABLE terms_rel (
term_id INT,
lang_id INT,
td_id INT
);

CREATE TABLE terms_def (
td_id INT AUTO_INCREMENT PRIMARY KEY,
td_value varchar(63)
);

The general idea being that each term which has multiple
representation has an entry in the terms table. When you add a term
you first add an entry to the term_def table with new representation
(for example watercolour), and then add an entry into the terms_rel
table connection all three tables. If you want to use the term_def for
multiple languages you just add another entry into the term_rel
talble. If there is no translation you could add a null or link to an
entry that says "does not exist". With the in default_lang you could
determine if it is a translation.

The queries would pretty easy to get what you would want, assuming you
know how to write JOINs.

Does this meet your needs?

--
Rob Wultsch