Analysis of personal blog database

Function module introduction

1. User management: user login, password retrieval

2. Blog management: write, modify and delete blog

3. Comment management: others can comment and reply to other users' comments

4. Category management: add and delete categories, and set categories for articles

5. Tag management: add and remove tags, tag articles

user management

• User table information: user id, user nickname, user password, user mailbox, user image

• The user is only myself. Set the initial user nickname, user password and user mailbox

• Users log in with user name and password, and change password with email

• After logging in, you can modify the personal information such as the user's Avatar, user's nickname, edit and publish your blog.

Bowen management

• Blog information: blog id, release date, blog title, blog content, likes, replies, visits, categories, labels

Comment system

• Comment information: comment ID, comment date, likes, comment blog ID, comment content, parent comment ID

Classified management

• Classification information: classification id, classification name, classification description, parent classification id

• You can add, delete, and modify classifications

• Categories can be used as blog menus

Label management

• Label information: label id, label name, label description
• When publishing a blog, you can set a label. The label can be used to classify a blog or as a menu for a blog

Mind mapping

mysql code

CREATE DATABASE myBlog
USE myBlog
-- Create user table
CREATE TABLE Blog_user(user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'user id',
user_name VARCHAR(50) COMMENT 'User name',
user_psd VARCHAR(20) COMMENT 'User password',
user_email VARCHAR(20) COMMENT 'User mail box',
user_avatar VARCHAR(1000) COMMENT 'User head')
-- User mailbox field is too short, change field
ALTER TABLE Blog_user MODIFY user_email VARCHAR(30)

-- Write initial data to user table
INSERT INTO Blog_user(user_name,user_psd,user_email,user_avatar) 
VALUES('xxx','xxxx','xxxx@xxx.com','xxx.img')

-- Query user table data
SELECT * FROM Blog_user

-- Create classification table
CREATE TABLE Blog_classify(
classify_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'classification id',
classify_name VARCHAR(30) COMMENT 'Classification name',
classify_desc VARCHAR(500) COMMENT 'Classification description',
classify_upId INT COMMENT 'Parent classification ID')

-- Create label table
CREATE TABLE Blog_label(label_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Label id',
label_name VARCHAR(20) COMMENT 'Label name',
label_desc VARCHAR(500) COMMENT 'Label description')

-- Create blog table
CREATE TABLE Blog_artc(artc_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Bowen id',
artc_title VARCHAR(500)NOT NULL COMMENT 'Blog title',
artc_content LONGTEXT NOT NULL COMMENT 'Blog content',
artc_praise SMALLINT DEFAULT 0 COMMENT 'Praise points',
artc_reply SMALLINT DEFAULT 0 COMMENT 'Reply number',
artc_browse SMALLINT DEFAULT 0 COMMENT 'Browse volume',
artc_classify INT NOT NULL COMMENT 'Blog classification',
artc_label INT NOT NULL COMMENT 'Bowen Tags',
artc_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

-- Set foreign key for blog table
ALTER TABLE Blog_artc ADD CONSTRAINT artic_classify 
FOREIGN KEY(artc_classify) REFERENCES Blog_classify(classify_id)
ALTER TABLE Blog_artc ADD CONSTRAINT artic_label 
FOREIGN KEY(artc_label) REFERENCES Blog_label(label_id)
DESC Blog_artc

-- Create comment table
CREATE TABLE Blog_comment(comment_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'comment id',
comment_artcId INT COMMENT 'Comments blog's id',
comment_content TEXT COMMENT 'Content of comments',
comment_fatherId INT COMMENT 'Father comments id',
comment_praise SMALLINT COMMENT 'Praise points',
comment_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT comment_artc FOREIGN KEY(comment_artcId) REFERENCES  Blog_artc(artc_id),
CONSTRAINT comment_com FOREIGN KEY(comment_fatherId) REFERENCES 
Blog_comment(comment_id)
)

Reference resources: Design of personal blog database