I came across this 37signals podcast describing how they manage polymorphism across their projects and it made me want to share how we do it.

From what the docs describe, they have a generic base table that along with generic columns stores an inheritor's id and the table name of that specific inheritor. Something like

model base {
    id
	created_at
	updated_at
	...
	inheritor_id 
	inheritor_type
}

model inheritor_a {
	id
	something_only_a_has
	...
}

model inheritor_b {
	id
	something_only_b_has
	...
}

a = insert into inheritor_a (something_only_a_has) values (some_a_value) returning *
insert into base (inheritor_id, inheritor_type) values (a.id, 'inheritor_a')
b = insert into inheritor_b (something_only_b_has) values (some_b_value) returning *
insert into base (inheritor_id, inheritor_type) values (b.id, 'inheritor_b')

Now they can modify any inheritor_X independently of base and because any inheritor_X is much smaller than base and concerns are separated it's easier to work with. I agree. It's relatively "yay."

We landed on a similar pattern for our polymorphism. Only I wasn't happy with the main drawback: when you want to query over all bases you need to join with and retrieve every inheritor table/row. If you have more than a few inheritor types, it's super unwieldy.

To get all base in a single query regardless of type using their pattern:

select *
from base
join inheritor_a on base.inheritor_type = 'inheritor_a' and base.inheritor_id = inheritor_a.id
join inheritor_b on base.inheritor_type = 'inheritor_b' and base.inheritor_id = inheritor_b.id
...
join inheritor_z on base.inheritor_type = 'inheritor_z' and base.inheritor_id = inheritor_z.id

They trade read performance for DX. What we do to avoid that is establish triggers on insert/updates to inheritor tables such that they denormalize themselves as JSON into a column of base.

model base {
    id
	created_at
	updated_at
	...
	inheritor_type
	inheritor_json
}

model inheritor_a {
	id
	base_id
	something_only_a_has
	...
}

create trigger inheritor_a_denorm
on insert or update of inheritor_a
execute 
	update base set 
	inheritor_json = to_json(NEW.*), 
	inheritor_type = 'inheritor_a' 
	where base.id = NEW.base_id

model inheritor_b {
	id
	base_id
	something_only_b_has
	...
}

create trigger inheritor_b_denorm
on insert or update of inheritor_b
execute 
	update base 
	set inheritor_json = to_json(NEW.*),
	inheritor_type = 'inheritor_b' 
	where base.id = NEW.base_id

base_a = insert into base returning *
insert into inheritor_a (base_id, something_only_a_has) values (base_a.id, some_a_value)
base_b = insert into base returning *
insert into inheritor_b (base_id, something_only_b_has) values (base_b.id, some_b_value)

Now we can query base generically without joins.

select *
from base

AFAICT we get the best of single table inheritance (STI) and "delegated types":

1. typed and constrained data at rest
2. concerns separated
3. join-free generic queries over base

For the tradeoff of:

1. marginally slower write performance
2. two copies of all inheritor rows
3. hypothetical type incompatibility among SQL and JSON (I haven't run into that yet)

We only use this pattern for wallets because that's when we discovered this pattern. Eventually all of our polymorphic tables (e.g. items) will follow it.