August 13, 2012 at 8:46 am
Boss want me to create a asp.net application for survey in which there are about 50 questions. Most question is "Yes/No" ragarding of health care questions. For exaple, questions like:
Do you smoke? (yes/no)
Have you had a flu shot? (yes/no)
etc.
I can design a form interface using radio button to store "1" or "0".
But how store another info such as member info, posted date and comments?
Can someone point out how to design a table? Is there a sample to learn?
August 13, 2012 at 8:55 am
My first instinct is four tables:
Survey-takers table, with name, address, etc. Might want to normalize further and name addresses in a sub-table, depending on the business rules needed.
Questions table, just a list of questions.
Surveys table, which would be the survey-taker ID, the date of the survey, any other data at that level.
Suveys-to-questions table, which would be the survey ID (which then maps back to the survey-taker), the question ID, and the answer given.
This allows for changes in questions without having to rebuild the database. Add a new question? Just add a row to the questions table. Same user takes the survey multiple times? Already allowed for, and tracks changes in answers over time. Was "Yes" on "do you smoke" last time, is "no" this time on the same question, easily tracked.
Could become more complex if you have different sets of questions for different "surveys", which would add a table to manage the question-sets, and the relationship between the sets and the individual questions. That's two more tables, and the Surveys table would need a column to track which set of questions it used.
Beyond that, the design depends on the business rules. And this is just a rough draft after about 30 seconds of thinking about the subject, so it's probably subject to obvious improvements if you start modeling it more fully.
Is that the kind of thing you're looking for?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 9:04 am
This was removed by the editor as SPAM
August 14, 2012 at 4:58 pm
GSquared (8/13/2012)
My first instinct is four tables:[..]
Could become more complex if you have different sets of questions for different "surveys", which would add a table to manage the question-sets, and the relationship between the sets and the individual questions. That's two more tables, and the Surveys table would need a column to track which set of questions it used.
Beyond that, the design depends on the business rules. And this is just a rough draft after about 30 seconds of thinking about the subject, so it's probably subject to obvious improvements if you start modeling it more fully.
Seconded.
Having done this a bunch of times, I'd add a "survey definition" table (question sets), and a "campaign" table (history of use of survey definitions, also declaring a campaign "open" and then eventually "closed" becomes important in certain businesses).
Also, depending on the business rules (as previous poster said), it can be useful to create an "answer values" table and an "answer sets" table -- a question is then mapped to a specific answer set when a user is defining a new survey definition.
When different campaigns use a particular survey definition, all the responses in that campaign have the same range of answers. If the campaigns are using different survey definitions, their questions might be the same but the range of answers for some of the questions might be different.
Maybe this seems like overkill, but you're analyzing over time, you want to know for sure whether you're comparing apples to apples. For example, take your simple "yes-no" case. After several months, the boss decides "declines to answer" is appropriate for one of the questions in the survey. This is a different answer set, hence a new survey definition, and you don't mess up your statistics.
Why bother with campaigns, why aren't they synonymous with survey definitions? A campaign is targeted at a time period and a set of recipients. Two campaigns may use the same survey definition, but pull from different source lists, or offer time-sensitive incentives for completion, etc. A campaign is a cheap way of creating user-defined marketing or polling strategies without having a bunch of *other* tables capturing all that strategy, which would be a very difficult thing to do comprehensively at the outset.
>L<
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply