Polymorphic Association - is it bad?

Question

In the following schema:

Collections
   Upvotes
Reports
   Upvotes
Reviews
   Upvotes

I'm tempted to have a single Upvotes table with a single "entityId" column that stores either the CollectionId, ReportId, or ReviewId. There will also be an enum for Type - storing either collection, report, or review.

The entityId will always be required, and the logic will always make sure that inserts enforce uniqueness across each type.

The benefits of this is that adding another type is just a matter of expanding the enum. Everything will live on a single table with no redundancy.

The cost seems to be the added complexity on the logic side, which is contained to the one place in my application logic that inserts new entities into the table.

Practically speaking, is there anything wrong with this approach? What would be some other reasons to avoid this?

Answer 1

I can't recommend polymorphic associations.

In my experience, this will lead to data consistency issues since you can't use a foreign key constraint in the schema. That means you're reliant on your application's writes to this part of the database being bug-free, and no one manually altering the data.

It can also make the queries which join Upvotes to >1 of the other tables more complicated/harder to understand.

Answer 2

Yes, it can be a good idea. But you still have some issues.

You need to enforce the type column across tables. The way to do this is by using a multi-column primary key, which you use to foreign-key against. And the child tables should have the type column as a computed column (or a CHECK enforcing a single value).

CREATE TABLE Entity (
    Type tinyint NOT NULL CHECK (Type BETWEEN 1 AND 3),
    Id int NOT NULL UNIQUE,
    -- common columns here
    PRIMARY KEY (Type, Id)
);

CREATE TABLE Collections (
    EntityType AS CAST(1 AS tinyint) PERSISTED,
    EntityId int NOT NULL,
    -- collections columns here
    FOREIGN KEY (EntityType, EntityId) REFERENCES Entity(Type, Id)
);

-- etc other entities

CREATE TABLE Upvotes (
    EntityType tinyint NOT NULL,
    EntityId int NOT NULL,
    UserId int NOT NULL REFERENCES Users (Id),
    -- votes colums here
    FOREIGN KEY (EntityType, EntityId) REFERENCES Entity(Type, Id)
);

db<>fiddle

---

Having said that, it depends on whether there are differences in the Upvotes for each entity. For example, if Upvotes for Collections has a very different design from Upvotes for Reports then it wouldn't make sense to do this, you may as well have separate votes tables.