Analysis of Forum Database Design & amp; analysis and Analysis of Forum Databases

Analysis of Forum database design and analysis, analysis of Forum Databases
1. Database Design

The Database Design of the Forum is quite interesting. The database design is split according to the business logic.
First, a blog can be used as a post table. Then, considering that the Forum data volume is large, the design is optimized.
The Forum divides the database into three data tables, so that it can be queried quickly when accessing different pages.
For database tables, refer to the discuz Database Design:
I just picked some related fields and did not list all the fields.

-- Create table forum (fid mediumint (8) unsigned not null AUTO_INCREMENT, 'name' char (50) not null default '', 'status' tinyint (1) not null default '0', threads mediumint (8) unsigned not null default '0', posts mediumint (8) unsigned not null default '0', todayposts mediumint (8) unsigned not null default '0', primary key (fid) ENGINE = InnoDB default charset = utf8; -- forum post create table 'thread' ('tid' mediumint (8) unsigned not null AUTO_INCREMENT, 'fid' mediumint (8) unsigned not null default '0', 'author' char (15) not null default '', 'authorid' mediumint (8) unsigned not null default '0', 'subobject' char (80) not null default '', 'dateline 'int (10) unsigned not null default '0 ', & apos; views & apos; int (10) unsigned not null default '0', & apos; replies & apos; mediumint (8) unsigned not null default '0', & apos; attachment & apos; tinyint (1) not null default '0', 'closed 'mediumint (8) unsigned not null default '0', 'status' smallint (6) unsigned not null default '0 ', primary key ('tid') ENGINE = InnoDB default charset = utf8; -- forum post content create table post (pid int (10) unsigned not null AUTO_INCREMENT, fid mediumint (8) unsigned not null default '0', tid mediumint (8) unsigned not null default '0', 'first' tinyint (1) not null default '0', author varchar (15) not null default '', authorid mediumint (8) unsigned not null default '0', 'subobject' varchar (80) not null default'', dateline int (10) unsigned not null default '0', message mediumtext not null, useip varchar (15) not null default ''primary key (tid, pid) ENGINE = InnoDB default charset = utf8;

2. Specific analysis

Analyze from the page, according to the user access path analysis.
First, you can access the forum homepage, where the forum table is directly queried.
The Forum section information, number of topics, and number of posts are saved.

For example, a ride website forum: http://www.517318.com/(casually looking)

Considering the efficiency, some redundant information is stored in the forum table, and the counting information is directly recorded in this table.

Then go to the topic page.

The thread table is queried, And the thread table is also redundant.
When a user posts a post, a thread table is created, only the number of titles is saved, and a post table is created.

If it is post, data is inserted in both the thread table and the post table.
If it is a reply, only the post table and thread id are recorded at the same time.
Only the first one is post, and the other is post.

3. Summary

The original connection of this article is: http://blog.csdn.net/freewebsys/article/details/47343257 is not allowed by the blogger can not be reproduced.

The Forum database is designed to use three tables to split up different data and get three types of page queries quickly.
This is very enlightening for our system design. Similar situations include blog database design.
The blog title is saved as a table, and the Blog content is saved as a table. After the data is separated, the query is optimized in both cases.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.