Typecho Database Design

Typecho Database Design

Main structure

How much data is on a single-user blog

Typecho's positioning is a single-user blog system, before we design its database it is necessary to do some evaluation of the load of the personal blog system. I have a friend who is a diligent blogger,alexa ranked above 100,000, The daily IP is around 10w. He chose WordPress as the main system, we know that the WordPress system of a home page optimistic estimates also have more than 20 queries. But that still doesn't stop the popularity of the program, which was used last year in a systematic survey of TOP10 bloggers worldwide. WordPress has a distinct advantage over other systems. Obviously, the load on WordPress is manageable.

When we design a single-user blog system, we have to always put the * single user * These three words in mind. Single-user means that the data query is very focused, and when a user page is accessed for an hour, he hardly senses how much latency this number of queries brings. And when the traffic is large, He must have the strength to upgrade his system, and because the single-user system queries are centralized, we can either deploy the file cache or the memory object cache to reduce the database pressure, or increase the number of databases to achieve a smooth system expansion. Therefore, single-user system design focuses on flexibility and structure, When we focus on exposing the system bottlenecks, we can focus on the other side of the problem.

5-Sheet design

Let's enumerate what elements a blog system needs, which also allows us to better design database tables. We need articles, comments, categories, links, users, now the blog system also needs * files, tags, link classification, Multi-Classification *, if we consider the flexibility of the system, We also need to put all the configurable options in one table, similar to the WordPress options table.

Let's take a look at these tables.

Article table

Comment Form

Article Classification table

Label table

Link table

Link classification Table

Article and Classification mapping table (one-to-many)

Article and Tag mapping table (one-to-many)

Configuration table

User table

File table

A total of 11 tables, although not many but always feel that there is room for abstraction. When we look closely at the relationship between them, in addition to the configuration table and the user table. The relationships between the other tables can be abstracted to the relationship between the content and the project (perhaps one-to-one, possibly a-to-many), such as comments and classifications Links and Links categories. With this abstraction, we can reduce the rest of the table to 3 tables, so let's take a look at our second version of the database structure

Table of Contents

Relational tables

Project table

Configuration table

User table

Based on the above design and our experience, it is only necessary to design the table structure of the content table and the project table to form rich extension applications. The Project table and the table of contents correspond to the content of the modification. Because of the existence of a relational table, the relationship between the content and the project can be one-to-many.

Content and content, 6-sheet design

If you carefully analyze the design above, you will find a hidden problem, that is, the definition of the comment table. Obviously the comment table cannot be a project table, so he can only be a table of contents, but the relationship between content and content is not defined in our design. The relationship between observation and content

Comments belong to content and cannot exist alone

Comments and content are a many-to-one relationship, and a comment can only correspond to one content

The number of comments is often relatively large, for the large amount of visits to the blog, its single article comments are often to reach the hundreds.

According to the above considerations, the comment table should be separate form a table and the content area, and according to the general practice and speed considerations, the comment should be a reserved field to save its subordinate content of the primary key for querying. Then our third version of the database structure is out of the oven.

Table of Contents

Relational tables

Project table

Comment Form

Configuration table

User table

Comb Our Design

Let's take a look at the types of content tables that can be expanded

Post (article)

Draft (draft)

Page (pages)

Link (links)

Attachment (file)

Then take a look at the type of project table

Category (category)

Tag (tag)

Link_category (link category)

Table and field naming

In view of the need for standardization and internationalization, we should use the standard name as much as possible in our table and field settings. And because of the use of a one-to-many relationship mapping, it is impossible to use a federated query between content and project in a predictable way, but with multiple * linkage queries. To remove multiple rows of associated data. Therefore, the field of the table of contents and the project table can have the same name (in the union query, the renamed fields are overwritten). Here's my name for each table.

Table of Contents-contents

Relational table-Relationships

List of items-metas (meta meaning about what)

Comment form-Comments

Configuration Table-Options

User Table-Users

Data dictionary

Contents table

Key name type attribute Interpretation

CID Int (Ten) primary key, non-negative, self -increment post table primary key

Title varchar ($) can be empty content Header

Slug varchar index, which can be an empty content thumbnail name

Created Int (index) , non-negative, GMT Unix timestamp for empty content generation

Modified Int (TEN) non-negative, the GMT Unix Timestamp when the content changes to NULL

text text can be empty content text

Order Int (TEN) non-negative, nullable sort

Authorid Int (TEN) non-negative, can be an empty content owning user ID

Template varchar (+) templates that can be used for empty content

Type varchar (+) can be an empty content category

Status varchar (+) can be empty content State

Password varchar can be empty protected content, this field corresponds to the Content protection password

Commentsnum Int (TEN) non-negative, can be the number of comments for empty content, redundant fields

Allowcomment char (1) nullable whether comments are allowed

allowping char (1) nullable whether Ping is allowed

Allowfeed char (1) can be null allowed to appear in aggregations

Relationships table

Key name type attribute Interpretation

CID Int (Ten) primary key, non- negative content primary key

Mid Int (Ten) primary key, non- negative Item primary key

Metas table

Key name type attribute Interpretation

Mid Int (Ten) primary key, non- negative Item primary key

Name varchar ($) can be a null name

Slug varchar index, which can be an empty project thumbnail name

Type varchar (+) can be an empty project type

Description varchar ($) nullable option description

Count Int (Ten) is non-negative and can be the number of items to which an empty project belongs

Order Int (TEN) non-negative, can be ordered for empty items

Comments Table

Key name type attribute Interpretation

coid Int (Ten) primary key, non-negative, self -increment comment table primary key

CID int (index) , non-negative post table primary key, associated field

Created Int (TEN) non-negative, can be an empty comment when generating a GMT Unix timestamp

Author varchar ($) can be empty Comment by author

Authorid Int (TEN) non-negative, can be empty comment to the user ID

ownerID Int (TEN) non-negative, can be empty Comment The content Author ID

Mail varchar ($) can be empty for reviewer messages

URL varchar ($) can be empty for reviewer URLs

IP varchar (up to ) can be null reviewer IP address

Agent varchar ($) can be empty for reviewer clients

text text can be empty comment text

Type varchar (+) can be empty comment types

Status varchar (+) can be empty comment State

Parent Int (Ten) can be a null parental comment

Options table

Key name type attribute Interpretation

Name varchar primary KEY configuration names

User Int (Ten) primary key, non- negative configuration owner, default 0 (global configuration)

Value text can be null -configured values

Users table

Key name type attribute Interpretation

UID Int (Ten) primary key, non-negative, self -increment user table primary key

Name varchar (+) unique user names

Password varchar (+) can be a blank user password

Mail varchar (+) unique user's mailbox

URL varchar ($) can be empty for a user's home page

Screenname varchar (+) the name that can be displayed for empty users

Created Int (TEN) non-negative, GMT Unix timestamp when a null user can be registered

Activated Int (TEN) non-negative, can be empty last active time

Logged Int (TEN) non-negative, can be null last logged on active time

Group varchar (+) n /a user group

Authcode varchar (max) login Verification code for empty user

Typecho Database Design