Personal blog development – Background (continuously updated)

System function structure diagram

Database design

The SQL statement

create table blog_user(
    user_id int not null AUTO_INCREMENT COMMENT 'id', 
    username varchar(255) not null DEFAULT ' ' COMMENT 'Username',
    password varchar(255) not null DEFAULT ' ' COMMENT 'password',
	role_id int not null DEFAULT '10' COMMENT 'character ID',
    head_url varchar(255) not null DEFAULT ' ' COMMENT 'Avatar link',
    sign varchar(255) default 'This man is lazy and has nothing left.' COMMENT 'Personal Signature',
    create_time datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    update_time datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT 'Update Time',
    is_delete tinyint not null DEFAULT '1' COMMENT 'Deleted 1 not deleted 2 Deleted'.PRIMARY KEY (`user_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User table';


create table blog_comments(
    comment_id int not null AUTO_INCREMENT COMMENT 'id', 
    article_id int not null DEFAULT '0' COMMENT 'the article ID',
    user_id int not null DEFAULT '0' COMMENT 'Comment user ID',
    sort int not null DEFAULT '0' COMMENT 'order',
    parent_id int not null DEFAULT '0' COMMENT 'Comment on a user',
    comment_content text not null COMMENT 'Comment content',
    create_time datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    is_delete tinyint not null DEFAULT '1' COMMENT 'Deleted 1 not deleted 2 Deleted'.PRIMARY KEY (`comment_id`),
    KEY `article_id` (`article_id`),
    KEY `user_id` (`user_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Comment list';


create table blog_articles(
    article_id int not null AUTO_INCREMENT COMMENT 'id', 
    article_title varchar(255) not null DEFAULT ' ' COMMENT 'Article Title',
    article_content text not null COMMENT 'Article content',
    label_id int not null DEFAULT '0' COMMENT 'tag ID',
    article_pv int not null DEFAULT '0' COMMENT 'Times of reading',
    editor_type int not null DEFAULT '0' COMMENT 'Text type 0 for rich text, 1 for Markdown',
    create_time datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    is_delete tinyint not null DEFAULT '1' COMMENT 'Deleted 1 not deleted 2 Deleted'.PRIMARY KEY (`article_id`),
    KEY `label_id` (`label_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Article table';


create table blog_labels(
    label_id int not null AUTO_INCREMENT COMMENT 'id', 
    label_name varchar(255) not null DEFAULT ' ' COMMENT 'Article tag name',
    create_time datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    is_delete tinyint not null DEFAULT '1' COMMENT 'Deleted 1 not deleted 2 Deleted'.PRIMARY KEY (`label_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Label list';


create table blog_message(
    message_id int not null AUTO_INCREMENT COMMENT 'id', 
    comment_id int not null DEFAULT '0' COMMENT 'Unread comment ID',
    create_time datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    is_delete tinyint not null DEFAULT '1' COMMENT 'Deleted 1 not deleted 2 Deleted'.PRIMARY KEY (`message_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Message table';


create table blog_resources(
    resource_id int not null AUTO_INCREMENT COMMENT 'id', 
    resource_title varchar(255) not null DEFAULT ' ' COMMENT 'Resource title',
    resource_content text not null COMMENT 'Resource Details',
    resource_url varchar(255) not null DEFAULT ' ' COMMENT 'URL path for resource',
    create_time datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT 'Resource Upload Time',
    is_delete tinyint not null DEFAULT '1' COMMENT 'Deleted 1 not deleted 2 Deleted'.PRIMARY KEY (`resource_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Resource table';


create table blog_roles(
    role_id int not null AUTO_INCREMENT COMMENT 'id', 
    role_name varchar(255) not null DEFAULT '0' COMMENT 'Role Name',
    create_time datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    is_delete tinyint not null DEFAULT '1' COMMENT 'Deleted 1 not deleted 2 Deleted'.PRIMARY KEY (`role_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Role table';

Copy the code

Java technology to learn

• Personal blog (background) – SpringBoot integration with JWT
• Personal blog (background) – upload files locally
• Personal blog (background) – verification code
• Personal blog (background) – cookie use
• Personal blog (background) – redis integration

Copy the code