DB design question - survey system

I've written several survey systems in which the majority of the questions
have the same or similar responses (Yes/No, True/False, scale of 1 - 5,
etc).

But this latest survey system I'm working on has 8-10 sections, with a
variety of question attributes and answer scales. Some items have just a
description and require a Yes/No answer, others have a description and an
active status and require a Yes/No and price answer, some require a comment,
etc.

Rather than build a separate response table for each survey section, I was
thinking of building one generic response table, and trying to force all
sections to fit by adding columns - some of which won't apply to some items.
Like this:

Survey Category (will apply to all items)
Survey Section (will apply to all items)
Item Description (will apply to all items)
Item YN (will apply to all items)
Item Price (will apply to about 10% of the items)
Item Points (will apply to about 10% of the items)
Item Active YN (will apply to about 10% of the items)
Item Fail YN (will apply to about 10% of the items)
Item Comment (will apply to about 10% of the items)

For instance, in the structure above the field "Item YN" would represent
multiple types of answers: is the item in use?, is the item in place?, is
the item given away for free?, is the item on display?, etc. Basically,
anywhere a Yes/No answer is used.

The advantage is one source table (rather than 8) for storing answers, and
it might be easier to query and report on.

The disadvantages I see are 1) it's more difficult to understand the meaning
of the responses when the answer field is named Item YN, and 2) you have a
non-normalized table that's difficult for a 3rd party to understand.

If I have the questions and responses in separate tables, I'll use names
like "ItemComplimentaryYN" and "ItemUsedYN" depending on the question. It's
easier for others to learn the data.

I actually don't like the "generic" approach, and probably won't use it, but
I figured I'd try to get some input from others who've written survey
systems.

Thanks

re: DB design question - survey system

Normalize.

You didn't explain how the survey is being generated, but that they may
not matter. However, if this is a web application that builds dynamic
forms, experience indicates the best solution is to provide a form
builder via an Admin application.

Store the meta-data about your forms and provide an interface for
adding form meta-data via building and modifying forms. Whenever a
form is created or updated, generate the form (asp, aspx, whatever)
using a Generator Service that monitors a queue. The queue gets PUSHes
from the Admin application whenever something triggers a form
regeneration, and the responsibility of the Generation Service is to
POP the queue and generate forms from the meta-data. You also want to
add the feature to generate sets of forms based on the results of a
stored procedure.

I suggest building the queue in SQL Server. Most developers lean
towards MSMQ, however the problem with MSMQ is that it has limited
interfaces - you can't POP and PUSH with Transact SQL. We migrated a
queue that runs 24x7 capturing data from our customers for their sites
from a 3rd party queue product to SQL Server . We have implemented
MSMQ in other areas of our organization, however we are migrating them
to SQL Server.

Our software runs about 5,000 sites and we provide our site
administrators the ability to build forms that capture normalized data.
Within five seconds after an administrator builds or modifies the
form, the new form is on their site (which is on a 15-server farm)
capturing and redirecting normalized data based on business rules for
the recipient(s).

re: DB design question - survey system

Hi

You talk about a single table per section, but I am not sure that that sort
of design would be any different to your suggested one.

You could have a separate table for each type of questions i.e have all Y/N
answers in one table, also Fail/Active/Nothing/Complementary can be a second
attribute therefore you will not have separate columns for the Y/N part.

You may also want to check out using SQL_VARIANT as a data type.
http://msdn.microsoft.com/library/de...asp?frame=true

Full text searching would possibly be a different solution.

John

"DFS" <nospam@nospam.com> wrote in message
news:41Yyd.2693$Zp1.2514@fe07.lga...[color=blue]
> I've written several survey systems in which the majority of the questions
> have the same or similar responses (Yes/No, True/False, scale of 1 - 5,
> etc).
>
> But this latest survey system I'm working on has 8-10 sections, with a
> variety of question attributes and answer scales. Some items have just a
> description and require a Yes/No answer, others have a description and an
> active status and require a Yes/No and price answer, some require a
> comment,
> etc.
>
> Rather than build a separate response table for each survey section, I was
> thinking of building one generic response table, and trying to force all
> sections to fit by adding columns - some of which won't apply to some
> items.
> Like this:
>
> Survey Category (will apply to all items)
> Survey Section (will apply to all items)
> Item Description (will apply to all items)
> Item YN (will apply to all items)
> Item Price (will apply to about 10% of the items)
> Item Points (will apply to about 10% of the items)
> Item Active YN (will apply to about 10% of the items)
> Item Fail YN (will apply to about 10% of the items)
> Item Comment (will apply to about 10% of the items)
>
> For instance, in the structure above the field "Item YN" would represent
> multiple types of answers: is the item in use?, is the item in place?, is
> the item given away for free?, is the item on display?, etc. Basically,
> anywhere a Yes/No answer is used.
>
> The advantage is one source table (rather than 8) for storing answers, and
> it might be easier to query and report on.
>
> The disadvantages I see are 1) it's more difficult to understand the
> meaning
> of the responses when the answer field is named Item YN, and 2) you have a
> non-normalized table that's difficult for a 3rd party to understand.
>
> If I have the questions and responses in separate tables, I'll use names
> like "ItemComplimentaryYN" and "ItemUsedYN" depending on the question.
> It's
> easier for others to learn the data.
>
> I actually don't like the "generic" approach, and probably won't use it,
> but
> I figured I'd try to get some input from others who've written survey
> systems.
>
> Thanks
>
>
>[/color]

re: DB design question - survey system

DFS wrote:[color=blue]
> I've written several survey systems in which the majority of the questions
> have the same or similar responses (Yes/No, True/False, scale of 1 - 5,
> etc).
>
> But this latest survey system I'm working on has 8-10 sections, with a
> variety of question attributes and answer scales. Some items have just a
> description and require a Yes/No answer, others have a description and an
> active status and require a Yes/No and price answer, some require a comment,
> etc.[/color]

Do use one table for answers. I mean for questions. There may be some
frame you need around this table; but I feel, and then I mean my
database intuition, that one table is the best way to get statistics and
reports.

Let me think aloud.

If one question can have an answer in more parts, that troubles the
setup. You mention yes/no and a price answer; but is this the type "No"
or "Yes, namely $32" ? In that case the yes/no part can be made implicit.

What you can use is some tag, indicating the meaning of the answer
value. I see this table before my eye:

Question( [ID,] questionNumber, questionText, answerType, answerTag)

Decide for yourself if the questionnumber would be the primary key, or
you use a separate, non-user-visible key (I prefer the latter).

answerType sits in a small table, containing values like
{"yes/no","number","text"}--much like the DataType of a field. You have
this table, appropriately called AnswerType, one-to-many to the question
table. Btw I name my tables single rather than plural. That's a choice.

answerTag can be any description that differentiates to you what kind of
datum the answer represents.

So far this structure can produce empty survey forms.
Real surveys provide answers; you might store those in

Survey(surveyID)
SurveyAnswer(surveyID, questionNumber, givenAnswer)

where you can derive the properties of givenAnswer from the Question table.

I recommend the generic approach. It enables you to change the survey
setup without data changes, it does take some getting used to as it is a
little more abstract than the straight table but once you get the hang
of it you won't want something else anymore ;-)
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

re: DB design question - survey system

maxl@msn.com wrote:[color=blue]
> Normalize.
>
> You didn't explain how the survey is being generated, but that they
> may not matter. However, if this is a web application that builds
> dynamic forms, experience indicates the best solution is to provide a
> form builder via an Admin application.
>
> Store the meta-data about your forms and provide an interface for
> adding form meta-data via building and modifying forms. Whenever a
> form is created or updated, generate the form (asp, aspx, whatever)
> using a Generator Service that monitors a queue. The queue gets
> PUSHes from the Admin application whenever something triggers a form
> regeneration, and the responsibility of the Generation Service is to
> POP the queue and generate forms from the meta-data. You also want to
> add the feature to generate sets of forms based on the results of a
> stored procedure.
>
> I suggest building the queue in SQL Server. Most developers lean
> towards MSMQ, however the problem with MSMQ is that it has limited
> interfaces - you can't POP and PUSH with Transact SQL. We migrated a
> queue that runs 24x7 capturing data from our customers for their sites
> from a 3rd party queue product to SQL Server . We have implemented
> MSMQ in other areas of our organization, however we are migrating them
> to SQL Server.
>
> Our software runs about 5,000 sites and we provide our site
> administrators the ability to build forms that capture normalized
> data. Within five seconds after an administrator builds or modifies
> the form, the new form is on their site (which is on a 15-server farm)
> capturing and redirecting normalized data based on business rules for
> the recipient(s).[/color]

Awesome answer, maxl. Thanks.

It's an Access application, so I use a variety of Access tab controls and
forms: datasheet and continuous view mainly. The surveys are slightly
dynamic, in that an administrator will be revising the questions, points,
items, etc and pushing the new survey data down to the field each week or
so.

The survey itself is generated at run-time, by the user, from lookup tables
containing the questions. I copy the questions (necessary parts anyway) and
items into survey tables, along with a unique ID for the survey instance.

I want to do some web survey systems, but I can deliver this one much
quicker under Access. And with only 5 to 6 users, code updates and
distribution isn't an issue.

Thanks for your help.

re: DB design question - survey system

John Bell wrote:[color=blue]
> Hi
>
> You talk about a single table per section, but I am not sure that
> that sort of design would be any different to your suggested one.[/color]

Well, I was considering aggregating all responses into one table. As I
described, it would require the addition of several columns that are
irrelevant to 90% of the questions. The result would be a non-normalized
mess, though it would probably be easier to query and report on.

[color=blue]
> You could have a separate table for each type of questions i.e have
> all Y/N answers in one table, also Fail/Active/Nothing/Complementary
> can be a second attribute therefore you will not have separate
> columns for the Y/N part.[/color]

Interesting. That's kind of in-between where I am now and my "generic"
approach under consideration. I'll look into this a little more. I do have
some tables with similar response types I could combine.

[color=blue]
> You may also want to check out using SQL_VARIANT as a data type.
>[/color]
http://msdn.microsoft.com/library/de...asp?frame=true

It's an Access system, so text would be my only real option if I wanted to
combine response types into one column.


Thanks for your help.


[color=blue]
> Full text searching would possibly be a different solution.
>
> John
>
> "DFS" <nospam@nospam.com> wrote in message
> news:41Yyd.2693$Zp1.2514@fe07.lga...[color=green]
>> I've written several survey systems in which the majority of the
>> questions have the same or similar responses (Yes/No, True/False,
>> scale of 1 - 5, etc).
>>
>> But this latest survey system I'm working on has 8-10 sections, with
>> a variety of question attributes and answer scales. Some items have
>> just a description and require a Yes/No answer, others have a
>> description and an active status and require a Yes/No and price
>> answer, some require a comment,
>> etc.
>>
>> Rather than build a separate response table for each survey section,
>> I was thinking of building one generic response table, and trying to
>> force all sections to fit by adding columns - some of which won't
>> apply to some items.
>> Like this:
>>
>> Survey Category (will apply to all items)
>> Survey Section (will apply to all items)
>> Item Description (will apply to all items)
>> Item YN (will apply to all items)
>> Item Price (will apply to about 10% of the items)
>> Item Points (will apply to about 10% of the items)
>> Item Active YN (will apply to about 10% of the items)
>> Item Fail YN (will apply to about 10% of the items)
>> Item Comment (will apply to about 10% of the items)
>>
>> For instance, in the structure above the field "Item YN" would
>> represent multiple types of answers: is the item in use?, is the
>> item in place?, is the item given away for free?, is the item on
>> display?, etc. Basically, anywhere a Yes/No answer is used.
>>
>> The advantage is one source table (rather than 8) for storing
>> answers, and it might be easier to query and report on.
>>
>> The disadvantages I see are 1) it's more difficult to understand the
>> meaning
>> of the responses when the answer field is named Item YN, and 2) you
>> have a non-normalized table that's difficult for a 3rd party to
>> understand.
>>
>> If I have the questions and responses in separate tables, I'll use
>> names like "ItemComplimentaryYN" and "ItemUsedYN" depending on the
>> question. It's
>> easier for others to learn the data.
>>
>> I actually don't like the "generic" approach, and probably won't use
>> it, but
>> I figured I'd try to get some input from others who've written survey
>> systems.
>>
>> Thanks[/color][/color]