Thoughts on Tables for Surveys

Databases
1

I am working on a new feature for my website which allows Members to rate Articles (if they so choose).

Below are two different designs. I would be curious to what everyone thinks.

Business Rules:

  • For now, all Survey Questions will be Multiple Choice (i.e. Scale 1 to 5) and so there is only one value stored for each.
  • One Survey (i.e. certain set of Survey Questions) is related to zero or more Articles
  • One Survey will likely have maybe 5 Survey Questions but that can change
  • Survey Questions should be “banked” and may be used on many Surveys and re-used at later dates
  • One Article can have many Survey Questions
  • One Survey Question can be related to many Articles
  • One Member can rate many Articles
  • One Article can be rated by many Members
  • One Member can respond to many Survey Questions
  • One Survey Question can be answered by many Members

Approach #1:
article -||------- 0<- survey_results ->0-------|- article_survey_questions
member -||--------0<- survey_results

article

  • id

member

  • id

article_survey_question

  • id

survey_results

  • article_id
  • article_survey_question_id
  • member_id

Approach #2:
article -||--------0<- article_survey_map ->0---------||- article_survey_question
member -||---------0<- survey_results ->0-----------||- article_survey_map

article

  • id

article_survey_question

  • id

article_survey_map

  • article_id
  • article_survey_question_id

survey_results

  • article_id
  • article_survey_question_id
  • member_id

member

  • id

I like Approach #2 as it seems more robust. Particularly, I think the concept of a “Survey Map” is important and great for managing things. (I got the idea from work.)

Thanks,

Debbie

2

I would allow yourself a little more wiggle room on the questions, rather than think everything is going to be an answer of 1-5.

questions - possible questions for all articles

question_id* AI
question_text

question_answers - possible answers for all questions

question_answer_id * AI
question_id
question_answer_text

articles

article_id* AI
other info about article…

article_questions - questions that are to be asked in each article

article_question_id* AI
article_id
question_id

article_results - answers

article_result_id*
article_question_id - INDEXED UNIQUE VALUE
question_answer_id
member_id

members

member_id

EDIT: Though looking back it looks like you had almost exactly this. Thought i didnt see anywhere to allow fulle text answers as possiblities

3

K. Wolfe,

I have no idea where you were going with your example, but that doesn’t help answer my OP.

Debbie

4

Well I’m sure you’ll get some help after the bunny with waffle post

5

Huh???

6

Google it