Ben A. Hilleli wrote:
Obviously this poses a problem for 'normal' relational-databases
No, it just takes a little indirection to represent this in a properly
normalized fashion. What you need is:
1. A table of language names mapped to IDs:
Languages
INTEGER id AUTO_INCREMENT UNIQUE
VARCHAR name UNIQUE
PRIMARY_KEY(id)
You could add a "sequence" column to this if you'd like to be able to
reorder the list for presentation.
2. A table of users with many attributes, one of which is their
preferred language:
Users
INTEGER id AUTO_INCREMENT UNIQUE
VARCHAR name
... etc ...
INTEGER language_id
PRIMARY_KEY(id)
3. A table of questions, multiply translated into the languages you
need, each of which has a unique ID, what language it is in, the
question text, and which table contains the answers for this question:
Questions
INTEGER id
INTEGER language_id
VARCHAR question
VARCHAR answer_table
PRIMARY_KEY(id, language)
The first two columns are a composite primary key, with the question ID
being the same for all questions with the same meaning. So, the table
will have N rows for the user's occupation question, where N is the
number of rows in the Languages table. All N rows will have the same
'id' value, but different 'language' values. Therefore, you can't use
an AUTO_INCREMENT column here, so it'll be up to your code to add new
values for this.
4. A table that lets you order the questions, so you don't have to do
something like renumber all the question IDs to reorder the questions:
QuestionOrder
INTEGER question_id
INTEGER sequence
5. For each question, a list of answers, each translated into the same N
langauges as you did for the questions:
Occupations
INTEGER id AUTO_INCREMENT
INTEGER language_id
VARCHAR name
PRIMARY_KEY(id, language)
6. For each table of question answers, a sequence table:
OccupationOrder:
INTEGER occupation_id
INTEGER sequence
7. A table to hold a mapping of questions to answers for each user:
Answers
INTEGER user_id
INTEGER question_id
INTEGER answer_id
Note that the answer doesn't say which language the user used when
answering the question. This table is only concerned with meaning, not
presentation.
A few of the tables have no key field. You could declare all columns in
these tables as a single composite key, if you like. Only the whole
record is unique in these tables.
The query order thus looks like this:
SELECT * FROM QuestionOrder ORDER BY sequence
SELECT * FROM Questions WHERE id = QUESTION_ID_FROM_ORDER_TABLE AND
language_id = USERS_PREFERRED_LANGUAGE
SELECT * FROM ANSWER_TABLEOrder ORDER BY sequence
SELECT * FROM ANSWER_TABLE WHERE id = ANWSER_ID_FROM_ORDER_TABLE AND
language_id = USERS_PREFERRED_LANGUAGE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
