Guide To Design Database For Blog Management In MySQL

This tutorial provides complete steps to design a database schema to manage the users, blog posts, post meta data, post comments, post categories, and post tags. It can be further used to develop a blogging website or mobile application.

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

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 posts. It provides options to review the comments by the post authors to avoid spamming so that only legitimate comments will be published and displayed on the Post Page.

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, Poll and Survey Database in MySql, Learn Basic SQL Queries In MySQL.

Blog Database

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

CREATE SCHEMA `blog` 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 all the post authors. The same table can be used to relate the post authors so that all the authors can manage their own posts. 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.
Registered At	This column can be used to calculate the life of the user with the blog.
Last Login	It can be used to identify the last login of the user.
Intro	The brief introduction of the Author to be displayed on each post.
Profile	The author details to be displayed on the Author Page.

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

CREATE TABLE `blog`.`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,
  `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) );

Post Table

In this section, we will design the Post Table to store the post data. Below mentioned is the description of all the columns of the Post Table.

Id	The unique id to identify the post.
Author Id	The author id to identify the post author.
Parent Id	The parent id to identify the parent post. It can be used to form the table of content of the parent post of series.
Title	The post title to be displayed on the Post Page and the lists.
Meta Title	The meta title to be used for browser title and SEO.
Slug	The post slug to form the URL.
Summary	The summary of the post to mention the key highlights.
Published	It can be used to identify whether the post is publicly available.
Created At	It stores the date and time at which the post is created.
Updated At	It stores the date and time at which the post is updated.
Published At	It stores the date and time at which the post is published.
Content	The column used to store the post data.

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

CREATE TABLE `blog`.`post` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `authorId` BIGINT NOT NULL,
  `parentId` BIGINT NULL DEFAULT NULL,
  `title` VARCHAR(75) NOT NULL,
  `metaTitle` VARCHAR(100) NULL,
  `slug` VARCHAR(100) NOT NULL,
  `summary` TINYTEXT NULL,
  `published` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `publishedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_slug` (`slug` ASC),
  INDEX `idx_post_user` (`authorId` ASC),
  CONSTRAINT `fk_post_user`
    FOREIGN KEY (`authorId`)
    REFERENCES `blog`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `blog`.`post` 
ADD INDEX `idx_post_parent` (`parentId` ASC);
ALTER TABLE `blog`.`post` 
ADD CONSTRAINT `fk_post_parent`
  FOREIGN KEY (`parentId`)
  REFERENCES `blog`.`post` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Post Meta

The Post Meta Table can be used to store additional information of a post including the post banner URL etc. Below mentioned is the description of all the columns of the Post Meta Table.

Id	The unique id to identify the post meta.
Post Id	The post id to identify the parent post.
Key	The key identifying the meta.
Content	The column used to store the post data.

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

CREATE TABLE `blog`.`post_meta` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `postId` BIGINT NOT NULL,
  `key` VARCHAR(50) NOT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_meta_post` (`postId` ASC),
  UNIQUE INDEX `uq_post_meta` (`postId` ASC, `key` ASC),
  CONSTRAINT `fk_meta_post`
    FOREIGN KEY (`postId`)
    REFERENCES `blog`.`post` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Post Comment Table

In this section, we will design the Post Comment Table to store the post comments. Below mentioned is the description of all the columns of the Post Comment Table.

Id	The unique id to identify the post comment.
Post Id	The post id to identify the parent post.
Parent Id	The parent id to identify the parent comment.
Title	The comment title.
Published	It can be used to identify whether the comment is publicly available.
Created At	It stores the date and time at which the comment is submitted.
Published At	It stores the date and time at which the comment is published.
Content	The column used to store the comment data.

The Post Comment Table with the appropriate constraints is as shown below.

CREATE TABLE `blog`.`post_comment` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `postId` BIGINT NOT NULL,
  `parentId` BIGINT NULL DEFAULT NULL,
  `title` VARCHAR(100) NOT NULL,
  `published` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `publishedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_comment_post` (`postId` ASC),
  CONSTRAINT `fk_comment_post`
    FOREIGN KEY (`postId`)
    REFERENCES `blog`.`post` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `blog`.`post_comment` 
ADD INDEX `idx_comment_parent` (`parentId` ASC);
ALTER TABLE `blog`.`post_comment` 
ADD CONSTRAINT `fk_comment_parent`
  FOREIGN KEY (`parentId`)
  REFERENCES `blog`.`post_comment` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Category Table and Post Category Table

In this section, we will design the Category Table and Post Category Table to store the post 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 `blog`.`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 `blog`.`category` 
ADD INDEX `idx_category_parent` (`parentId` ASC);
ALTER TABLE `blog`.`category` 
ADD CONSTRAINT `fk_category_parent`
  FOREIGN KEY (`parentId`)
  REFERENCES `blog`.`category` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

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

Post Id	The post id to identify the post.
Category Id	The category id to identify the category.

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

CREATE TABLE `blog`.`post_category` (
  `postId` BIGINT NOT NULL,
  `categoryId` BIGINT NOT NULL,
  PRIMARY KEY (`postId`, `categoryId`),
  INDEX `idx_pc_category` (`categoryId` ASC),
  INDEX `idx_pc_post` (`postId` ASC),
  CONSTRAINT `fk_pc_post`
    FOREIGN KEY (`postId`)
    REFERENCES `blog`.`post` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pc_category`
    FOREIGN KEY (`categoryId`)
    REFERENCES `blog`.`category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Tag Table and Post Tag Table

Similar to the category and post category tables, we can design the Tag Table and Post 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 posts.
• One should assign only a few categories to a post whereas tags count can be more.

Summary

This is how we can design a Blog Database to be used as the formation of Blog 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 Poll & Survey applications. The RBAC design can be used for Role-Based Access Control implementation.

The complete database schema is also available on GitHub.