ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Zoran Avtarovski <zo...@sparecreative.com>
Subject OT: N+1 Best Practice
Date Fri, 21 Oct 2005 03:54:10 GMT
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