CODEWITHSUNDEEP
mysqldatabasedatabase-design
Miguel Stevens
Miguel Stevens

Reputation: 9191

Reusable Questions in MySQL Survey Design

I'm working on a Reusable Survey database design. So the idea is.

A Client has many users, A client has categories which consist of questions. Every User has to answer all questions to complete the Survey. Those answers are stored in the Answers table.

The hard part

Some users are coaches, so a coach can fill in the survey for the user, thus providing a score on what they would answer in the place of the user. So we can later compare what the user answered and what the coach answered for each user. That's not to hard! The following is:

After some months we should be able to let the users redo the surveys, so with new answers to all the still existing questions.

I'm wondering if my db design is allright for this. I have the feeling that this isn't optimal.

enter image description here

For example the following queries seem difficult with my design

Looking very forward to your responses!

Upvotes: 3

Views: 375

Answers (1)

HLGEM
HLGEM

Reputation: 96552

Think about how you are going to want to use this information. Are you going to want to compare users scores to coaches scores to their new scores? I think that is likely. Will they end up taking the survey multiple times if they don't improve enough? Are there going to be questions that do not have integer answers? How are you going to store those results? When they create a new survey are they going to want to reuse some previous questions or answers (like yes/no). How are you going to identify a unique user, names are not unique and autogenerated IDs are unique, but how will you know which John Smith belongs to which of the 12 ids you have?

  • I would rename the Answer table as SurveyResponse.

  • To it I would add the datetime of the surveyresponse (so people can answer it multiple times and you can compare the answers) and a Survey ID (from the new table in the next suggestion).

  • I would create a Survey table that stores the questions that belong to a particular survey.

  • I would create a new Answer table that just has possible answers and an ID.

  • I would create a table called SurveyQuestionAnswer which stores the allowed answers to the question for each survey (different surveys might have different possible responses to the same question).

Upvotes: 2

Related Questions