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
