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?
[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
