Typecho database design
Main structure
How about the data volume of a single user blog
Typecho is positioned as a single user blog system. Before we design its database, it is necessary to evaluate the load of the personal blog system. I have a friend who is a diligent blogger. Alexa ranks above 100000 and the daily IP is around 10w. He chose Wordpress as the main system, We know that there are more than 20 optimistic queries on the homepage of WordPress system. However, this cannot stop the popularity of this program. In the survey of the systems used by the top 10 bloggers in the world last year, WordPress has obvious advantages over other systems. Obviously, the load of WordPress is controllable
When designing a single user blog system, we should always pay attention to the words "* single user *". A single user means that data queries are very concentrated. When a user's page has a small number of visits, he can hardly feel how many delays caused by these additional queries. When the number of visits is large, he must have the strength to upgrade his system, Because the query of single user system is centralized, we can reduce the pressure on the database by deploying file cache or memory object cache, or increase the number of databases to achieve smooth system expansion. Therefore, the design of single user system focuses on flexibility and structure. When we focus on exposing system bottlenecks, On the other hand, we can concentrate on solving it
Design of 5 tables
Let's enumerate what elements a blog system needs, so that we can better design database tables. We need Article, comment, category, link, user The current blog system also needs * files, tags, link classifications, and multiple classifications *. If we consider the flexibility of the system, we also need to put all configurable options in a table, similar to the options table of WordPress
Let's count these tables
Article Table
Comment Form
Article Classification Table
Label Table
Linked Table
Link Classification Table
Article and classification mapping table (one to many)
Article and label mapping table (one to many)
Configuration table
User Table
file table
There are 11 tables in total. Although there are not many, there is always room for abstraction. When we carefully observe the relationship between them, in addition to the configuration table and the user table, the relationship between other tables can be abstracted as the relationship between content and items (maybe one-to-one, or one-to-many), such as comments and classification, links and link classification. Through this abstraction, We can reduce the remaining tables to three tables. Let's take a look at our second version database structure
table of content
Relationship Table
Project Table
Configuration table
User Table
According to the above design and our experience, we only need to carefully design the table structure of the content table and item table to form rich extended applications The correspondence between the item list and the content list forms the modification of the content Due to the existence of relationship tables, the relationship between content and items can be one-to-one or one to many
Content and content, design of 6 sheets
If you carefully analyze the above design, you will find a hidden problem, which is the definition of the comment table. Obviously, the comment table cannot be an item table, so it can only be a content table, but the relationship between content and content is not defined in the above design. Observe the relationship between comments and content
Comments belong to content and cannot exist alone
Comments and content are many to one, and one comment can only correspond to one content
The number of comments is often large. For blogs with a large number of visitors, the comments on a single article often reach hundreds
According to the above considerations, the comment table should form a separate table to distinguish from the content. According to the conventional practice and speed considerations, the comment should use a reserved field to save the primary key of its subordinate content for query. Then our third version of the database structure will be published
table of content
Relationship Table
Project Table
Comment Form
Configuration table
User Table
Sort out our design
Let's take a look at the types of content tables that can be extended
Post
Draft
Page
Link
Attachment
Then look at the types in the item table
Category
Tag
Link_category
Table and field naming
Considering the need for standardization and internationalization, we should try to use standard names in table and field settings. Because of the use of one to many relationship mapping, it is impossible to use joint queries between content and items in predictable places, but multiple * linked queries *, So the fields of the content table and the item table can have the same name (in the union query, the fields with the same name will be overwritten). The following is my naming of each data table
Content table - contents
Relationship Table - relationships
Item List - meta
Comments Form - comments
Configuration Table - options
Users Table - users
data dictionary
Contents table
| Key Name | type | attribute | explain |
| cid | int(10) | Primary key, non negative, auto increment | Post table primary key |
| title | varchar(200) | Can be empty | Content Title |
| slug | varchar(200) | Index, can be empty | Content Abbreviation |
| created | int(10) | Index, non negative, can be empty | GMT unix timestamp when content is generated |
| modified | int(10) | Non negative, can be empty | GMT unix timestamp when content changes |
| text | text | Can be empty | Content text |
| order | int(10) | Non negative, can be empty | sort |
| authorId | int(10) | Non negative, can be empty | User ID of the content |
| template | varchar(32) | Can be empty | Templates used by content |
| type | varchar(16) | Can be empty | Content Category |
| status | varchar(16) | Can be empty | Content Status |
| password | varchar(32) | Can be empty | Protected content, this field corresponds to the content protection password |
| commentsNum | int(10) | Non negative, can be empty | Number of comments to which the content belongs, redundant field |
| allowComment | char(1) | Can be empty | Allow comments |
| allowPing | char(1) | Can be empty | Allow ping |
| allowFeed | char(1) | Can be empty | Allow to appear in aggregation |
Relationships table
| Key Name | type | attribute | explain |
| cid | int(10) | Primary key, non negative | Content Primary Key |
| mid | int(10) | Primary key, non negative | Item Primary Key |
| Key Name | type | attribute | explain |
| mid | int(10) | Primary key, non negative | Item Primary Key |
| name | varchar(200) | Can be empty | name |
| slug | varchar(200) | Index, can be empty | Project Abbreviation |
| type | varchar(32) | Can be empty | Project Type |
| description | varchar(200) | Can be empty | Option Description |
| count | int(10) | Non negative, can be empty | Number of items |
| order | int(10) | Non negative, can be empty | Item sorting |
| Key Name | type | attribute | explain |
| coid | int(10) | Primary key, non negative, auto increment | Primary key of comment table |
| cid | int(10) | Index, nonnegative | Post table primary key, associated field |
| created | int(10) | Non negative, can be empty | GMT unix timestamp when comments are generated |
| author | varchar(200) | Can be empty | Commentator |
| authorId | int(10) | Non negative, can be empty | User ID of the comment |
| ownerId | int(10) | Non negative, can be empty | Author ID of the content to which the comment belongs |
| mail | varchar(200) | Can be empty | Reviewer Email |
| url | varchar(200) | Can be empty | Reviewer URL |
| ip | varchar(64) | Can be empty | Commenter ip address |
| agent | varchar(200) | Can be empty | Reviewer client |
| text | text | Can be empty | Comments |
| type | varchar(16) | Can be empty | Comment Type |
| status | varchar(16) | Can be empty | Comment Status |
| parent | int(10) | Can be empty | Parent comment |
Options table
| Key Name | type | attribute | explain |
| name | varchar(32) | Primary key | Configuration name |
| user | int(10) | Primary key, non negative | Configure the user. The default is 0 (global configuration) |
| value | text | Can be empty | Configuration value |
Users table
| Key Name | type | attribute | explain |
| uid | int(10) | Primary key, non negative, auto increment | Primary key of user table |
| name | varchar(32) | only | User name |
| password | varchar(32) | Can be empty | User Password |
| mail | varchar(200) | only | User's mailbox |
| url | varchar(200) | Can be empty | User's home page |
| screenName | varchar(32) | Can be empty | Name displayed by the user |
| created | int(10) | Non negative, can be empty | GMT unix timestamp at user registration |
| activated | int(10) | Non negative, can be empty | Last Activity Time |
| logged | int(10) | Non negative, can be empty | Last active time of last login |
| group | varchar(16) | N/A | User group |
| authCode | varchar(40) | Can be empty | User login verification code |
