MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:October 19 2005 8:34pm
Subject:Re: Turning tables on their side
View as plain text  
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:
> To unsubscribe: 
> unsub=brent@stripped

Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

Turning tables on their sideJeffrey Goldberg19 Oct
  • Re: Turning tables on their sideBrent Baisley19 Oct
    • Re: Turning tables on their sideJeffrey Goldberg19 Oct
    • Re: Turning tables on their sidesheeri kritzer19 Oct
      • Re: Turning tables on their sideJeffrey Goldberg20 Oct
RE: Turning tables on their sideJon Frisby19 Oct
RE: Turning tables on their sideJon Frisby19 Oct
  • Re: Turning tables on their sideJeffrey Goldberg20 Oct