The person you inherited from formatted the data correctly in my
opinion. With the existing format, you can index all the data with a
minimum number of indexes and quickly compile results. It can scale
to any number of questions without having to modify the underlying
data structure. It can also easily answer queries like, who missed
one or more questions?
What you are trying to do is store the data as you see it, which is
rarely a normalized data model. Your presentation layer should handle
the formatting for the user. The model you are envisioning would also
be difficult to query to determine missed questions.
I would use the presentation layer (i.e. Perl, PHP, Python, Ruby,
Java, etc) to "pivot" the data for display. That's where you also add
things like coloring to highlight errors or interesting information.
On Oct 19, 2005, at 3:24 PM, Jeffrey Goldberg wrote:
> I suspect that this is the wrong list for this kind of question,
> but if someone could point me to appropriate sources, I would very
> much appreciate it.
>
> I am new to SQL but inherited project designed by someone who
> doesn't seem answer his email anymore.
>
> Essentially date were collected on the web using PHP inserting
> things into a MySQL data base. It took me time, but I now have a
> handle on what is in which of the 15 tables involved.
>
> Each response to each question by each respondent produced its own
> record (row). That is, I have something like
>
>
> respondent_id question_id answer_id answer_text
> ----------------------------------------------------
>
> 23 201 56 NULL
> 23 202 20 NULL
> 23 203 1 NULL
> 23 204 NULL Arlington
> 24 201 52 NULL
> 24 202 21 NULL
> 24 203 0 NULL
> 24 204 NULL Richmond
>
>
> and so on for other respondent_ids as well.
>
> What I would like to get for my users is something that looks like
>
>
> respondent_id q201 q202
> q203 ...
>
> ----------------------------------------------------------------------
> -
> 23 text-for-ans56 text-for-ans20 text-for-
> answer1 ...
> 24 text-for-ans52 text-for-ans21 text-for-
> answer0 ...
>
>
> So instead of having a record for each response, I'd like to have a
> single record for each respondent that shows all of that
> respondents responses.
>
> For someone who knows SQL this should be easy. I suspect that a
>
> group by respondent_id
>
> clause will play a role, but I just don't see it.
>
> As I said, references to books or sites that I should learn from
> would also be welcome.
>
> -j
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=brent@stripped
>
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
