Voting/Polling System ERD (With Database Structure)

Welcome to a sharing of an ERD for a voting or polling system. So you are working on a polling system project, or maybe stuck with this as a school project? Well, here is a quick sharing of my version – Read on!

 

 

TABLE OF CONTENTS

 

 

VOTING/POLLING SYSTEM ERD

There are a lot of “connections”, but look closely. There are only 3 groups of entities:

• Users – Yep, the assumption is that the polling/voting is for registered users only.
• Polls – To address the elephant in the room. Poll questions and options.
• Poll Select – To collect the “poll data”.

 

FOR REFERENCE ONLY

My developer senses are tingling, I can hear the “angry experts” banging on their keyboards – “Dumb, stupid, worst, useless”, “I can make a better system”. By any means, this is only a simple example for your reference.

• If you want to open the polls/votes to the public, make your own changes. Remove the users and find some way to prevent spam.
• A poll can have multiple questions, feel free to make your own changes that if polls only have one question.
• A question must have multiple options (at least two). Feel free to change that if it is somehow not the case.
• We adopt “a user is able to choose multiple options” here to cover all the possibilities. Free free to change this if a user can only choose one option for a question.

Yes, you are building your own system now. You have to decide what is “good, intelligent, useful” for yourself.

 

 

 

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, this is a closed system. The votes/polls are only open to registered users for a good reason – An anonymous public poll on the Internet is literally an open invitation for spam. So unless you are building a “poll/vote” terminal in a shop/restaurant/hotel, it will be wise to keep a user table.

 

PART 2) POLLS

2A) “MAIN” POLL

COLUMN	DATA TYPE	NOTES
poll_id	INTEGER	Primary key.
poll_title	VARCHAR	Title of the poll. For example, “How is the quality of the food”.
poll_txt	VARCHAR	A short description of the poll. For example, “For the newly launched lunch menu at the school cafeteria on some date”.
poll_owner	INTEGER	Foreign key. The user who created this poll.

This should be straightforward – Who created a poll, and what it is for?

 

 

2B) POLL QUESTIONS

COLUMN	DATA TYPE	NOTES
poll_id	INTEGER	Primary and foreign key.
pq_id	INTEGER	Primary key, question ID.
pq_txt	VARCHAR	The poll question.

The poll questions. Yes, a poll can have multiple questions. Following up with the above example on the school cafeteria food, we can have questions like “Did the menu have a good variety of items” and “How is the taste of the food”.

P.S. You can also add a field to control if this question allows only one, many, or no options to be selected. But be careful not to turn this into a survey.

 

2C) POLL OPTIONS

COLUMN	DATA TYPE	NOTES
poll_id	INTEGER	Primary and foreign key.
pq_id	INTEGER	Primary and foreign key. Question ID.
po_id	INTEGER	Primary key. Option ID.
po_txt	VARCHAR	The option itself.

The poll options. This can be anything from “1 to 5”, to “bad, average, good”, or even “I want to see more fish/beef/vegetable varieties”.

 

 

PART 3) POLL RESPONSE

COLUMN	DATA TYPE	NOTES
poll_id	INTEGER	Primary and foreign key.
user_id	INTEGER	Primary and foreign key.
pq_id	INTEGER	Primary and foreign key.
po_id	INTEGER	Primary and foreign key.

Finally, a table to capture the user responses. Feel free to make your own changes if a user can only choose one option – Remove the option as a primary key.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart
• Simple PHP MySQL Poll System – Code Boxx
• Python Flask Vote System – Code Boxx