Designing SQL Database for a blog application

With this tutorial we would have an understanding on how create a database schema for a blog application. It defines several tables and their relationships to store information about authors, blog posts, comments, replies, and post likes.

Here's a breakdown of the script:

1. Creating the blog database:

CREATE DATABASE IF NOT EXISTS Blog;

1. Creating the Author table:

CREATE TABLE IF NOT EXISTS Author (
author_id INT AUTO_INCREMENT,
full_name VARCHAR(255) NOT NULL,
about TEXT,
is_admin TINYINT(1),
status TINYINT(1),
date_registered DATETIME,
password VARCHAR(255),
PRIMARY KEY (author_id)
);

This table stores information about authors, including their ID, full name, about section, admin status, status, registration date, and password.

1. Creating the Posts table:

CREATE TABLE IF NOT EXISTS Posts (
post_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT,
is_published TINYINT(1),
date_published DATETIME,
date_edited DATETIME,
author_id INT NOT NULL,
thumbnail VARCHAR(255) DEFAULT('thumbnail.png'),
PRIMARY KEY (post_id),
FOREIGN KEY (author_id) REFERENCES Author(author_id)
);

This table stores information about blog posts, including their ID, title, description, published status, publication date, last edited date, author ID (foreign key referencing the Author table), and thumbnail image.

1. Creating the Comments table:

CREATE TABLE IF NOT EXISTS Comments (
c_id INT AUTO_INCREMENT,
post_id INT NOT NULL,
comment TEXT,
is_published TINYINT(1),
date_published DATETIME,
date_edited DATETIME,
author_id INT NOT NULL,
PRIMARY KEY (c_id),
FOREIGN KEY (post_id) REFERENCES Posts(post_id)
);

This table stores information about comments on blog posts, including their ID, post ID (foreign key referencing the Posts table), comment text, published status, publication date, last edited date, and author ID (foreign key referencing the Author table).

1. Creating the Replies table:

CREATE TABLE IF NOT EXISTS Replies (
r_id INT AUTO_INCREMENT,
c_id INT NOT NULL,
reply TEXT,
is_published TINYINT(1),
date_published DATETIME,
date_edited DATETIME,
author_id INT NOT NULL,
PRIMARY KEY (r_id),
FOREIGN KEY (c_id) REFERENCES Comments(c_id)
);

This table stores information about replies to comments, including their ID, comment ID (foreign key referencing the Comments table), reply text, published status, publication date, last edited date, and author ID (foreign key referencing the Author table).

1. Creating the PostLikes table:

CREATE TABLE IF NOT EXISTS PostLikes (
like_id INT AUTO_INCREMENT,
post_id INT NOT NULL,
likes TINYINT(1),
is_published TINYINT(1) DEFAULT('1'),
date_published DATETIME,
date_edited DATETIME,
author_id INT NOT NULL,
PRIMARY KEY (like_id),
FOREIGN KEY (post_id) REFERENCES Posts(post_id)
);

This table stores information about post likes, including their ID, post ID (foreign key referencing the Posts table), number of likes, published status, publication date, last edited date, and author ID (foreign key referencing the Author table).

1. Performing queries on the tables: The script includes sample queries to retrieve data from the tables. These queries include selecting all posts, retrieving a single post by its ID,