Mysql – Need help making a sane database schema

database-designMySQLsubtypes

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.

Best Answer

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

Related Question