Polymorphic associations are helpful to development, but there are 3 big downsides I have found as an SRE supporting Rails apps:
Using the database’s referential integrity constraints is impossible, leading to possible data integrity problems.
Index sizes bloat to 2-3x, because the “type” string has to be included in each index tuple.
Activerecord requires an extra query, because it doesn’t JOIN the tables like it would with a normal FK. It queries the child record, then based on the “type”, formulates a new query for the “parent” record. This means more time spent in database calls, which adds up at scale.
I have been thinking about different strategies to migrate away from a polymorphic association.
As a first step, I’ve written some LEFT JOIN queries. The downside there is when the query is written, it needs to know about each of the possible association types. This maybe isn’t a problem for a mature app though.
Another option could be to have multiple FKs, all of which are NULL except one, as described in another comment. That’s probably the best option.
More experimentally, I’ve been mulling over a way to use Postgres’s table inheritance somehow. I’d love any thoughts from folks who have gone down that road.
Rails isn’t really my bag so perhaps this type of thing is normal for you guys….
After reading this I understood the concept, and had one question, because the article didn’t mention it, and I was fairly sure I already knew the answer….
It was shocking to me how many articles/pages mentioning “polymorphic” and “foreign key” (in relation to Rails/active record) I went through before I found one that acknowledged the elephant in the room, the question I already knew the answer to…
I think it’s OK to have concepts like this at the application level, when lower data integrity is acceptable for a certain use case, or when doing rapid prototyping and things are likely to change rapidly.
But continuing to use the term foreign key to describe something that literally cannot have an actual enforced foreign key constraint, with (from my search) very little acknowledgment of the caveat, is disingenuous at best.
But like I said, I don’t really do Rails so maybe this is par for the course in that community?
It’s par for the course. Rails defaulting to using foreign key constraints is relatively new. Most developers relied on the ORM to handle deleting dependent records.
For myself, I use foreign key constraints as much as possible. When it comes to polymorphic foreign keys, I try to restrict them to one side of a join table and put a unique index on (right_table, right_id) and a constraint on (left_id). That way the rows will only be orphaned on one side.
You could effectively create a foreign key with a constraint trigger, but I’m not sure the constraint itself would be truly polymorphic. That said, I’ve never actually seen this done. Polymorphic relationships are one of my big gripes with rails for the reasons you outlined along with the fact that they tend to be abused because the threshold for using them is so low.
pretty sure you can also implement this with an extra “kind” column, N nullable foreign-keys, and a check constraint that verifies which fkey should be null or not depending on the kind column:
create table admin (
admin text primary key,
age int not null check (age > 0)
);
create table guest (
guest text primary key,
invited_by text not null references admin(admin),
age int not null check (age > 0)
);
create table comment (
comment text not null,
author_kind text not null check (author_kind in ('admin', 'guest')),
admin text references admin(admin),
guest text references guest(guest),
author text generated always as (coalesce(admin, guest)) stored, -- optional
constraint "valid_polymorphic_author" check (
case author_kind
when 'admin' then admin is not null and guest is null
when 'guest' then guest is not null and admin is null
end
)
);
Is this polymorphic relationship just to require one less join table, or is there a real need in the problem domain? Is the real intent here to support both a user and an account to both be members of a collaboration group?
Polymorphic associations are helpful to development, but there are 3 big downsides I have found as an SRE supporting Rails apps:
I have been thinking about different strategies to migrate away from a polymorphic association.
As a first step, I’ve written some LEFT JOIN queries. The downside there is when the query is written, it needs to know about each of the possible association types. This maybe isn’t a problem for a mature app though.
Another option could be to have multiple FKs, all of which are NULL except one, as described in another comment. That’s probably the best option.
More experimentally, I’ve been mulling over a way to use Postgres’s table inheritance somehow. I’d love any thoughts from folks who have gone down that road.
Rails isn’t really my bag so perhaps this type of thing is normal for you guys….
After reading this I understood the concept, and had one question, because the article didn’t mention it, and I was fairly sure I already knew the answer….
It was shocking to me how many articles/pages mentioning “polymorphic” and “foreign key” (in relation to Rails/active record) I went through before I found one that acknowledged the elephant in the room, the question I already knew the answer to…
I think it’s OK to have concepts like this at the application level, when lower data integrity is acceptable for a certain use case, or when doing rapid prototyping and things are likely to change rapidly.
But continuing to use the term foreign key to describe something that literally cannot have an actual enforced foreign key constraint, with (from my search) very little acknowledgment of the caveat, is disingenuous at best.
But like I said, I don’t really do Rails so maybe this is par for the course in that community?
It’s par for the course. Rails defaulting to using foreign key constraints is relatively new. Most developers relied on the ORM to handle deleting dependent records.
For myself, I use foreign key constraints as much as possible. When it comes to polymorphic foreign keys, I try to restrict them to one side of a join table and put a unique index on (right_table, right_id) and a constraint on (left_id). That way the rows will only be orphaned on one side.
You could effectively create a foreign key with a constraint trigger, but I’m not sure the constraint itself would be truly polymorphic. That said, I’ve never actually seen this done. Polymorphic relationships are one of my big gripes with rails for the reasons you outlined along with the fact that they tend to be abused because the threshold for using them is so low.
pretty sure you can also implement this with an extra “kind” column, N nullable foreign-keys, and a check constraint that verifies which fkey should be null or not depending on the
kindcolumn:queriying is more complicated but doable using (for example):
Is this polymorphic relationship just to require one less join table, or is there a real need in the problem domain? Is the real intent here to support both a user and an account to both be members of a collaboration group?