Structure a database for a Blog

I built a database for a blog. I am a student yet, so my knowledge is limited in this area. I post this question to get a briefly description of what I do wrong, why, and how I can fix it. I'm not a back end developer, so if you misunderstood something from this post, I'll explain it further.

This is a simple blog project. The following procedures are available for admin and the user.

ADMIN

• Register as author on the blog

• Post

• Write and article

USER

• View a post

• Like the post

• Post a comment

• Like the comment

Bellow are the tables that I use for the above procedures:

• authors (holds the registered authors of the blog)

• posts (holds all the informations needed for a post)

• comments (holds the data needed for a comment)

• likes_counter (holds the unique likes that a user make on a post or a comment)

Authors and Posts table relationship

I use one to many relationship to connect the authors and the posts tables. Each author can write many posts, and every post needs exactly one author.

The EER Diagram is given bellow:

And here are both the authors and the posts table schemas:

``
CREATE TABLE IF NOT EXISTS authors (
author_ID bigint(10) unsigned NOT NULL AUTO_INCREMENT,
author_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
author_avatar text COLLATE utf8_bin NOT NULL,
author_description text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (author_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS posts (
post_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
post_date datetime NOT NULL,
post_content longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
post_status varchar(20) COLLATE utf8_bin NOT NULL DEFAULT 'draft',
post_type varchar(10) COLLATE utf8_bin NOT NULL,
post_like_count int(10) unsigned NOT NULL DEFAULT '0',
post_comment_count` int(11) unsigned NOT NULL DEFAU

Tips:

• If an avatar is an image, use BLOB, not TEXT.

• Don't prefix each column name with the table name; it clutters unnecessarily.

• You are unlikely to ever need BIGINT; use INT UNSIGNED instead (max or 4 billion).

• A PRIMARY KEY is a UNIQUE key is a KEY. So don't re-index the PK.

• Consider using ENUM for things like status and type.

• A Post has both an article and a content? What's up?

• IP addresses, if kept as strings, can be VARCHAR(39) CHARACTER SET ascii.

StackExchange Database Administrators Q#145222, answer score: 4