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 "ItemCompliment aryYN" 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 5 1690
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).
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" <no****@nospam. com> wrote in message
news:41******** *********@fe07. lga... 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 "ItemCompliment aryYN" 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
DFS wrote: 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.
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","t ext"}--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(su rveyID, 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 ma**@msn.com wrote: 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).
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.
John Bell wrote: 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.
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.
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.
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.
You may also want to check out using SQL_VARIANT as a data type. 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.
Full text searching would possibly be a different solution.
John
"DFS" <no****@nospam. com> wrote in message news:41******** *********@fe07. lga... 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 "ItemCompliment aryYN" 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: franz |
last post by:
Hi developers!
Are you using design pattern? And do you want to help me with my
research project?
Ok – you only need two minutes – I made a little survey form (8 very
short questions).
There are no mail address or other personal data questioned…
Just point your browser to:
http://baseportal.com/cgi-bin/baseportal.pl?htx=/krauth/pattern_survey/pattern_survey
|
by: Kenzo Fong |
last post by:
Hi everyone,
Sorry to fill up this newsgroup with this request, but for coursework at
Erasmus University (the Netherlands) I need to conduct a survey
regarding the use of certain open source projects and I'm therefore
looking for some volunteers to participate in this survey. The survey is
located at: http://www.surveymonkey.com/s.asp?u=42670815560 or
alternatively at http://tinyurl.com/3stvu. I'm aware of the (self)
selection bias...
|
by: DFS |
last post by:
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...
|
by: JG |
last post by:
I am a developer and I have a problem trying to design a system to
manage data coming from web surveys. Each section can potentially have
dozens of questions, i.e., fields.
I am focusing here only on the table(s) that will hold the survey data.
I do not have any DDL as I am still trying to understand this!
All the examples I have found so far in books and on the web seem to
deal with fairly limited data, that is easily, or so it looks,...
|
by: kimi |
last post by:
I have just started working on a project that is partially complete. It
is an application that is using access to store test results. The test
results are being stored in two Access 2000 databases.
DB #1 = StudentDB
DB #2 = TestResulstsDB
Why are there 2 dbs? I do not know - but that is one of the tings that
we will be changing. Combining all of the data into one database.
| |
by: Joerg Rech |
last post by:
Dear software practitioners, consultants, and researchers,
we are currently conducting an international survey about
architecture and
design patterns. Our goal is to discover how familiar people are with
these
patterns (and anti-patterns) as well as to elicit the information
need, the
usage behavior, and the experience of software organizations regarding
architecture patterns and design patterns.
|
by: Joerg Rech |
last post by:
Dear software practitioners, consultants, and researchers,
we are currently conducting an international survey about
architecture and design patterns. Our goal is to discover how familiar
people are with these patterns (and anti-patterns) as well as to
elicit the information need, the usage behavior, and the experience of
software organizations regarding architecture patterns and design
patterns.
Therefore, we would like to invite you...
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
|
by: Janet93 |
last post by:
If you are involved in the development of scientific computing
software, you are invited to participate in a survey on developing
this kind of software. If you have already received this request, I
apologize for the cross-posting, but I am attempting to advertise to
as many developers as possible.
I would appreciate it if you could take 20-30 minutes to complete this
questionnaire. If you know others involved in the development of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |