Re: custom crosstab question

On 4/26/07, Joe Conway <mail(at)joeconway(dot)com> wrote:
> Michael Swierczek wrote:
> > On 4/25/07, Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com> wrote:
> >> Joe,
> >> That's exactly what I needed. I completely missed that it would
> >> be possible with the tablefunc/crosstab. Since you're the main
> >> (only?) name I see associated with that code, I'm sure you would know.
> >>
> >> -Mike
> >>
> >
> > I spoke too soon, there's an additional factor at play that
> > complicates things.
> > Most of the questions take a single answer, but the multiple select
> > questions can have several answers. So for a regular question with
> > code 'drug', a given survey_event can have 0 or 1 entries in the
> > answer table with question_code 'drug'. For a multiple select
> > question like, 'health', 0-7 entries are possible in the answer table,
> > and we want them exported as columns 'health0', 'health1', 'health2'
> > through 'health7' and each column populated according to whether that
> > section of the multiple select was chosen.
>
> It seems to me that if you really want health0 and health1 tracked
> independently, they should each be considered separate questions. Then
> the existing code would "just work". Perhaps if it is important to tie
> those "healthN" questions together, you could have some other attribute
> for questions that allows grouping. So, for example, grp_id = 0 means
> this question is "stand alone" and grp_id > 0 is a grouped question.
> Something like:

The software has been in production for a few years, and although I
have the ability and authority to retrofit the existing data, I'm
nervous about doing so. A number of seemingly innocent tweaks in the
past caused major headaches further along.

But it's definitely worth considering.
-Mike

>
> create table survey_question_grps (
> grp_id int,
> grp_name text
> );
>
> insert into survey_question_grps values
> (0, 'stand alone question'),
> (1, 'health');
>
> create table survey_question (
> id int,
> survey_id int,
> question_code text,
> grp_id int,
> question_order int
> );
>
> insert into survey_question values
> (1,1,'drug',0,1),
> (2,1,'marijuana',0,2),
> (3,1,'sick',0,3),
> (4,1,'health1',1,4),
> (5,1,'health2',1,5);
>
> Joe
>