Forum system - how to track already read vs new messages, per user

[eluser]jedd[/eluser]
Okay.. that reminded me to fix up my mail handling, as I'd done some dodgy dual-use stuff with one of the fields in my 'one size fits all' message table. And I hate dodgy dual-use fields - I always forget one of the uses.

I'm doing private messages as threads that have a forum ID of 0 - this is special (not keen on specials, but it'll work for now) that indicates the thread belongs to mail, not forum. I then have a separate table to denote who has access to a thread. This means I don't really need the special of '0', but will reconsider this later. The bulk of threads will not have an entry in my thread_acl table - only private messages will, and they'll require two rows (sender, receiver). This means I can possibly cater for semi-private forums & threads in the future.

In any case, mail will be more forum-like, as I agree with your suggestion on this aspect. There is now no concept of an 'addressee' for a message. The addressee is effectively the other person you invite to your private thread at message compose time.

Anyway, I present my current messaging schema for completeness and comments.

One comment and question up front - I've separated out the message_content (4k BLOB) into its own table on the expectation I'll get better performance. Do we think that this is true? I will probably go VARCHAR on that actual message content column. I expect that I won't be hitting message very often without hitting message_content at at the same time (LEFT JOIN to grab the actual content). It's definitely a 1:1 relation, which is why I feel a bit uneasy about separating it into two tables.

Code:

# forum
#
# Likely only a few dozen of these.
#
# @TODO - consider having parent_id in here, to allow for sub-forums, or forum headings
# so perhaps a BOOL flag to denote if it's a grouping or a real forum.
#
# For now - flat structure - every forum is an island.

CREATE TABLE forum  (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        CHAR (100) UNIQUE NOT NULL,     # Eg. 'Seed saving'
    description CHAR (250),                     # Eg. 'To discuss the saving of seeds'
    PRIMARY KEY (id),
    INDEX (name)
    );

Code:

# thread
#
# Threads - for forums and private mail

CREATE TABLE thread  (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    forum_id    INT UNSIGNED NOT NULL,          # FK to forum.id
    sticky      BOOL,                           # To show at the top of every forum, or not
    views       SMALLINT,                       # We don't care about tracking > 65k views
    subject     CHAR (99),                      # Eg. 'Problem with drying out tomato seeds'
    PRIMARY KEY (id),
    INDEX (forum_id),
    INDEX (subject)
    );

Code:

# message
#
# All private messages and public (forum) messages will live in this one table.  I think.

CREATE TABLE message  (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    thread_id   INT UNSIGNED DEFAULT 0,         # FK to thread.id (every message is part of a thread)
    author      INT UNSIGNED DEFAULT 0,         # FK to member.id (every message has an author)
    msgdate     DATETIME,                       # Date of message creation
    message_content_id  INT UNSIGNED NOT NULL,  # FK to message_content.id
    PRIMARY KEY (id),
    INDEX (thread_id),
    INDEX (author)
    );

Code:

# message_content
#
# The actual content / body / text - variable size, might be quite huge, single index for now
#
# @TODO - will need to look at requirements for indexing the .text column

CREATE TABLE message_content  (
    id          INT UNSIGNED NOT NULL UNIQUE,   # Same as message.id
    actual      TEXT(4096),                     # Gives us 4k per message
    PRIMARY KEY (id)
    );

Code:

# thread_acl
#
# Rights to threads - will probably only ever be used for mail/private messages,
# so two rows per private thread (author/addressee).

CREATE TABLE thread_acl  (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    thread_id   INT UNSIGNED NOT NULL,          # FK to thread.id
    member_id   INT UNSIGNED NOT NULL,          # FK to member.id
    PRIMARY KEY (id),
    INDEX (thread_id),
    INDEX (member_id)
    );

Code:

# message_member
#
# Tracks who has read what messages - or rather, where a member is up to
# in a particular thread.
#
# NOTE: only ever one row for any given [ thread_id , member_id ] pairing.

CREATE TABLE message_member  (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id   INT UNSIGNED NOT NULL,          # FK to member.id
    thread_id   INT UNSIGNED NOT NULL,          # FK to thread.id
    message_id  INT UNSIGNED NOT NULL,          # FK to message.id
    PRIMARY KEY (id),
    INDEX (member_id),
    INDEX (thread_id),
    INDEX (message_id)
    );