I was working on a forum component for a bigger project and considered adding in a hellban feature where a mod may prevent a user's posts from being viewed by anyone but that user. This basically enforces the "don't feed the troll" rule, forcing everyone to ignore the troublemaker. Meanwhile the troublemaker likely becomes bored as he doesn't succeed in getting a rise out of anyone and hopefully moves on.
My first thought was to add in a "hellbanned" column in a post table, and create a "hellbanend" table. A hellbanned user would have their user_id added as a record to the hellbanned table, and henceforth all their future posts would have their hellbanned column set to true.
So a query showing all a topic's posts would simply show all posts where 'hellbanned = False'. And, a post operation would check if the user was in the hellban table, and if so, set the post's 'hellbanned' column to True.
I can't help but thinking there is a better way to do this; I'd really appreciate some suggestions.
Hellbanning exists at the level of the user, not individual posts, so you don't have to keep the flag at the level of the posts table at all - in fact, doing that would open you to data inconsistencies (e.g. an application bug may lead to an "incompletely" hellbanned user).
Instead, put hellbanned user ID to a separate table (and if your DBMS supports it: cluster it to avoid "unnecessary" table heap)...
CREATE TABLE HELLBANNED_USER (
USER_ID INT PRIMARY KEY,
FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID)
)
...and when the time comes to exclude the hellbanned user's posts, do it similarly to this:
SELECT * FROM POST
WHERE USER_ID NOT IN (
SELECT USER_ID FROM HELLBANNED_USER
)
This should perform nicely due to the index on HELLBANNED_USER.USER_ID.
The hellbanned users are still in the regular USER table, so everything else can keep working for them without significant changes to your code.
Obviously, once the user is hellbanned above, all of its posts (even those that were made before hellbanning) would become invisible. If you don't want that, add a HELLBANNED_DATE field to the hellbanned table and then hide the posts after the hellbanning similarly to...
SELECT * FROM POST
WHERE NOT EXISTS (
SELECT * FROM HELLBANNED_USER
WHERE POST.USER_ID = HELLBANNED_USER.USER_ID
AND POST_DATE >= HELLBANNED_DATE
)
Alternatively, you could just keep the HELLBANNED flag (and/or HELLBANNED_DATE) in the USER table, but you'd need to be careful to index it properly for good performance.
This might actually be a better solution than the HELLBANNED_USER, if you need to JOIN with the USER anyway (to display additional user information for each post), so the flag is readily reachable without doing the additional search through the HELLBANNED_USER table.