1

I am writing a backend for an application that has 4 main types of content: a message, a post, a thread, and a thread reply. Right now, these are all stored in separate MySQL tables: a table for each type of content.

I would like to introduce a moderation system into my app. To do this, I will allow users to report/flag a piece of content. In the database, each report should link back to the original piece of content, so that I can retrieve information about the reported content as needed. However, this requires yet 4 more tables: message report, post report, thread report, and reply report. Each of these tables will be exactly the same except for a foreign key column to reference the correct piece of content (a message, post, thread, or reply).

Using this system, reports will be saved in a many-to-one fashion, meaning that 1 single piece of content can have multiple reports associated with it. To further complicate things, I also need to be able to collect data on a single piece of content. For instance, say thread with ID 23 got many reports, but a moderator comes along and marks the content as safe. I need to be able to somehow mark all the reports for thread ID 23 as 'safe'. This seems difficult to wrap my head around, and perhaps I need another table to track this kind of information.

Does anyone have any recommendations for how to improve/design a sane database schema for this scenario? I feel as though I am doing something wrong and it is making this far too complicated.

  • read up on table inheritance, aka subtype/supertype relationships – Neil McGuigan Nov 26 '15 at 19:58
0

Most entities need a thread_id.

If you want "in reply to", you need a link into the same message/comment/post table as parent_id; use NULL or 0 to indicate start of chain.

Since I don't know your distinction between message and comment and post, I can't judge whether to put them in 1, or 2, or 3 tables; it depends on whether they have enough differences. But Threads should be a separate table that discusses common info about the set of messages/comments/posts.

safe (and questioned), as you described it, belong in Threads. But you might want a flag(s) on the other table(s) for censoring specific items, too.

Code that displays a thread (or one item in the thread) would need to check safe and the user_id to decide whether this user can see it. (Presumably, user_id='admin' can see everything.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.