Need help making a sane database schema

Question

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.

Answer 1

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.)

Answer 2

If you have four existing tables and need the same data in the report I can think of two possibilities without changing the existing tables:

using a discriminator column

Using just one table but using a discriminator column in the report table. The foreign key then is made of discriminator which tells you which of the four tables is referenced and the id of the row in that table.

Then you can find/update all reports to a thread/etc by filtering by discriminator and the id.

However that doesn't allow for a foreign key constraint

Using a parent table

Each of the four tables have a foreign key to a table reportable and reports reference to that table.

To find out to which table a reportable belongs to you'd need to look into all four tables however (unless you also add a discriminator which would be redundant information then).

However you can use foreign key constraints that way