ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Henrique Ferreira e Silva <dhsi...@gmail.com>
Subject Re: OT: N+1 Best Practice
Date Fri, 21 Oct 2005 11:06:43 GMT
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 <zoran@sparecreative.com> 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              |
> +-------------+-------------+-------------+
>
>
>

Mime
View raw message