-1

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?

Community
  • 1
  • 1
reaper_unique
  • 2,916
  • 3
  • 28
  • 48
  • This might be a better fit for programmers.stackexchange.com – Eric J. Jul 11 '12 at 23:09
  • Why exactly? I've come across various topics on stackoverflow regarding database design. Maybe DatabaseAdministrators. – reaper_unique Jul 11 '12 at 23:11
  • There is nothing specific about your question. It's basically -- "here's my requirements, tell me the answer". You've also tailored the question to your use cases and desires, and in the process the answers will not be of general interest to other developers working in the same space. For both those reasons, I believe you fall outside the stackoverflow guidelines. You should be able to complete your model, by adding a respondent entity (which should replace your "Emails" entity. A respondent should have an email address attribute that is nullable. Link Respondent to Surveys with a table. – gview Jul 11 '12 at 23:20
  • Assuming you call that table SurveyRespondent, give that table it's own PK. Then create a table SurveyRespondentAnswer that links to the Surveys_have_Questions for each answer given. Add what you need to record the answer. You hinted at a "meta" design for the questions that would let you describe the possible responses for a question... but you can see that this is already not a "simple" design. I'd suggest using MongoDB instead, as creating a "Question" collection that allows for question variance is a lot easier. – gview Jul 11 '12 at 23:23
  • @gview, I'm a bit stressed due to the amount of work I've got. So I indeed posted this with the idea, maybe they'll provide me with some answers. But because ctrahey basically said that I should try and concider the design I came across earlier I cooled down and checked it again, and whatadayakno' It works. I just had to review it better and more calmly. – reaper_unique Jul 12 '12 at 00:06
  • @reaper_unique All good. Solving this problem in a relational model is non-trivial unless you are willing to cut a lot of corners. I've worked on projects that had some fairly large and sophisticated design behind them for handling surveys, and even with the limitations you are trying to set, there's still probably 10 tables involved to do it in the way you described. – gview Jul 12 '12 at 00:14
  • See also http://stackoverflow.com/questions/1764435/database-design-for-a-survey – Damir Sudarevic Jul 13 '12 at 11:41
  • You can visit http://stackoverflow.com/questions/540885/what-mysql-database-tables-and-relationships-would-support-a-qa-survey-with-con – cutit Jul 20 '15 at 04:43
  • You can reference http://stackoverflow.com/questions/540885/what-mysql-database-tables-and-relationships-would-support-a-qa-survey-with-con – cutit Jul 20 '15 at 04:46

2 Answers2

1

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. :-)

Chris Trahey
  • 18,202
  • 1
  • 42
  • 55
  • Well, I'm guessing you are going towards this: http://stackoverflow.com/questions/540885/database-design-for-a-survey-system/5858666#answer-5858666 Which Is something I already tried but the relationships can't be right, can they? Also I don't need to store or know anything from the people that answer the questions except their age, sex, times they've visited the event, etc. but those questions don't need to be tied to a user or anything like that. – reaper_unique Jul 11 '12 at 23:21
  • Are you averse to that kind of schema? The relationships can start to seem complicated, but a normalized database (which is FULL of relationships) will serve you better in the long run. If you aren't comfortable with a seemingly-complex schema like that, read a bit on normalization... I've actually come to be uncomfortable with overly-simple schemas, they always rear their head at some point in the future; and the solutions are usually painful. – Chris Trahey Jul 11 '12 at 23:26
  • Not really I'm aware that that design is better and allows for more flexibility, but I'm in a bit of rush, my event is this weekend and I'd like to have a database ready by tomorrow :x I have the survey on paper as well, but I'd like visitors to fill in the survey online. I've already made the database from the link I posted in my comment but I'm not too familiar with the many-to-many relationship and how to handle the insertion of data. – reaper_unique Jul 11 '12 at 23:30
  • Believe it or not, I've got it. It really isn't that complex, I just needed to study the design a bit better and more calmly :). Now I can implement the database design into my online survey and most likely be able to let the visitors use the only survey without any problems. – reaper_unique Jul 12 '12 at 00:12
1

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.

Zalaboza
  • 8,899
  • 16
  • 77
  • 142
  • Wow, three years later. One up for the effort :) In the mean time I moved on and learned quite a bit more. But thanks anyway. – reaper_unique Nov 02 '15 at 09:11