So I am designing a multitenant platform database in PostgreSQL. A user belongs to an org through a membership and can hold several roles in it.
When drafting the `memberships` and `membership_roles` tables, I found myself with two ways of handling the primary and foreign keys:
Design A (Composite Keys):
CREATE TABLE memberships (
org_id uuid REFERENCES organizations(id),
user_id uuid REFERENCES users(id),
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
status membership_status DEFAULT 'active',
PRIMARY KEY (org_id, user_id)
);
CREATE TABLE membership_roles (
org_id uuid,
user_id uuid,
role_id INT REFERENCES roles(id),
FOREIGN KEY (org_id, user_id) REFERENCES memberships(org_id, user_id),
PRIMARY KEY (org_id, user_id, role_id)
);
Design B (Surrogate Key):
``` CREATE TABLE memberships (
id SERIAL PRIMARY KEY,
org_id uuid REFERENCES organizations(id),
user_id uuid REFERENCES users(id),
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
status membership_status DEFAULT 'active',
UNIQUE (org_id, user_id)
);
CREATE TABLE membership_roles (
membership_id INT REFERENCES memberships(id),
role_id INT REFERENCES roles(id),
PRIMARY KEY (membership_id, role_id)
);
```
From what I can tell so far, both enforce the exact same constraints. Design A makes it easier to filter or partition by org since `org_id` lives directly on the child rows, whereas Design B gives a clean single-column handle for other tables and APIs at the cost of requiring a join to get `org_id`.
Generally, when there are multiple ways to model something like this, there is often a subtle catch down the road. Between these two, is there any significant difference or impact I might be missing, especially around performance at scale, general maintenance or anything really? Which one would you pick, and why?