Specifications

We will make one other optimization: We will separate the message bodies from the other data
and store them in a separate table. The reason for this is that this attribute will have the MySQL
type text. Having this type in a table can slow down queries on that table. Because we will do
many small queries to build the tree structure, this would slow it down quite a lot. With the mes-
sage bodies in a separate table, we can just retrieve them when a user wants to look at a particu-
lar message.
MySQL can search fixed size records faster than variable sized records. If we need to use vari-
able sized data, we can help by creating indexes on the fields that will be used to search the
database. For some projects, we would be best served by leaving the text field in the same
record as everything else and specifying indexes on all the columns that we will search on.
Indexes take time to generate though, and the data in our forums is likely to be changing all
the time, so we would need to regenerate our indexes frequently.
We will also add an area attribute in case we later decide to implement multiple chats with the
one application. We won’t implement this here, but this way it is reserved for future use.
Given all these considerations, the SQL to create the database for the forum database is shown
in Listing 29.1.
LISTING 29.1 create_database.sql—SQL to Create the Discussion Database
create database discussion;
use discussion;
create table header
(
parent int not null,
poster char(20) not null,
title char(20) not null,
children int default 0 not null,
area int default 1 not null,
posted datetime not null,
postid int unsigned not null auto_increment primary key
);
create table body
(
postid int unsigned not null primary key,
message text
);
Building Web Forums
C
HAPTER 29
29
BUILDING WEB
FORUMS
717
35 7842 CH29 3/6/01 3:34 PM Page 717