Analysis Forum Database Design & Analysis

1, Database design

Forum Database design is quite interesting, according to the business logic to split the database design.
First of all, if it is a blog on a post memento can be. Then consider the data volume of the forum is relatively large, so the design is optimized.
The forum divides the database into 3 data tables, so queries are quickly made when accessing different pages.
database table Reference Discuz database design:
Just picked up some related fields and didn't list all the fields.

--Forum Section tableCREATE TABLEForum (FID Mediumint (8) unsigned not NULLAuto_increment,' name ' Char( -) not NULL DEFAULT "',' status 'tinyint1) 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=innodbDEFAULTCharset=utf8;--Forum postsCREATE TABLE ' thread '(' Tid 'Mediumint (8) unsigned not NULLAuto_increment,' FID 'Mediumint (8) unsigned not NULL DEFAULT ' 0 ',' Author ' Char( the) not NULL DEFAULT "',' Authorid 'Mediumint (8) unsigned not NULL DEFAULT ' 0 ',' Subject ' Char( the) not NULL DEFAULT "',' Dateline ' int(Ten) unsigned not NULL DEFAULT ' 0 ',' views ' int(Ten) unsigned not NULL DEFAULT ' 0 ',' replies 'Mediumint (8) unsigned not NULL DEFAULT ' 0 ',' attachment 'tinyint1) not NULL DEFAULT ' 0 ',' Closed 'Mediumint (8) unsigned not NULL DEFAULT ' 0 ',' status ' smallint(6) unsigned not NULL DEFAULT ' 0 ',PRIMARY KEY(' Tid ')) Engine=innodbDEFAULTCharset=utf8;--Forum post contentCREATE TABLEPost (PIDint(Ten) unsigned not NULLAuto_increment, FID Mediumint (8) unsigned not NULL DEFAULT ' 0 ', Tid mediumint (8) unsigned not NULL DEFAULT ' 0 ',' first 'tinyint1) not NULL DEFAULT ' 0 ', authorvarchar( the) not NULL DEFAULT "', Authorid Mediumint (8) unsigned not NULL DEFAULT ' 0 ',' Subject ' varchar( the) not NULL DEFAULT "', Datelineint(Ten) unsigned not NULL DEFAULT ' 0 ', Message Mediumtext not NULL, Useipvarchar( the) not NULL DEFAULT "'  PRIMARY KEY(Tid,pid)) Engine=innodbDEFAULTCharset=utf8;

2, specific analysis

Analysis from the page, according to the user Access path analysis.
First, the user visits the Forum home page, which will directly query the forums table.
It holds the Forum section information, the number of topics, the number of posts.

Like a ride. Web forum: http://www.517318.com/(Casual search)

Given the efficiency, some redundant information is stored in the Forum table, and the information of the count is directly recorded in the table.

Then go to the topic page

The following thread table is queried, and the thread table also does data redundancy.
When the user posts the appropriate will create a thread table, only the number of titles, and then create a post table, post the specific content.

If it is a post, both the thread table and the post table insert data.
If it is a reply, only the post table is recorded at the same time, the thread ID is recorded.
Only the first is a post, the others are replies.

3, Summary

The text of this text connection is: http://blog.csdn.net/freewebsys/article/details/47343257 not allowed to reprint without the Bo master.

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

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Analysis Forum Database Design & Analysis