I just hope that PostgreSQL becomes able to do something as conveniently (keyword, "conveniently") as this within my lifetime:
CREATE TABLE permission_overrides (
-- (...)
granted_by_user_id BIGINT NOT NULL REFERENCES users (id),
granted_to_user_id BIGINT NOT NULL REFERENCES users (id),
-- Ensure both users belong to the same tenant.
CHECK (DEREFERENCE(granted_by_user_id).tenant_id = DEREFERENCE(granted_to_user_id).tenant_id)
);
(Or whatever similarly-convenient variation we want to bikeshed about.)
I understand there are performance implications[1] with such a thing, but being able to prevent invalid data from even existing in the database is one of the main reasons I use a database instead of some dumb store, because I don't want to have weird data the moment the application has a major change (or worse, a rewrite).
[1]: That specific example looks like it would be slow as hell unless that table is mostly append-only. (EDIT: And also that the referenced rows don't change too much.)
While there are lots of cases where referencing "through" foreign key relations would be useful, this specific example is one for which dereferencing & check seems to be LESS convenient that what is available today with just composite foreign keys.
I understand there are performance implications[1] with such a thing, but being able to prevent invalid data from even existing in the database is one of the main reasons I use a database instead of some dumb store, because I don't want to have weird data the moment the application has a major change (or worse, a rewrite).
[1]: That specific example looks like it would be slow as hell unless that table is mostly append-only. (EDIT: And also that the referenced rows don't change too much.)