CODEWITHSUNDEEP
databasedatabase-designreporting
Sébastien
Sébastien

Reputation: 1697

A single table to keep reports for different type of information

I have many kind of information like 'messages', 'comments', 'articles', 'reviews', ... stored in different tables in my DB.

I would like to make a 'report' functionnality for each one of them but I don't want overload the DB and limit the amount of resulting work.

So I would like to know what would be a good DB design to keep all these user reports in only one DB.

Thanks in advance, Sébastien

EDIT: By report, I mean report abuse like racism, violence, ... in messages or comments, ...

Upvotes: 1

Views: 273

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

I guess you mean you want to keep all these user reports (not in one DB but) using only one table or at least a small number of tables.

Possible solution:

Add a new Information table that will act as a supertype for the Message, Comment, Article, Review, etc. tables (which will be the subtypes):

Information
-----------
InformationId
PRIMARY KEY (InformationId)

Add a new column InformationId in Message table that is a FOREIGN KEY to Information:

Message
-------
MessageId                  --- no changes here
... other data             --- or here

InformationId                        --- one additional column

PRIMARY KEY (MessageId)    --- no changes here
...                        --- or here

UNIQUE KEY (InformationId)           --- so every message, article, comment 
FOREIGN KEY InformationId            --- or review can be identified
  REFERENCES Information (InformationId)

Do the same for all other "information" tables, like Comment, Article, Review, etc.

Then add a Report table:

Report
------
ReportId                     
InformationId              --- which message, article, etc is reported
ReporterId                 --- who reports it
ReportType                 --- what type of abuse it is (racist, etc.)
DateOfReport 
Explanation
PRIMARY KEY (ReportId)      
FOREIGN KEY InformationId
  REFERENCES Information (InformationId)
FOREIGN KEY ReporterId
  REFERENCES Person (PersonId)              --- the User table

Upvotes: 1

Yoni Baciu
Yoni Baciu

Reputation: 2707

If your database is relatively small I would just process the reports on-the-fly instead of persisting them in a DB.

If you still want to persist your reports in the DB and you absolutely need to keep it in one table only, I would serialize the report data in some format like XML or JSON and keep it in a big TEXT or BLOB field. So your table will have two columns: report_id and report_data.

Yes another option is to use some OLAP solution/reporting engine that will store your reports (or partial report data) and leave your normalized DB nice and clean.

Upvotes: 0

Related Questions