Re: Good structure for diversified data
Thanks for the reply. I think you missed the whole point of splitting the tables.
I tried to reply twice but each time my whole reply got lost because the form somehow went back and all got expired and flushed. So, I'm just giving a (simplified) query I've come up with that satisfies all my needs. If you think you can add any insight or improve it with the scarce data presented here I'd be grateful if you do. Otherwise just forget about it ;-)
I want to split one existing table in two because I do not want to add 30 fields each time I add a language-specific field and umpteen other fields if I add another language. So, I split in a table with base data (_Stamm) and another table that has (for instance) 30 records per base record in the base table (_Lang). My question was if that makes sense or if there is a better way.
To make it more complex I also have to cater for Views that have an n:M relation to the base records. And to make if even further complicated I have three types of records that are hierarchically ordered and currently belong to three tables (in the future they belong to 3x the number of tables I split into): categories, questions, answers (e.g. there are umpteen categories in several levels where each category may have 10 or 20 questions and each question may have 5 or 10 answers - or in a few cases: none).
My original question contained only the categories (Module_). In my second edit in my original question I list a query which would satisfy my needs. Is there a better way to make this query (without knowing more)?
Now I'm at the point of moving this over from categories to questions and answers. Where answers are related to questions, so more joins/relations come into play. I've come up with this query.
SELECT MAX(IF(ql.LangID = 'de', ql.LF, NULL)) AS question1,
MAX(IF(ql.LangID = 'en', ql.LF, NULL)) AS question2,
MAX(IF(al.LangID = 'de', al.LF, NULL)) AS answer1,
MAX(IF(al.LangID = 'en', al.LF, NULL)) AS answer2,
<lots of other fields>
FROM Questions_Relations qr
LEFT JOIN Questions_Stamm qs ON qr.QuestionID = qs.QuestionID
LEFT JOIN Questions_Lang ql ON qs.QuestionID = ql.QuestionID
LEFT JOIN Answers_Relations ar ON qr.QuestionID = ar.ParentID
LEFT JOIN Answers_Stamm ans ON ar.AnswerID = ans.AnswerID
LEFT JOIN Answers_Lang al ON ans.AnswerID = al.AnswerID
WHERE ( <lots of WHERE clauses> )
GROUP BY qr.QuestionID, ar.AnswerID
ORDER BY qr.QuestionOrder, ar.AnswerOrder
The output from the six tables joined together contains one question and one answer per record, in two languages (so, one question may appear in multiple records because typically there are multiple answers per question). Adding to the original query for modules I had to add grouping by qr.QuestionID, because there are questions that don't have answers. In these cases the AnswerID was NULL and grouping managed to group questions and answers together that didn't belong to each other, just because of the NULL value.