Hi Zoran, Usually i try to follow all normalization rules when designing a database data model. But sometimes, normalizing is not the best solution in real world.
In your very specific situation i'd do as follows: Survey ---------< Questions -----------<Answers (a list of the possible answers for each question) And then a table that is a 4-tuple relation table that will store the answers a user has submitted: Submitted_Answers ---------------------------- user_id survey_id question_id answer_id This table is not exactly what theory says us to do and i'm sure it's not the most elegant solution, but it will make your queries pretty simple and will deliver you a good performance. Your requirements are your constraint! Cheers, Daniel Silva. On 10/21/05, Zoran Avtarovski <[EMAIL PROTECTED]> wrote: > I have a pretty straight forward situation: > > In a html form we ask a number of questions which have check box answers. > When the form is submitted we used to save the responses for each question > as a comma delimited string of the results in a table with a unique > identifier and string fields, as illustrated by the simple sample here: > > +-------------+-------------+-------------+-------------+---------------+ > | survey_id | foods_1 | foods_2 | foods_3 | foods_4 | > +-------------+-------------+-------------+-------------+---------------+ > | 1 | 1,5,6,7 | 2,3,8 | 1,4,9,10 | 3,4,5,6 | > | 2 | 1,5,6,7 | 2,3,8 | 1,4,9,10 | 3,4,5,6 | > | 3 | 1,5,6,7 | 2,3,8 | 1,4,9,10 | 3,4,5,6 | > +-------------+-------------+-------------+-------------+---------------+ > > This was fine for simple stuff but we now have more complex needs and have > to move to a parent-child table structure. > > I'm not a SQL expert and I'm trying to maximise the efficiency of what I'm > doing. As I see it, I have two options. One is two have a separate child > table for question and then use a SQL join and the standard n+1 approach > listed on the Wiki or two, I could use the setup below. What I'm looking for > is a suggestion as to the best way to achieve this. > > > I was thinking that I could generate a unique incrementing id for each > response which I then link in a child table as illustrated below. But the > problem I see is that I will have to perform multiple child queries for each > each parent row. Can somebody please suggest a better way. > > The survey table: > +-------------+-------------+-------------+-------------+---------------+ > | survey_id | foods_1 | foods_2 | foods_3 | foods_4 | > +-------------+-------------+-------------+-------------+---------------+ > | 1 | 1 | 2 | 3 |4 > | 2 | 5 | 6 | 7 |8 > | 3 | 9 | 10 | 11 |12 > +-------------+-------------+-------------+-------------+---------------+ > > The food_response table: > +-------------+-------------+-------------+ > | resp_id | q_id | food | > +-------------+-------------+-------------+ > | 1 | 1 | 1 | > | 2 | 1 | 5 | > | 3 | 1 | 6 | > | 4 | 1 | 7 | > | 5 | 2 | 2 | > | 6 | 2 | 3 | > | 7 | 2 | 8 | > | 8 | 3 | 1 | > | 9 | 3 | 4 | > | 10 | 3 | 9 | > | 11 | 3 | 10 | > | 12 | 4 | 3 | > | 13 | 4 | 4 | > | 14 | 4 | 5 | > | 15 | 4 | 6 | > | 16 | 5 | 1 | > | 17 | 5 | 5 | > | 18 | 5 | 6 | > | 19 | 5 | 7 | > +-------------+-------------+-------------+ > > >
