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