My supervisor asked to design a database by using MySQL. This database is used to store information about evaluation surveys. The survey has a lot of questions, and each question has 5 choices, the user choose the answer, and then the system evaluate the scores by the answer. Basically, my supervisor wants me to create a database that stores information about surveys, and also the results of each survey. Could anybody give me an idea how to start it?
Mysql – Design of MySQL
database-designMySQL
Related Solutions
You need to make a distinction between the possible answers and the selected answers.
The Option table needs to be two tables. The Option table should be 1:M to Question and should include the possible answers for that question.
Then you need to make a new intersection entity, call it Selected_Option which sits between User and Option.
If your question gives the user an opportunity to fill in a value as an answer (i.e. "OTHER:...") then this value would be stored in the Selected_Option table. Otherwise the value chosen by the user would be the value found in Option.
EDIT:
Based on OP's clarification of requirements: What you need is not like a typical questionnaire model in the following ways:
- Your questions all have the same sets of answers (columns)
- Some of your answers (columns) are grouped together.
- Blocks of questions are grouped together.
Taking your form snapshot as a guide, I've divided up the elements of your form into entities which I've colour coded:
This could be accomodated by the following logical ERD:
Note that I've colour coded the entities in the ERD to correspond to the snapshot of your sample form to show the correlation.
One of the assumptions in this model is that each block has only one set of quesitons (i.e. one QUESTION_GROUP) which corresponds to the left-hand column in the block. This is a bit of a simplifying assumption.
I offer a more complete model, from an actual solution.
The 'type' is associated to the question per-se (not to its items, as stated at the fourth item from the question).
A question may be of single-line or multiple-line textual box or composed by several items. For each item may be assigned a complementary question (eg. 'Country?' - 'Other' - 'Which: ___').
HOW the question and its items will be presented to the user depends on their type.
The model has been made flexible enough to represent several structures of questions.
The 'FormQuestionType' entity has an attribute to identify the ASCX (ASP.net component) responsible of rendering questions of this type.
It's possible to make a question dependent of one or more of the previous ones (eg. 'How many children?' may be made dependent of 'Do you have any children?').
There's prevision for data validation, from 'required' constraint to regular expressions.
For every major entity has been defined the mandatory and unique attribute named 'identification', allowing queries based upon textual codes instead of numerical ids (eg. 'How many children?' question, of id '351', is also identified by 'HOW_MANY_CHILDREN').
And, for the items of a given questions, there may be defined an external source of data (eg. 'city' question has its options retrieved live from corporate 'city' table).
The actual model provide versioning, but, for clarity's sake, I present a stripped down version of it.
Best Answer
I don't have any experience in this domain, but a quick Google of "open source survey software mysql" gives 874,000 results. What I recommend that you do is to download a few of these - see how they organise their database schema(s) and adopt and adapt your own schema to what's been done already. As Newton said, stand on the shoulders of giants. You could also take code if you can read the particular langauage in which it's written - (PHP, Perl, Python - whatever).
Your question is very broad and vague and probably outside of the guidelines for the forum - take the tour and also the "help us to help you" blog - both at the bottom of the page. These forums are a great resource and you will gain a lot by following the guidelines.