Welcome to a sharing of an ERD for a simple feedback system. So you are working on a system to capture feedback from students, customers, patients, or whoever else? Well, here is a quick sharing of my version – Read on!
TABLE OF CONTENTS
ER Diagram
Table Structure
Extras
The End
ER DIAGRAM
First, here is the entire ER diagram and a quick overview of the system.
FEEDBACK SYSTEM ERD
This may be quite intimidating for some beginners, but keep calm and look carefully. The entities are split into 3 groups:
- Users – Students, teachers, doctors, patients, customers – Let’s just call it “users” here.
- Feedback – To do what the system is supposed to do… The questions you want to ask, and the available options.
- Feedback Response – To collect the user response.
FOR REFERENCE ONLY
I can hear the “true professional experts” banging on their keyboards – “This is stupid”, “worst, useless”, “that is not how it works”. By any means, this is only a simple example for reference.
- The assumption here is “an online feedback system”, feel free to make your own changes if that is not the case.
- If you allow public or anonymous feedback, feel free to remove users from the response.
- A feedback exercise will definitely have one or many questions. If that is somehow not the case, feel free to change it on your own.
- A question can have none or multiple options; Open questions don’t have “selectable options”. If that is not the case, feel free to change it.
Yes, you are building your own system now. You decide for yourself what is “cleaver” and “makes sense”.
TABLE STRUCTURE
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
| COLUMN | DATA TYPE | NOTES |
| user_id | INTEGER | Primary key. |
| user_name | VARCHAR | The user’s full name. |
| user_email | VARCHAR | Email. Set this to unique to prevent duplicate registrations. |
| user_password | VARCHAR | The user’s login password. |
As above – The assumption is an “online feedback system”, users have to log into the system to do the feedback. Even if it is an “offline feedback system”, some form of user system has to exist for the administrators to create and access the feedback data.
PART 2) FEEDBACK
2A) “MAIN” FEEDBACK
| COLUMN | DATA TYPE | NOTES |
| feedback_id | INTEGER | Primary key. |
| feedback_title | VARCHAR | Title of the feedback. For example, “Customer Service Feedback For ABC Store”. |
| feedback_txt | VARCHAR | A short description of the poll. For example, “For the period of X to Y”. |
| user_id | INTEGER | Foreign key. The administrator who created this feedback. |
Straightforward as can be. Who created this feedback form, and what is it for?
2B) FEEDBACK QUESTIONS
| COLUMN | DATA TYPE | NOTES |
| feedback_id | INTEGER | Primary and foreign key. |
| q_id | INTEGER | Primary key, question ID. |
| q_txt | VARCHAR | Feedback question. |
| q_type | VARCHAR | What type of question – Multiple choice, select one, select multiple, scale of 1 to 5, open text? |
Questions of the feedback form, examples:
- From 1 to 5, rate the customer service, the product quality, etc…
- Which of the following product features do you find attractive, pick one or more.
- What types of products do you like to see more?
2C) FEEDBACK OPTIONS
| COLUMN | DATA TYPE | NOTES |
| feedback_id | INTEGER | Primary and foreign key. |
| q_id | INTEGER | Primary and foreign key. Question ID. |
| o_id | INTEGER | Primary key. Option ID. |
| o_txt | VARCHAR | The option itself. |
Available options for a question. This can be anything from “bad, poor, average, good, excellent” to selectable options such as “user-friendliness, design, price, etc…”
P.S. Questions such as a number scale (1 to 5) and open text will not have options.
PART 3) FEEDBACK RESPONSE
| COLUMN | DATA TYPE | NOTES |
| feedback_id | INTEGER | Primary and foreign key. |
| user_id | INTEGER | Primary and foreign key. |
| q_id | INTEGER | Primary and foreign key. |
| o_id | INTEGER | Primary and foreign key. |
| r_txt | VARCHAR | For open-text questions only. |
Lastly, a table to capture the user response. Pretty much a collection of foreign keys, but should not be much trouble.
P.S. If you want to allow anonymous feedback, just change the user ID to a VARCHAR response ID; Generate a random unique string for every feedback session, and use that as the response ID.
EXTRAS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!
