Analysis Forum Database design & Analysis __ Database

1, database design

Forum Database design is very interesting, according to business logic to split the database design.
First of all, if a blog is a post token can be. Then consider the Forum data volume is larger, so the design has optimization.
The forum divides the database into 3 data tables so that the query is quick when accessing different pages.
database table Reference Discuz database design:
Just picked up some related fields and didn't list all the fields.

--Forum Plate Table CREATE Table Forum (FID Mediumint (8) unsigned not NULL auto_increment, ' name ' char ' is not null DEFAULT ', ' Status ' tinyint (1) NOT null default ' 0 ', Threads mediumint (8) unsigned NOT NULL default ' 0 ', Posts mediumint (8) UNS igned NOT NULL default ' 0 ', todayposts mediumint (8) unsigned NOT NULL default ' 0 ', PRIMARY KEY (FID)) Engine=innodb D

Efault Charset=utf8; --Forum posts CREATE TABLE ' thread ' (' Tid ' mediumint (8) unsigned not NULL auto_increment, ' FID ' mediumint (8) unsigned not NU LL DEFAULT ' 0 ', ' author ' char (' null default ', ' Authorid ' mediumint (8) unsigned NOT NULL default ' 0 ', ' Sub Ject ' char not null default ', ' Dateline ' int (m) unsigned NOT NULL default ' 0 ', ' views ' int (a) unsigned not NU

  LL DEFAULT ' 0 ', ' replies ' Mediumint (8) unsigned NOT NULL default ' 0 ', ' attachment ' tinyint (1) NOT null default ' 0 ', ' Closed ' mediumint (8) unsigned NOT NULL default ' 0 ', ' status ' smallint (6) unsigned NOT null default ' 0 ', PRIMARY KE Y(' Tid '))

Engine=innodb DEFAULT Charset=utf8; --Forum post content CREATE TABLE post (PID int (a) unsigned NOT NULL auto_increment, FID Mediumint (8) unsigned NOT NULL DEFAULT ' 0 ', Tid mediumint (8) unsigned NOT NULL default ' 0 ', ' I ' tinyint (1) NOT null default ' 0 ', author varchar (NO)
  T null default ', Authorid mediumint (8) unsigned NOT NULL default ' 0 ', ' subject ' varchar (+) NOT null default ',

  Dateline Int (a) unsigned NOT null default ' 0 ', message mediumtext NOT NULL, USEIP varchar (a) NOT null default ' PRIMARY KEY (tid,pid)) Engine=innodb DEFAULT Charset=utf8;

2, the specific analysis

Analysis from the page, according to the user Access path analysis.
First the user visits the Forum homepage, this place will inquire directly the forum table.
There is the Forum plate information, the number of topics, the number of posts.

For example, a cycling website forum: http://www.517318.com/(Casual search)

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

Then go to the theme page

The following thread table is queried, and the thread table also has data redundancy.
When the user posts fit will create a thread table, save only the number of headers, and then create a post table, post the specific content.

If it is a post, the thread and post tables are inserted into the data.
If it is a reply, only the post table is recorded while the thread ID is logged.
Only the first one is the post, the others are replies. 3, Summary

The original text of this article connection is: http://blog.csdn.net/freewebsys/article/details/47343257 without the owner's permission shall not be reproduced.

Forum database design through 3 tables, divided open different data, get 3 kinds of page query speed is very fast.
This is very enlightening for our system design, and similar to the blog database design.
The title of the blog to save a table, the content of the blog to save a table, the data is separated, both of the query are optimized.