List:General Discussion« Previous MessageNext Message »
From:Leandro GuimarĂ£es Faria Corcete DUTRA Date:November 8 2006 11:08am
Subject: Re: Multilanguage database design issue
View as plain text  
Em Sat, 04 Nov 2006 21:04:02 -0800, Alphonse Langueduc escreveu:

> The most natural solution for this is to
> define the related fields sometimes with an ENUM statement, sometimes with
> a SET statement.

	Not actually, because these are proprietary constructs.  The standard
would be to define DOMAINs with CHECK constraints, but neither is available in
MySQL.



> Do you know any better solution than these ones I described?

       	 Easy enough.  You must have two tables, one for territories and other
for languages.  You can use the ISO codes as primary keys for them.  Then, you
can have a m:n relationship table between them, defining dialects and having
both tables’ primary keys as a composed primary key, each part of it being a
foreign key to the first two tables.

	Then, each string in your application interface (it doesn’t matter if it
is client-server, web or whatever) will be stored in the database in four
fields: string identificator (may be the English string or a surrogate key),
dialect (country + territory), and the string itself, in the relevant dialect.

	I should have such an example here somewhere, if you need it.


-- 
Leandro Guimarães Faria Corcete DUTRA     +55 (11) 9406 7191 (cel)
Administrador de (Bases de) Dados         +55 (11) 2122 0302 (com)
http://br.geocities.com./lgcdutra/        +55 (11) 5685 2219 (res)
BRASIL                            mailto:leandro@stripped

Thread
Multilanguage database design issueAlphonse Langueduc5 Nov
  • Re: Multilanguage database design issueLeandro GuimarĂ£es Faria Corcete DUTRA8 Nov