Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Survey Database design

    Hi everyone.

    I have a survey database that I am developing for a client. It started out as a question/answer database for a single application, but after a requirements change, I think its better to got with a survey engine.

    Link to Database Diagram

    There are some caveats that you need to be aware of. First all user data is currently held in an external database. We do not want to redundently store the information into this database. We are using a view to access the necessary information from that database.

    however....(there is always a but)

    we need to control how surveys are answered. I would like to do as much of that in the database as I can.

    For instance in this first application there will be 2 surveys. The first survey is only ever displayed once. The other survey is to be displayed so long as it has never been answered based on the person, place, and appt type.(which are in the view I discussed earlier) This view is being used to alert the questioner as to which survey has been asked.

    Person1 first ever visit gets xSurvey
    Person1 visits new location gets ySurvey
    Person1 visits same location and gets no survey.

    The questions and answers are dynamic. I had this section working before the requirements change. However now it may be a bit more complicated.

    Ill try and clear things up as questions are answered.

    The main point of this is to track the Answers for marketing. We will need to be able to track which answers a particular user gave(at least for the person level survey which gets opt-in information).

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    ... and your question is ?

    PS Welcome to the forum

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    I need help verifying that the database structure will work for what I am trying to do. I have actually made some more changes since that image and will post the changes.

    New map

    I guess I am looking for suggestions and whether I am on the right track for what I am trying to do.

    ie...

    You really shouldnt have a double index like that..or you really need to rethink the way you are creating your relationships...If it were me I would.....

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It would probably be good to provide the create table (and index) statements as well if you have them.

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    The creation script is enclosed. It should be ok...let me know if not and ill regenerate it.
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by ecathell
    I need help verifying that the database structure will work for what I am trying to do
    I was kind of hoping for a few very simple create statements but somehow got 50k worth of SQL

    A few points:
    • Where do the user answers go - none of your tables seem to hold them?
    • You have a sort order on the valid answers but not one on the questions themselves.
    • A user can never enter a free text answer using your system.
    • Would it be worth adding the facility that certain questions are only asked if another question is answered with X?
    • How about grouping your questions into groups. This will make the questionnaire more logical. You could then have a hierarchy of groups (if you wished). A group of questions would only be asked if a question was answered with X etc.
    • What is the purpose of the AnswerTypes and QuestionTypes tables?
    • Could the tables SurveyQuestions, Questions and QuestionTypes just be one table?
    • How do you know that a user has finished a survey on an application rather than just quit half way through?

    Mike
    Last edited by mike_bike_kite; 05-01-09 at 05:56.

  7. #7
    Join Date
    Apr 2009
    Posts
    4
    Quote Originally Posted by mike_bike_kite
    I was kind of hoping for a few very simple create statements but somehow got 50k worth of SQL

    A few points:
    • Where do the user answers go - none of your tables seem to hold them?
      in the second image there is a responses table I forgot to add in the first one. It holds the personid,surveyid,answerid fields.
    • You have a sort order on the valid answers but not one on the questions themselves.
      Yea, I was thinking about adding that functionality. In the previous iteration, but then these requirements changed and at the moment isnt a priority, even though its just a field.
    • A user can never enter a free text answer using your system.
      Actually they can. That is what the other field in responses is for. In the actual program QuestionType and AnswerType control what kind of controls are used to create the questions. there is an Other answertype which pops up a dialog box that allows them to freetext their answers. the Response is placed in the other field.
    • Would it be worth adding the facility that certain questions are only asked if another question is answered with X?
      Not at this time, these are pretty straightforward survey questions. But it would be something to think about in the future.
    • How about grouping your questions into groups. This will make the questionnaire more logical. You could then have a hierarchy of groups (if you wished). A group of questions would only be asked if a question was answered with X etc.
      its not needed at this time, but could be a future enhancement for other surveys
    • What is the purpose of the AnswerTypes and QuestionTypes tables?
      these help control how the answers and questions are addressed in the application. ie radiobuttons, checkboxes, freetextfield for questiontypes(which actually formats the answers) answertypes allows for standard answers, additional text, or dropdown field
    • Could the tables SurveyQuestions, Questions and QuestionTypes just be one table?
      I dont think so, because there could be multiple surveys that have the same question on them, I could maybe get rid of SurveyQuestions, because there will ever only be one questionid on the same survey. So survey would have a childtable of Questions instead...Questiontypes is needed to determine the type of question, It would not be good normalization to have that as part of the Question Table
    • How do you know that a user has finished a survey on an application rather than just quit half way through?
      We dont, other than in the reports when we see they didnt answer all of them. But that will be controlled(as much as possible) through the application itself. As well whether they refused the survey. that will just be a question with a no answer that we can track

    Mike
    please see my inline responses...the website did not like me doing that.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Your second diagram looks to me like it would fulfill the requirements you have outlined.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •