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.