How does this database design look

database-design

enter image description here

This is my first official design after having read a coursebook in preparation for the upcoming semester. The goal is to have a user-based form creation/publication and submission web application.

A user should be able to create/publish a form on the site (my web server). Then users (both the creator of the form and others) should be able to submit the form. After that, a report could be generated and show which user submitted which answers. I tend to over-complicate statements when simple is better.

Here is how I envision the relationships working:

  • A USER belongs to many GROUPS
  • A GROUP consists of many USERS
  • Due to the many-many association, a composite entity is created.
  • A USER can create many FORMS
  • Each FORM belongs to one USER
  • A FORM provides many QUESTIONS
  • Each QUESTION belongs to one FORM
  • A QUESTION has many ANSWERS
  • Each ANSWER goes with one QUESTION
  • A FORM has many SUBMISSIONS
  • Each SUBMISSION is tied to one FORM
  • A SUBMISSION has many SUBMISSION_ANSWERS
  • Each SUBMISSION_ANSWER is part of one SUBMISSION

Best Answer

Your design doesn't record "which user submitted which answers".

Should there not be a relationship between SUBMISSION and USER? You have the attribute submission_user but you haven't indicated that it is a foreign key to USER.

Should there not be a link between SUBMISSION_ANSWER and ANSWER? How else do you know which question is being answered and what the value of the answer is?

SUBMISSION_ANSWER.attribute_name seems like you left something incomplete.

Related Question