SEO

Hello,
I'm not very experienced with MySql and I'm building a website that deal with user profiles. User profiles involves lists of preferences to choose from (for example, your contry, your profession, etc.), so the user chooses from a drop-down lists. The most natural solution for this is to define the related fields sometimes with an ENUM statement, sometimes with a SET statement.
And now, the problem comes... the problem is that my site will be available in several languages, so it will have several versions, for example, www.englishversion.com, www.spanishversion.com, etc. Obviously I will not create a different database for each site with the same data in different languages.
I could define many fields in the same table, suppose in my table Profile, I would put fields like country_french_enum, country_english_enum and so on. Each one would contain a list of all countries in french, in english, etc. But I feel it a little cumbersome and difficult to work with.
Another solution whould consist to not define enums or sets at all. Instead I could just define tha country field as varchar in the Profile table and create a new table with static data, with the following fields or such:
table name: country_names
table fields: (country_id, country_name_en, country_name_fr, country_name_es, country_name_it, ...)
So each time a form with a country drop-down list is loaded, it would load the country names from country_name table according to the language of the site. And when form is submitted, the choosen value would be validated and inserted in the country varchar field of profile table.
This gives me the advantage of take my profile table simpler and faster. But the inconvenient is that now, I have much more field validations to do, I must validate each submitted choice, because a varchar field doesn't limit or delimit allowed values as ENUM or SET do. If I get speed with the database, I actually loses speed with the website or server load. If at one side I simplify things, I complicate them in another.
So, I wrote this message to get some help and knowing more how these kind of issues are solved in real-world situations.
Internet has lots of multilanguages database driven websites with similar data issues. Surely some standard solution is applied. Do you know any better solution than these ones I described? If not, cound you suggest me which is the best of the two?
Thanks, I would appreciate a lot your help.

Alphonse


---------------------------------
Want to start your own business? Learn how on Yahoo! Small Business.

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@dutra.fastmail.fm