Guide To Design Database For Poll & Survey In MySQL

Guide To Design Database For Poll & Survey In MySQL

A complete guide to designing a database in MySQL for polling and survey websites and applications. It also covers the tracking of user activities to declare the poll and survey results.

December 08, 2019

This tutorial provides complete steps to design a database schema of closed or open polls and surveys via questionnaire to manage the users, polls, questions, answers, and votes. It can be further used to develop a polling and survey website or mobile application.

The Entity Relationship Diagram or visual database design is shown below.

Poll and Survey Database Design In MySQL

Fig 1

Notes: To keep the database schema simple and to develop a minimal viable product, it does not cover the more advanced options like versioning and reviewing the polls and surveys. It restricts to only logged in users to take part in a survey or poll to avoid spamming so that only legitimate votes get submitted.

You can also visit the popular tutorials including How To Install MySQL 8 on Ubuntu, How To Install MySQL 8 on Windows, RBAC Database in MySql, Blog Database in MySql, Learn Basic SQL Queries In MySQL.

Poll Database

The very first step is to create the Poll Database. It can be created using the query as shown below.

CREATE SCHEMA `poll` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I have used the character set utf8mb4 to support a wide range of characters.

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.

Id The unique id to identify the user.
First Name The first name of the user.
Middle Name The middle name of the user.
Last Name The last name of the user.
Mobile The mobile number of the user. It can be used for login and registration purposes.
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.
Profile The owner details to be displayed on the Poll or Survey Page.

The User Table with the appropriate constraints is as shown below.

CREATE TABLE `poll`.`user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR(50) NULL DEFAULT NULL,
  `middleName` VARCHAR(50) NULL DEFAULT NULL,
  `lastName` VARCHAR(50) NULL DEFAULT NULL,
  `mobile` VARCHAR(15) NULL,
  `email` VARCHAR(50) NULL,
  `passwordHash` VARCHAR(32) NOT NULL,
  `host` TINYINT(1) NOT NULL DEFAULT 0,
  `registeredAt` DATETIME NOT NULL,
  `lastLogin` DATETIME NULL DEFAULT NULL,
  `intro` TINYTEXT NULL DEFAULT NULL,
  `profile` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_mobile` (`mobile` ASC),
  UNIQUE INDEX `uq_email` (`email` ASC) );

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.

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.
Slug The slug to form the URL.
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.
Created At It stores the date and time at which the poll/survey is created.
Updated At It stores the date and time at which the poll/survey is updated.
Published At It stores the date and time at which the poll/survey is published.
Starts At It stores the date and time at which the poll/survey starts and open up for voting.
Ends At It stores the date and time at which the poll/survey closes for voting.
Content The column used to store the poll/survey data.

The Poll Table with the appropriate constraints is as shown below.

CREATE TABLE `poll`.`poll` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `hostId` BIGINT NOT NULL,
  `title` VARCHAR(75) NOT NULL,
  `metaTitle` VARCHAR(100) NULL,
  `slug` VARCHAR(100) NOT NULL,
  `summary` TINYTEXT NULL,
  `type` SMALLINT(6) NOT NULL DEFAULT 0,
  `published` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `publishedAt` DATETIME NULL DEFAULT NULL,
  `startsAt` DATETIME NULL DEFAULT NULL,
  `endsAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_slug` (`slug` ASC),
  INDEX `idx_poll_host` (`hostId` ASC),
  CONSTRAINT `fk_poll_host`
    FOREIGN KEY (`hostId`)
    REFERENCES `poll`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Poll Meta

The Poll Meta 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.

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

The Poll Meta Table with the appropriate constraints is as shown below.

CREATE TABLE `poll`.`poll_meta` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pollId` BIGINT NOT NULL,
  `key` VARCHAR(50) NOT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_meta_poll` (`pollId` ASC),
  UNIQUE INDEX `uq_poll_meta` (`pollId` ASC, `key` ASC),
  CONSTRAINT `fk_meta_poll`
    FOREIGN KEY (`pollId`)
    REFERENCES `poll`.`poll` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

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.

Id The unique id to identify the poll question.
Poll Id 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.
Created At It stores the date and time at which the question is created.
Updated At It stores the date and time at which the question is updated.
Content The column used to store the question.

The Poll Question Table with the appropriate constraints is as shown below.

CREATE TABLE `poll`.`poll_question` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pollId` BIGINT NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_question_poll` (`pollId` ASC),
  CONSTRAINT `fk_question_poll`
    FOREIGN KEY (`pollId`)
    REFERENCES `poll`.`poll` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

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.

Id The unique id to identify the poll answer.
Poll Id The poll id to identify the parent poll/survey.
Question Id The question id to identify the parent question.
Active Flag to identify whether the answer is active.
Created At It stores the date and time at which the answer is created.
Updated At It stores the date and time at which the answer is updated.
Content The column used to store the answer.

The Poll Answer Table with the appropriate constraints is as shown below.

CREATE TABLE `poll`.`poll_answer` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pollId` BIGINT NOT NULL,
  `questionId` BIGINT NOT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_answer_poll` (`pollId` ASC),
  CONSTRAINT `fk_answer_poll`
    FOREIGN KEY (`pollId`)
    REFERENCES `poll`.`poll` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

ALTER TABLE `poll`.`poll_answer` 
ADD INDEX `idx_answer_question` (`questionId` ASC);
ALTER TABLE `poll`.`poll_answer` 
ADD CONSTRAINT `fk_answer_question`
  FOREIGN KEY (`questionId`)
  REFERENCES `poll`.`poll_question` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

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.

Id The unique id to identify the poll vote.
Poll Id The poll id to identify the poll/survey.
Question Id The question id to identify the question.
Answer Id The answer id to identify the answer.
User Id The user id to identify the user.
Created At It stores the date and time at which the answer is created.
Updated At It stores the date and time at which the answer is updated.
Content The column used to store the user input.

The Poll Vote Table with the appropriate constraints is as shown below.

CREATE TABLE `poll`.`poll_vote` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pollId` BIGINT NOT NULL,
  `questionId` BIGINT NOT NULL,
  `answerId` BIGINT DEFAULT NULL,
  `userId` BIGINT NOT NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_vote_poll` (`pollId` ASC),
  CONSTRAINT `fk_vote_poll`
    FOREIGN KEY (`pollId`)
    REFERENCES `poll`.`poll` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

ALTER TABLE `poll`.`poll_vote` 
ADD INDEX `idx_vote_question` (`questionId` ASC);
ALTER TABLE `poll`.`poll_vote` 
ADD CONSTRAINT `fk_vote_question`
  FOREIGN KEY (`questionId`)
  REFERENCES `poll`.`poll_question` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `poll`.`poll_vote` 
ADD INDEX `idx_vote_answer` (`answerId` ASC);
ALTER TABLE `poll`.`poll_vote` 
ADD CONSTRAINT `fk_vote_answer`
  FOREIGN KEY (`answerId`)
  REFERENCES `poll`.`poll_answer` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `poll`.`poll_vote` 
ADD INDEX `idx_vote_user` (`userId` ASC);
ALTER TABLE `poll`.`poll_vote` 
ADD CONSTRAINT `fk_vote_user`
  FOREIGN KEY (`userId`)
  REFERENCES `poll`.`user` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Category Table and Poll Category Table

In this section, we will design the Category Table and Poll Category Table to store the poll categories and their mappings. Below mentioned is the description of all the columns of the Category Table.

Id The unique id to identify the category.
Parent Id The parent id to identify the parent category.
Title The category title.
Meta Title The meta title to be used for browser title and SEO.
Slug The category slug to form the URL.
Content The column used to store the category data.

The Category Table with the appropriate constraints is as shown below.

CREATE TABLE `poll`.`category` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `parentId` BIGINT NULL DEFAULT NULL,
  `title` VARCHAR(75) NOT NULL,
  `metaTitle` VARCHAR(100) NULL DEFAULT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`));

ALTER TABLE `poll`.`category` 
ADD INDEX `idx_category_parent` (`parentId` ASC);
ALTER TABLE `poll`.`category` 
ADD CONSTRAINT `fk_category_parent`
  FOREIGN KEY (`parentId`)
  REFERENCES `poll`.`category` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Below mentioned is the description of all the columns of the Poll Category Table.

Poll Id The poll id to identify the poll or survey.
Category Id The category id to identify the category.

The Poll Category Table with the appropriate constraints is as shown below.

CREATE TABLE `poll`.`poll_category` (
  `pollId` BIGINT NOT NULL,
  `categoryId` BIGINT NOT NULL,
  PRIMARY KEY (`pollId`, `categoryId`),
  INDEX `idx_pc_category` (`categoryId` ASC),
  INDEX `idx_pc_poll` (`pollId` ASC),
  CONSTRAINT `fk_pc_poll`
    FOREIGN KEY (`pollId`)
    REFERENCES `poll`.`poll` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pc_category`
    FOREIGN KEY (`categoryId`)
    REFERENCES `poll`.`category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Tag Table and Poll Tag Table

Similar to the category and poll category tables, we can design the Tag Table and Poll Tag Table. The major differences between the Category and Tag are listed below.

  • The parentId column is not required in the Tag Table.
  • The count of categories remains low since these can be used to form the Main Menu for navigational purposes. The tags can be more as compared to categories.
  • Both categories and tags can be used to relate the polls.
  • One should assign only a few categories to a poll whereas tags count can be more.

Summary

This is how we can design a Poll Database to be used as the formation of Poll and Survey based websites and mobile applications. The same can be further enhanced to add more advanced options including videos, payments, subscriptions, etc.

You may submit your comments to join the discussion. You may also be interested in designing the database of Blog applications. The RBAC design can be used for Role-Based Access Control implementation.

The complete database schema is also available on GitHub.

Write a Comment
Click the captcha image to get new code.
Discussion Forum by DISQUS
Recent Posts
How to store pictures in MySQL database
Guide To Design Database For Task Manager In MySQL
Guide To Design Database For Social Network System In MySQL
Guide To Design Database For Employee Management System In MySQL
Guide To Design Database For Notifications In MySQL