I wouldn’t say I’m an expert in programming, but I know quite alot. I’m currently designing a database-like site, and I had a question regarding the speed or functionality of things.
For every entry there will be comments. And one thing I want to be able to do is let people flag comments as inappropriate. These comments would show up in a special place for mods and admins so they don’t have to browse every comment to see if it’s flagged, but that’s pretty obvious I guess.
Anyway, I thought of two ways to do this, and wondered which one is better?
I can make a column in my comments database table, ‘is_flagged’, that would be off by standard, and when a user flags it, it will be on. When a mod goes to the section where all the flagged comments show up, it would search every single comment and test if their ‘is_flagged’ property is on.
I can make a new table called flagged_comments. Every time a user flags a comment, a new entry would be created. Each entry has the comment_id. When a mod goes to the section where al the flagged comments show up, it would list all entries in this table (that haven’t been closed yet, I could make another column here with is_closed, when handled by a mod).
I can see both ways working fine, but when thinking about it, I’d guess the second option is the faster one for mods, as it does not have to browse every comment to check if it’s flagged.
However, I’d have an additional table that just lists the comment_id’s. After this I would open up the comments table anyway, and have it search for all comment_id’s. So wouldn’t this actually take longer? Or does it take much less time to search for an index key (comment_id) than it would when searching for a certain column (is_flagged)?
This ofcourse does not just apply to this situation (flagging comments), but many others aswell. I’d like to hear opinions, or facts, about this