Mysql – 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 DEFAULT '0',
  `post_has_article` tinyint(4) NOT NULL DEFAULT '0',
  `article_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `article_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`post_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

Q1

Is it how should I create the relationship?

Comments table

• Each posts can holds comments
• Users can not reply to comments
• Each comment can be faved

Bellow is the Diagram:

And the table schema:

CREATE TABLE IF NOT EXISTS `comments` (
  `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) unsigned NOT NULL,
  `comment_count` bigint(20) unsigned NOT NULL,
  `comment_author` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment_author_IP` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment_date` datetime NOT NULL,
  `comment_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment_approved` tinyint(4) NOT NULL DEFAULT '0',
  `comment_like_count` int(10) unsigned NOT NULL,
  `comment_author_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`comment_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

Q2

My main concern in this is how do I know which post holds what comment. I created a column comment_count that holds the id of the current post.

Example:

Lets say we have three articles with ids 300, 301, 302 and four comments. Then each row will take the following form:

comment_ID  -  comment_post_ID - comment_count
1              300               1
2              301               1
3              301               2
4              301               3

The article that has id of 302 has no comments, so no row is registered. I think is kind of many to many relationship, but not exactly. Is the right form to use?

Likes_Counter table

This was by far the most difficult decision I took.

• Each article can be faved
• Each comment can be faved
• The user won't need any account to take that action.
• He can fav an item (single post or comment) only one time. No duplicates faves allowed

I really don't know if all that should be actually contained in one table.

The thought was simple. We get the id of fav (comment or post) and we assign it to the same named column. We also get the user's IP address. So in the end we know if a user has already faved an item or not. But I don't know if my implementation is good (and by that I man maintainable).

Diagram:

Schema:

CREATE TABLE IF NOT EXISTS `likes_counter` (
  `like_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `like_type` varchar(10) COLLATE utf8_bin NOT NULL,
  `like_content_ID` bigint(20) NOT NULL,
  `like_IP` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`like_ID`),
  KEY `like_ID` (`like_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

I know this was a long post but I will already appreciate your answers.