CODEWITHSUNDEEP
mysqldatabase-designsurvey
reaper_unique
reaper_unique

Reputation: 2931

Simple survey database design

I'm creating a survey for visitors of my event. However it's been a while since I created a database. So I need some help.

I found some solutions but they are way to extensive and that is something I don't need.

Visitors need to stay anonymous but they can leave their email behind (seperate table Emails that isn't linked to anything atm). They have about 20 questions, some are open, some are one option(radio) and some are multiple options (checkboxes). The questions need to be reusable. That's about it. I just don't know how to go beyond the many-to-many in the diagram you see below. Survey databasedesign

How do I go from here? An Answers table needs to have a relationship with? The Surveys_have_Questions, or with Questions?

Edit:

As the answer in the following links mentions, most surveys are based upon classic design patterns. Mine is one of those surveys. More info in the link below: What mysql database tables and relationships would support a Q&A survey with conditional questions?

Upvotes: -1

Views: 5805

Answers (2)

Momen Zalabany
Momen Zalabany

Reputation: 9007

well i dont see reason why you need all these tables ! i think it can be much simpler than that.

surverys

desc VarChar
startDate timestamp
endDate timestamp
isOpen boolean

survery_questions

survery_id int (FK)
question Text
vote_count unsigned INT

user_survery

user_id
survery_id 
unique key (user_id_survery_id) #to ensure no duplicate votes

That all :). when ever a user vote just run

insert into user_survery (user_id,survery_id) VALUES (1,1);
update survery_questions set vote_count = vote_count+1;

when u need to get a survery result 

select * from survery_questions where survery_id = X;

etc.

Upvotes: 1

Chris Trahey
Chris Trahey

Reputation: 18290

I would probably model the event of a user taking a survey, perhaps a table called "User_Answer_Session", which has links to the survey and the user; and then "User_Answers", which are tied to the session and the question and include the actual blob of the answer. How exactly I modeled the answers would depend on a few things (mainly how robustly I wanted to be able to look them up). For instance, do I want to be able to index multiple-choice answers for extremely rapid reporting? If so, then you need to model for that. This may include creating a "Question_Options" table, which is a one-to-many between a question and the available options...

This should get you thinking along a good path. :-)

Upvotes: 1

Related Questions