Adding More Advanced Features Like Managing Categories and Voting on Threads and Posts

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

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

To be able to grasp the model more easily, we have prepared an example of such a forum with categories, threads, posts, etc. We hope that this makes it easier to understand things:

Entities from Part 1 are colored in yellow and entities from Part 2 are colored in orange. Here is the current database model after the first two articles:

More Details on Posts

In the second article, I only added relationships to “status” for threads and posts, but now I would like to enhance the posts with an additional detail beyond just simple content. For now, I will add a “subject” for each post which could be different from the “subject” of the thread. With the “subject” (think of the subject line on an email or the subject of an MMS message), we can display a condensed version of the content – like the first lines of an email that is shown in Outlook, or on your phone’s mail. Also, users can use a post’s “subject” to record a high-level comment like “I disagree” and then put their arguments in the comment section of the post. This is a nice simple change to the model.

Categories

Now that we have warmed ourselves up, let’s add a new concept (entity) for the categorization of threads and posts. Each category will be defined with a name; each category is created by a user and will have at least one moderator among the users, plus a few other attributes like the posts and threads we already have. We would like categories and sub-categories, so we will allow categories to refer to other (sub-) categories.

As I would like to have multiple moderators on each category, I will make a table to link categories to groups of users, and link users to those groups. This table will define “groups” of users, one of which will be “moderators”, but in the future, this could be expanded for other types of groups.

Voting or “Likes”

Personally, I prefer to “vote up” or “vote down” on something rather than to simply “like” it (but I guess Mark Zuckerberg disagrees with me on that). I will choose to create a voting mechanism which allows users to cast a “vote up” or a “vote down”. We might choose to disallow down votes when there are no up votes, or limit the number of down votes, but that is up to the application to decide, the database will simply keep track of the count of up and down votes.

We might create separate tables for votes on threads and votes on posts, but I will keep them in the same table with optional foreign keys from the threads and posts table. In addition, I choose not to have voting on categories.

Formal Design

For now, that wraps up the changes on the model, so we can look at the formal design. Again, we extend the ERD that was created in Parts 1 & 2. Changes described in this article and introduced into the model are colored in blue.

Conclusion

There are still additional improvements that could be made, but here we have made our database for an online forum into a fairly complete design – this would support most typical online discussion forums.

What other features does your online forum require?