preface

After sharing the requirements, we will design the database according to the requirements. Here, I choose Mysql for the reason that Mysql is light, convenient, open source, free, easy to use, and efficient enough compared with other databases such as ORACLE

Database design

Here database design I recommend you a database design tool, I have been using feel good called PDMan

Multi-platform versions, Mac, Windows,Linux. All in all, very powerful, PDMan official website

Table design

The users table

CREATE TABLE users(
    user_Id BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'user ID' ,
    user_name VARCHAR(128)    COMMENT 'Username' ,
    user_nickname VARCHAR(128)    COMMENT 'User name' ,
    pwd VARCHAR(64)    COMMENT 'User password' ,
    email VARCHAR(64)    COMMENT 'User mailbox' ,
    avatar VARCHAR(128)    COMMENT 'User profile picture' ,
    create_time DATETIME    COMMENT 'Registration Time' ,
    birthday DATE    COMMENT 'User's Birthday' ,
    age INT    COMMENT 'User age' ,
    moble_phone VARCHAR(32)    COMMENT 'User's Mobile Phone Number' ,
    PRIMARY KEY (user_Id)
) COMMENT = 'users';;

Copy the code

The article table

CREATE TABLE article(
    article_id BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'the post ID' ,
    push_data DATETIME    COMMENT 'Release Date' ,
    article_user VARCHAR(32)    COMMENT 'Publish user' ,
    title VARCHAR(1024)    COMMENT 'Post title' ,
    like_count INT    COMMENT 'Likes' ,
    comment_count INT    COMMENT 'Number of comments' ,
    read_count INT    COMMENT 'Views' ,
    top_flag VARCHAR(1)    COMMENT 'Top or not' ,
    create_time DATETIME    COMMENT 'Creation time' ,
    article_summary VARCHAR(1024)    COMMENT 'Abstract' ,
    PRIMARY KEY (article_id)
) COMMENT = 'articles';;

Copy the code

The article details

CREATE TABLE article_detail(
    article_detail_id BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'Article details ID' ,
    content_md TEXT    COMMENT 'Article Markdown content' ,
    content_html TEXT    COMMENT 'Article HTML content' ,
    article_id BIGINT    COMMENT 'the article id' ,
    PRIMARY KEY (article_detail_id)
) COMMENT = 'Article Details';;

Copy the code

The article label

CREATE TABLE article_tag_referenced(
    atr_Id BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'reference id' ,
    article_id BIGINT    COMMENT 'the article id' ,
    tag_id BIGINT    COMMENT 'tag id' ,
    PRIMARY KEY (atr_Id)
) COMMENT = 'Article tag';;

Copy the code

The article classification

CREATE TABLE article_category_referenced(
    acr_id BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'reference id' ,
    article_id BIGINT    COMMENT 'the article id' ,
    category_id BIGINT    COMMENT 'category id' ,
    PRIMARY KEY (acr_id)
) COMMENT = 'Article Classification';;

Copy the code

Classification table

CREATE TABLE category(
    category_id BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'category ID' ,
    category_name VARCHAR(64)    COMMENT 'Category name' ,
    alias_name VARCHAR(64)    COMMENT 'Class alias' ,
    description VARCHAR(128)    COMMENT 'Classification description' ,
    parennt_id BIGINT    COMMENT 'Parent category ID' ,
    create_time DATETIME    COMMENT 'Creation time' ,
    PRIMARY KEY (category_id)
) COMMENT = 'classification';;
Copy the code

The label table

CREATE TABLE tag(
    tag_id BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'tag ID' ,
    tag_name VARCHAR(64)    COMMENT 'Label name' ,
    alias_name VARCHAR(64)    COMMENT 'Tag alias' ,
    description VARCHAR(128)    COMMENT 'Tag Description' ,
    create_time DATETIME    COMMENT 'Creation time' ,
    PRIMARY KEY (tag_id)
) COMMENT = 'tags';;
Copy the code

Comments on the table

CREATE TABLE discuss(
    discuss_id BIGINT NOT NULL AUTO_INCREMENT  COMMENT 'comment ID' ,
    create_time DATETIME    COMMENT 'Comment Date' ,
    like_count INT    COMMENT 'Likes' ,
    discuss_user BIGINT    COMMENT 'Publish user' ,
    article_id BIGINT    COMMENT 'Comment article ID' ,
    content VARCHAR(3072)    COMMENT 'Comment content' ,
    parent_id BIGINT    COMMENT 'Parent comment ID' ,
    PRIMARY KEY (discuss_id)
) COMMENT = 'comments';;
Copy the code

Pay attention to the public number ape xiao Shu to obtain SQL files