当前位置:网站首页>Turn the idea into a computer-readable design and design a database for my blog based on the idea

Turn the idea into a computer-readable design and design a database for my blog based on the idea

2020-12-08 11:09:26 The simplicity of developing small programs

According to the previous idea information , Combined with mind mapping 、 Design draft , We can design the database :
We need to create 5 A watch , Namely : Article table (articles)、 List of articles (article_data)、 Article classification table (categories)、 Article picture table (attachments)、 Blog manager table (admins).
Then design the required fields for each table :
 database

Article table articles Table structure :

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(250) NOT NULL DEFAULT '',
  `keywords` varchar(250) NOT NULL DEFAULT '',
  `description` varchar(250) NOT NULL DEFAULT '',
  `category_id` int(10) unsigned NOT NULL DEFAULT 0,
  `views` int(10) unsigned NOT NULL DEFAULT 0,
  `status` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `created_time` int(11) NOT NULL DEFAULT 0,
  `deleted_time` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_updated_time` (`created_time`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_views` (`views`),
  KEY `idx_status` (`status`),
  KEY `idx_created_time` (`created_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The article table is designed to store blog post information , Yes 9 A field , The Chinese meaning of the corresponding fields .

  • id For the self adding of the article id
  • title For the title of the article
  • keywords For the key words of the article
  • description For the introduction of the article
  • category_id For the classification of articles associated with classification id
  • views Count the number of page views
  • status For the display status of the article , The value is 1 It means normal display , The value is 0 Yes. , It means draft , Not on the front desk , The value is 99 When , This article has been deleted , Not on the front desk , It's kept for later inspection
  • created_time When the article was published , It's a timestamp
  • deleted_time It indicates the deletion time of the article , Record when this article was deleted, also save the time stamp

List of articles article_data Table structure

CREATE TABLE `article_data` (
  `article_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `content` longtext DEFAULT NULL,
  PRIMARY KEY (`article_id`),
  CONSTRAINT `fk_articles_article_data` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Design article table and article content table are stored separately , The main reason is that the content field of an article often has a lot of content , And the big content is mysql Searching , Will slow down the search time , Often when the content is displayed in the list , There's no need to show it , So separate storage can let the article list display when , You don't need to read the article , Increase the display time , Reduce memory consumption . Separate storage does not affect the display effect of specific articles .
The table of contents is simple , It's just 2 A field

  • article_id For the article id, Related to the article table id
  • content For the content of the article , use longtext Format store , Can be stored 40 Billion bytes of data , Don't worry about the problem that the article can't be saved if it is longer .

Article classification table categories Table structure

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(250) NOT NULL DEFAULT '',
  `description` varchar(250) NOT NULL DEFAULT '',
  `content` longtext DEFAULT NULL,
  `parent_id` int(10) unsigned NOT NULL DEFAULT 0,
  `status` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `created_time` int(11) NOT NULL DEFAULT 0,
  `deleted_time` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_created_time` (`created_time`),
  KEY `idx_updated_time` (`created_time`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The article classification table is used to store article categories , Set up 8 A field , They are :

  • id For classification id
  • title For the classification name
  • description Introduction to the classification
  • content For categorizing content
  • parent_id Classify the superior of the classification id
  • status For the state of classification , The value is 1 It means normal display , The value is 0 Yes. , It means draft , Not on the front desk , The value is 99 When , Indicates that this category has been deleted , Not on the front desk , It's kept for later inspection
  • created_time Indicates the release time of the classification , It's a timestamp
  • deleted_time Indicates the deletion time of the classification , Record when this category was deleted, and the same time stamp is saved

There are some fields reserved in the design , It needs to be expanded in the future . Classification introduction 、 Category content 、 Classified superior id Maybe not for the time being , Later use and reuse .

Article picture table attachments Table structure

CREATE TABLE `attachments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `file_name` varchar(100) NOT NULL DEFAULT '',
  `file_location` varchar(250) NOT NULL DEFAULT '',
  `file_size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `file_md5` varchar(32) NOT NULL DEFAULT '',
  `width` int(10) unsigned NOT NULL DEFAULT 0,
  `height` int(10) unsigned NOT NULL DEFAULT 0,
  `status` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `created_time` int(11) NOT NULL DEFAULT 0,
  `deleted_time` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `file_md5` (`file_md5`),
  KEY `idx_status` (`status`),
  KEY `idx_created_time` (`created_time`),
  KEY `idx_updated_time` (`created_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The article picture table is used to store the picture information uploaded by the published article . It has 10 A field .

  • id Auto add for image storage id
  • file_name Is the image file name
  • file_location Storage path for the image
  • file_size For the size of the picture
  • file_md5 For the picture MD5 value , It is used to determine whether the picture has been uploaded , Duplicate images can skip the upload and storage steps
  • width For the width of the picture
  • height For the height of the picture
  • status For the state of the picture , The value is 1 It means normal display , The value is 0 Yes. , It means draft , Not on the front desk , The value is 99 When , This article has been deleted , Not on the front desk , It's kept for later inspection
  • created_time Indicates the time when the picture was published , It's a timestamp
  • deleted_time Indicates the deletion time of the picture , Record when the image was deleted, and the same time stamp is saved

Blog manager table admins Table structure

CREATE TABLE `admins` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(16) NOT NULL DEFAULT '',
  `password` varchar(128) NOT NULL DEFAULT '',
  `status` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `created_time` int(11) NOT NULL DEFAULT 0,
  `deleted_time` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (`user_name`),
  KEY `idx_status` (`status`),
  KEY `idx_created_time` (`created_time`),
  KEY `idx_updated_time` (`created_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The blog administrator table is used to store the blog administrator's account and password . In theory, it supports multiple administrators to use .

  • id For the Administrator id
  • user_name For administrator name
  • password For administrator password , This code is not clear text , It's a string of encrypted strings , The database does not store clear text passwords . When you verify your password , By encrypting the submitted password , Then on 2 Compare encrypted passwords to verify that they are correct .
  • status For administrator status , The value is 1 It means normal display , The value is 0 When , Indicates that the user has disabled , The value is 99 When , deleted
  • created_time Indicates the administrator's add time , It's a timestamp
  • deleted_time Indicates the deletion time of the administrator , Record when the administrator deleted it, and the time stamp is the same

At this point, the database design is completed , Detail the meaning and function of each field . We import the data result information of the above design into the database to get the available database . Next , We will be based on the needs of the blog , To choose the language and technology for development .

版权声明
本文为[The simplicity of developing small programs]所创,转载请带上原文链接,感谢
https://chowdera.com/2020/12/20201208110859973n.html