polls-database-schema

Polls database Schema

With this schema you can now start building an application in any languages of your choice to create a simple polls applications.

Tables in this schema

User Table

In this section, we will design the User Table to store user information of the poll/survey owner. The same table can be used to relate the poll/survey owners so that the users can manage their own poll or survey and to track the voting activities. Below mentioned is the description of all the columns of the User Table.

Column name	Description
Id	The unique id to identify the user.
First Name	The first name of the user.
Last Name The last name of the user.
Email	The email of the user. It can be used for login and registration purposes.
Password Hash	The password hash generated by the appropriate algorithm. We must avoid storing plain passwords.
Host	The flag to identify whether user can host poll or survey.
Registered At	This column can be used to calculate the life of the user with the application.
Last Login	It can be used to identify the last login of the user.
Intro	The brief introduction of the User to be displayed on the Poll or Survey Page.
displayName	The owner details to be displayed on the Poll or Survey Page.

Poll Table

In this section, we will design the Poll Table to store the poll and survey data. Below mentioned is the description of all the columns of the Poll Table.

Column name	Description
Id	The unique id to identify the poll/survey.
Host Id	The host id to identify the poll/survey host.
Title	The poll/survey title to be displayed on the Poll/Survey Page and the lists.
Meta Title	The meta title to be used for browser title and SEO.
Summary	The summary to mention the key highlights.
Type	The type to distinguish between the poll and the survey.
Published	It can be used to identify whether the poll/survey is publicly available.
CreatedAt	It stores the date and time at which the poll/survey is created.
UpdatedAt	It stores the date and time at which the poll/survey is updated.
PublishedAt	It stores the date and time at which the poll/survey is published.
StartsAt	It stores the date and time at which the poll/survey starts and open up for voting.
EndsAt	It stores the date and time at which the poll/survey closes for voting.
Content	The column used to store the poll/survey data.

Poll Metadata

The Poll Metadata Table can be used to store additional information of a poll or survey including the poll banner URL etc. Below mentioned is the description of all the columns of the Poll Meta Table.

Column name	Description
Id	The unique id to identify the poll meta.
PollId	The poll id to identify the parent poll/survey.
Key	The key identifying the meta.
Content	The column used to store the poll metadata.

Poll Question Table

The Poll Question Table can be used to store the questions related to polls and surveys. The ideal scenario is to have one question for polls and multiple questions for surveys. Below mentioned is the description of all the columns of the Poll Question Table.

Column name	Description
Id	The unique id to identify the poll question.
PollId	The poll id to identify the parent poll/survey.
Type	The type of question. The type can be a single choice(Yes/No), multiple-choice, select, or input.
Active	Flag to identify whether the question is active.
CreatedAt	It stores the date and time at which the question is created.
UpdatedAt	It stores the date and time at which the question is updated.
Content	The column used to store the question.

Poll Answer Table

The Poll Answer Table can be used to store the answers of single-choice, multiple-choice and select type questions. In case of single-choice question, the answers can be Yes and No. Below mentioned is the description of all the columns of the Poll Answer Table.

Column name	Description
Id	The unique id to identify the poll answer.
PollId	The poll id to identify the parent poll/survey.
QuestionId	The question id to identify the parent question.
Active	Flag to identify whether the answer is active.
CreatedAt	It stores the date and time at which the answer is created.
UpdatedAt	It stores the date and time at which the answer is updated.
Content	The column used to store the answer.

Poll Vote Table

The Poll Vote Table can be used to store the user choices and inputs. Below mentioned is the description of all the columns of the Poll Vote Table.

Column name	Description
Id	The unique id to identify the poll vote.
PollId	The poll id to identify the poll/survey.
QuestionId	The question id to identify the question.
AnswerId	The answer id to identify the answer.
UserId	The user id to identify the user.
CreatedAt	It stores the date and time at which the answer is created.
UpdatedAt	It stores the date and time at which the answer is updated.
Content	The column used to store the user input.