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?

You may also be interested in: [Implementing comments and Likes in database](http://stackoverflow.com/a/8113064/533120). — Branko Dimitrijevic, Dec 11 '13 at 22:37
[Data Model of SO](http://meta.stackexchange.com/questions/2677/database-schema-documentation-for-the-public-data-dump-and-sede) maybe a good reference for your problem. — Wuaner, Apr 21 '16 at 03:51

score 10 · Accepted Answer · answered Dec 08 '13 at 16:27

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.

Yeah, the first approach is what I was considering - a single "EntityId" field that gets used to query against whatever table corresponds to the ENUM. The common parent approach - would you mind explaining this a bit further, or even a link that demos the concept would be helpful. — RobVious, Dec 08 '13 at 16:33
Also, I want to add that from practicability perspective having everything normalized and enforced should not be the goal by itself; consider time and complexity of each solution. — a1ex07, Dec 08 '13 at 16:50
Fantastic, I just ordered that book. Thank you for your help. — RobVious, Dec 08 '13 at 16:54
Wouldn't the common parent approach (child.Parentid) limit the relationship between child and parent to be one-to-one instead of one-to-many? Or, am I misunderstanding? — HoldOffHunger, Jul 14 '16 at 01:26

score 5 · Answer 2 · answered Dec 08 '13 at 16:11

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.

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

2 Answers2