Specifications

Designing the Database
There are a few attributes we’ll need to store about each article posted to the forum: the person
who wrote it, called the poster; the title of the article; when it was posted; and the article body.
We will therefore need a table of articles. We’ll create a unique ID for each article, called the
postid.
Each article needs to have some information about where it belongs in the hierarchy. We could
store information about an article’s children with the article. However, each article can have
many replies, so this can lead to some problems in database construction. As each article can
only be a reply to one other, it is easier to store a reference to the parent article, that is, the arti-
cle that this article is replying to.
That gives us the following data to store for each article:
•
postid: A unique ID for each article
• parent: The postid of the parent article
• poster: The author of this article
• title: The title of this article
• posted: The date and time that the article was posted
• message: The body of the article
We will add a couple of optimizations to this.
When we are trying to determine whether an article has any replies, we will have to run a query
to see whether any other articles have this article as a parent. We will need this information for
every post that we list. The fewer queries we have to run, the faster our code will run. We can
remove the need for these queries by adding a field to show whether there are any replies. We
will call this field children and make it effectively Boolean—the value will be 1 if the node has
children, and 0 if it does not.
There is always a price to pay for optimizations. Here we are choosing to store redundant data.
As we are storing the data in two ways, we must be careful to make sure that the two represen-
tations agree with each other. When we add children, we must update the parent. If we allow
the deletion of children, we need to update the parent node to make sure the database is consis-
tent. In this project we are not going to build a facility for deleting articles, so we will avoid
half of this problem. If you decide to extend this code, bear this issue in mind.
It is worth noting that some databases would help us out a little more here. If we were using
Oracle, it could maintain relational integrity for us. Using MySQL, which does not support
triggers or foreign key constraints, we need to write our own checks and balances to make sure
that data still makes sense each time we add or delete a record.
Building Practical PHP and MySQL Projects
P
ART V
716
35 7842 CH29 3/6/01 3:34 PM Page 716