Andrew Ward wrote:
> As I said, not all organisations were asked the same questions so I can't
> just put the data in directly
>
> The only way I can see of dealing with this is to create tables like
> ID,QUESTION,RESPONSE
> 1,"YEAR",2001
> 1,"SEX",1
>
> This doesn't strike me as very smart. I would greatly value anyone's advice
> on dealing with data that is similar in many respects but different in
> others. Thank you very much.
Actually it is not that bad of an idea esp. since you have different
questions. You might make two tables: one to hold common information and the
other to hold the questions, e.g:
CREATE TABLE header ( question_id integer, year integer, age integer, sex
char, UNIQUE( question_id));
CREATE TABLE questions ( question_id integer, question char(32), response_str
char(32), response_int integer, KEY( question_id ));
The whole response_str/response_int thing is only if you have lots of
questions and do not want to calculate stats on cast strings. If your
responses are always ints then, obviously, you would not need the _str/_int
thing either.
With a design like this you can start to get stats in a clean way, e.g.: How
may male respondents questioned in and after 2000 liked beer?
SELECT COUNT(*) FROM header, questions WHERE
header.question_id=questions.question_id AND year>=2000 AND sex='M' AND
question="LIKES BEER" AND response_str="Y";
You can count me as one. ;-)
--Bill
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
