Since I never really documented the Prattle data model, I'll elaborate a bit. Comments welcomed.
The data model depends on the features you want to support.
I wanted a login system. That means I need to keep track of people, so I have a user table with the predictable columns for names, passwords, and so forth. Naturally, each user gets a unique uid.
Obviously I need to keep track of messages, so I have a table for that, with the predictable columns for a normal message's properties: title, body, et cetera. Of course, each note gets a unique messageid.
There is a one-to-many authoring relationship between users and messages. That means each person could write many messages, but no message has more than one author. Therefore the message table contains a uid column so that each message can be stamped with its author's uid. I don't put the author's name, email, et cetera, directly into the message table -- that would be dumb.
Now come two features that complicate the data model.
First, I want to organize the messages.
There are several ways I could do that. If I encouraged branching (like Phorum does, here), I would allow each note to point to a parent's ID -- a recursive one-to-many relationship. That's easy, and doesn't require another table. A little recursive function will crawl down the "tree" and retrieve all of the child messages.
But I wanted to discourage branching -- pushing a model more like Web Crossing or The Well. I wanted fewer contexts and more linear talk within a context.
And I wanted a context to be potentially customizable in terms of display.
So I created a third type of entity, which I call a room (drawing from the Citadel BBS model). A room has simple properties -- title, creator, roomid, et cetera, plus text fields for HTML that can contain a potentially customized header and footer and stylesheet.
There is a one-to-many relationship between rooms and messages (in other words, messages appear in one, and only one, room but a room can contain many messages). To support that, the message table gets another column, so each message can know where it belongs. To find all the messages in a given room, I just select from the message table where roomid = the current room's roomid. Not too hard.
The second complicating feature is that I wanted to automatically display ONLY the new messages, keeping track of the each user's "highwater" mark in each room. This is pretty important in a linear discussion system -- you don't want to be digging through a big pile of old messages every time you visit.
This is a many-to-many relationship, so to support it I have a separate "hiwater" table. It is simple: it contains uid, roomid, and messageid information.
So, when you read messages in a room, I first query the hiwater table where roomid = the current room and uid = the current user. That gives me the last-read messageid, the hiwater mark. Then I query the message table where roomid= the current room and messageid > the hiwater mark. Voila: The unread messages.
Naturally, I have to update the hiwater table every time you read.
(If I wanted to throw out hiwater tracking and the room-context headers and footers., all of this could be done with two tables. If I also threw out the login requirement, all of this could be done with one table and it wouldn't be particularly hard.)
After I built all of this, I decided that I wanted even more organization: I wanted to be able to put entire discussions (rooms) into folders.
I didn't create another table for folders. Instead, I decided that a folder is just a special kind of room. This is pretty much how Unix handles files and directories. I added an "isfolder" column to the rooms table. A room can contain messages; a folder can contain rooms or messages. This is enforced externally, by the PHP script, not by SQL.
