Modeling a Basic Data Structure to Manage Users, Threads, and Posts

An online discussion forum is a site where people can hold conversations in the form of posted messages. Discussion forums allow conversations to take place when people are not on-line, and messages may be temporarily archived. Also, depending on the forum set-up, a message might need to be approved by a moderator before it becomes visible to other users. Forums have a specific set of terms, for example, a single conversation is generally referred to as a “thread”. Threads can be replied to by as many people as would like to. Most forums require users to register and subsequently log in to post messages. However, on most forums, users do not have to log in to read existing messages.

These are common requirements for a website forum, so let’s work to make a database model starting with a basic forum setup which we can extend later.

Entities

As mentioned previously, people hold conversations in the form of posted messages that are grouped in conversations. So the database needs to support information about people or users, conversations or threads and messages or posts.

For the online forum, users will need to be created – it might be necessary to allow users to edit their details after they have been created, but there is probably no need to allow deletion of users. Users will create conversations, or threads, generally by posting a first message on to the forum, rather than explicitly creating a conversation, but it might be necessary to support both possibilities. Threads will contain posts which are created by users.

Functionality

Posting of messages is the main functionality of the online forum. Should users be able to edit messages after they have been posted? Should users be able to delete messages after posting? The answer to both is probably yes.

Relationships

Posts are linked to threads and posts are created by users, so there will be a defined relationship between users and posts. Threads are created by users so you should store a relationship there (for example, if a user is banned from the forum, you might want to remove all the threads and posts that that user created).

Now that you know the entities and relationships, you are ready to build up a model of the database, and that should only take minutes. This is where tools come in handy.

Database Model

Let’s take a look at Vertabelo to continue the online discussion forum.

Create a model for the online forum and start adding in the entities representing users, threads, and posts. Vertabelo will remind you that you need to define primary keys for each table; I recommend using id columns as that will give you more potential flexibility (and won’t tie you to the name of the user as the primary key or other pitfalls). Next, add in the relationships that we already described previously. I have added a password (in hashed form) for the user to control login of users.

Now, you’ll end up with something that looks roughly like this:

You will see that there are timestamps with the date/time of the creation of each row, so that that information can displayed on the forum (“User since February 2, 2014”, “Date posted: December 24, 2014”, etc.).

If you are going to have a moderated online discussion forum in which messages must be approved by a moderator before they become visible to other users, then you should add a status item to threads and posts so that the application can decide if the threads and associated posts have been reviewed and approved. A status would also allow other users to mark threads and posts as spam or inappropriate.

I hope that you can already start thinking about improvements. For example, if you need formal details about the user, instead of a single “name” field, you may want their first name, last name and username or nickname. And a post might have a subject in addition to the content, but I think that the subject of the post is linked back to the thread so that might not be necessary.