Modeling Upvotes / Likes - one table per type, or one big table?

Question

I'm modeling a system that will have "upvotes" across various entity types - Reports, Reviews, and Collections.

These are the only three entities that can be upvoted, though there may be more in the future.

Currently, the db schema has ReportUpvotes, ReviewUpvotes, and CollectionUpvotes.

I'm wondering if it would be better to bring all of these tables together into a single Upvotes table, with an enum for Type.

What should I be considering when evaluating this kind of decision?

Answer 1

The idea of storing entities of the same type (Upvotes) in one table makes perfect sense to me. However, it may be implemented in different ways. Right of the bat I can tell about 3 ways.

The first one that is proposed in the question (unless I misunderstood it) results in polymorphic association, a practice generally considered as bad. Correct me if I'm wrong, but it seems that you want to have something like CREATE TABLE Upvotes (..., type enum ('Report','Review',...), entity_id int) where entity_id refers to one of the Report,Review, etc tables based on value of type column. There is no way you can enforce such constraint without triggers.

Second is exclusive arcs. A bit better approach (referential integrity can be enforced), but still quite ugly. If you go this way, you will have something like

CREATE TABLE Upvotes(..., report_id INT , review_id INT, ...., 
CONSTRAINT FK_REPORT FOREIGN KEY (report_id) REFERENCES Report(report_id), 
CONSTRAINT FK_REPORT FOREIGN KEY (review_id) REFERENCES Review(review_id)
);

First of all you need to ensure that only one of (report_id,review_id, etc) is not null for any row. Secondly, adding new entity type that can be upvoted means adding new column to Upvotes.

The third way is a "common parent" approach. You are creating a new table, say UpvotableEntity (bad name, just for illustration). Then make it parent table for existing ReportUpvotes, ReviewUpvotes, and CollectionUpvotes. Finally, Upvotes table stores upvotable_entity_id.

Answer 2

Just like in software development, requirements should strongly influence what you should do.

Do you need to be able to display all upvotes/likes together or in one place?

Do you envisage adding many more types of upvotes?

Answering yes to either of these two would make me lean towards a single table.

Apart from this, just like in software development, if tables are very similar in structure and purpose, they can be "refactored" into a single table. For this reason also, I would lean towards having a single table.