On Tue, Aug 17, 2021 at 8:41 AM Jack Christensen <jack@jncsoftware.com> wrote:
>
> The only way to ensure a user can only be a member of a group in the same
> tenant is to user_group_memberships.tenant_id be part of the foreign key. And
> that will only work with a unique key on id and tenant_id in both users and
> user_groups. It's a bit inelegant to create multiple extra indexes to ensure
> consistency when existing indexes are enough to ensure uniqueness.
>
> Jack
You could accomplish this by using composite primary keys on the users and
user_groups tables:
CREATE TABLE users (
id serial,
tenant_id int REFERENCES tenants(id),
PRIMARY KEY (tenant_id, id)
);
This approach works pretty well for multi-tenant databases, because then your
indexes all start with tenant_id, which should help with performance, and, in
theory, would make your database easier to shard. But then it requires
including a tenant_id in *every* query (and subquery!), which may be difficult
to enforce in a codebase.
One downside of the composite primary/foreign key approach is that ON DELETE
SET NULL foreign keys no longer work properly because they try to set both
columns to NULL, the true foreign key id, AND the shared tenant_id that is part
of the referencing table's primary key. I have a patch [1] out to add new
functionality to solve this problem though.
- Paul
[1]:
https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV%3DnjbSMxf%2BrbDHpx%3DW%3DB7AEaMKn8dWn9OZJY7w%40mail.gmail.com
