Storing and retrieving threaded messages requires extra care to display the threads in the correct order. Finding the children of each message and building the tree of message relationships can easily lead to a recursive web of queries. Users generally look at a list of messages and read individual messages far more often then they post messages. With a little extra processing when saving a new message to the database, the query that retrieves a list of messages to display is simpler and much more efficient.
Store messages in a table structured like this:
CREATE TABLE pc_message ( id INT UNSIGNED NOT NULL, posted_on DATETIME NOT NULL, author CHAR(255), subject CHAR(255), body MEDIUMTEXT, thread_id INT UNSIGNED NOT NULL, parent_id INT UNSIGNED NOT NULL, level INT UNSIGNED NOT NULL, thread_pos INT UNSIGNED NOT NULL, PRIMARY KEY(id) );
The primary key,
id, is a unique integer that
identifies a particular message. The time and date that a message is
posted is stored in
body are (surprise!) a message’s
author, subject, and body. The remaining four fields keep track of
the threading relationships between messages. The integer
thread_id identifies each thread. All messages in
a particular thread have the same
thread_id. If a
message is a reply to another message,
id of the replied-to message.
level is how many replies into a thread a message is. The first message in a thread has level ...