The Database Schema is a type of blueprint or structure which represents the logical view of the database in a formal language supported by DBMS(Database Management System). It defines all the entities and their relationship with each other. The schema does not contain any actual data it’s just a visual representation of the database which is used by the database architect to design the actual database. To learn about designing database schema using MySQL Workbench. Please, refer MySQL Workbench – How to create database schema in MySQL Workbench?
Note: In this tutorial, we will create database schema for our Blog Application. Please refer the Blog Application Series for complete backend development with Spring Boot and MySQL.
Steps to design Database Schema
Step 1- Requirements and Analysis
First of all we need to do ther requirement gathering and analysis for the entities and the attributes which we want in our blog application. One can add more entities or attributes as per their requirements(Here, we will be using following the entities with their attributes).
| User | userId userName userEmail userPassword userAbout roleId |
| Post | postId postTitle postContent postImage postDate uesrId categoryId |
| Comment | commentId commentMessage userId postId |
| Category | categoryId categoryTitle categoryDescription |
| Role | roleId roleName |
Step 2- Design database schema
In this step, we will design the database schema of our blog application. For this we will be using Diagram Software and Flowchart Maker.
In the above database schema diagram, we have five different entities .i.e., User, Post, Comment, Category and Role. We can also visualize the relationship between different entities. For example, 1:n relationship between user and post .i.e., one user can create multiple posts. Similarly, we have other entities and their relationships with each other.