Making a More Advanced Model With User, Thread, and Post Statuses

In my first article about an online forum, I mentioned that there might be several more advanced features to add:

• More formal details about the user instead of a single “name” field. You may want the user’s first name, last name and username or nickname. A nice forum would also allow users to have a profile picture, email, roles, status (to allow users to be blocked), and other information like when they last visited the forum.
• Additional control related to user creation so that we can track when a new user is created but before his/her email address has been confirmed.
• Forum categories and sub-categories where each category has a subject, several moderators and additional information like creation date of the category. A subject for a post in addition the content
• Moderated postings that must be approved by a moderator before they are visible to other users. Posts and threads would have different statuses like: waiting for publication, published, reported as spam, blocked, unblocked.
• And then we might want to allow users to vote up and vote down threads and posts.

For the forum, I will use the term “thread” to refer to a conversation with potentially several postings related to the thread.

I will make an opening comment; I generally use round numbers like 100 or 1000 to define the length of varchar fields; I am not suggesting that these are necessarily the appropriate size, but am using this as shorthand, rather than leaving the length undefined (%). On the other hand, I occasionally use very specific lengths for fields like email and ip_address; 254 is the maximum length that an email address can be according to the RFC definitions, while 45 is the maximum length that an IPv6 address can be.

User Details

In Part 1 of our article series on building the online forum, user information was very limited. I will enhance the user details which are stored. For now, moderation will be very basic: users will be either be the moderator or not. We might create more complicated moderation rules related to categories and threads later on.

For the status of users, I will create the user_status table, so that I might re-use that in another situation even if there are very few statuses, like “EMAIL_NOT_VERIFIED”, “VERIFIED”, and “BLOCKED”.

User Creation

I will use the user’s status to recognize users which are in the status “EMAIL_NOT_VERIFIED” after a user has created their account and an email has been sent to their given email address, but before they have clicked on the verification URL in the email. You could even get trickier and have statuses like “EMAIL_VERIFICATION_TO_BE_SENT” and “EMAIL_VERIFICATION_RESENT” if some of these steps are handled by different components in your system and not immediately during user creation.

Thread and Post Statuses

Moderated postings must be approved by a moderator before they are visible to other users. Posts and threads would have different statuses like: waiting for approval, approved, reported as spam, blocked. For the status of threads and posts, I will choose a more flexible way of dealing with statuses by linking to the status table. Then the application must know what each value means within the status tables (if status = “APPROVED”, the thread is displayed), but I prefer this to just storing a text in the thread and post tables. We will have a few statuses, like “WAITING_FOR_APPROVAL”, “APPROVED”, “REJECTED”, “REPORTED_AS_SPAM”, and “BLOCKED”, and we might want to add more in the future.

In other words, a user creates a new thread or a new post, and it is put into status “NOT_APPROVED”. Not approved threads and posts are not displayed to most users; however, moderators can view not approved items and chose “Approve” or “Reject”. Users can mark a thread or posting as spam, but that must be confirmed by a moderator. Spam threads and posts are not displayed to users.

This allows me to use the status table for both threads and posts, as the statuses for both of these should have the same meaning. I will not misuse the status table to indicate the status of users; I don’t think that would be a good design choice.

Formal Design

So, we extend the ERD that was created in Part 1. I have colored the tables which were created in the Part 1 article in yellow and colored the newly added tables in orange so that it is easier to see the additions. However, I have not marked individual changes within tables.

What Next?

Again, there are still additional improvements to be made, but here we have taken a very simple online forum and added several useful new features.

In the next parts, I’ll look at adding other features such as:

• forum categories and sub-categories where each category has a subject, several moderators and additional information like creation date of the category. A subject for a post in addition to the content
• and then we might want to allow users to vote up and vote down threads and posts.

What features does your online forum require? Are there any features you want me to take into account when preparing the next part of this series? If so, let me know.