MetaMove is my fitness coaching app: trainers build workout and meal plans, members follow them and log sessions. It runs on Postgres through Drizzle, and I designed the schema the way most app developers do, which is to say quickly and by feel. Now that I’m learning relational design properly, I’m auditing that schema one table at a time and writing down what I find.
First table: member_assignments, the roster of which members belong to which trainer. It has a surrogate id, and the textbook told me junction tables shouldn’t. It turned out the id was the least of its problems.
export const memberAssignments = pgTable("member_assignments", {
id: uuid("id").primaryKey().defaultRandom(),
memberId: text("member_id").notNull(),
workoutPlanId: uuid("workout_plan_id"),
trainerId: text("trainer_id").notNull(),
assignedAt: timestamp("assigned_at", { withTimezone: true })
.defaultNow()
.notNull(),
startDate: date("start_date")
.notNull()
.default(sql`CURRENT_DATE`),
endDate: date("end_date"),
status: text("status")
.$type<"active" | "completed" | "paused">()
.default("active"),
})What a junction table is
A trainer has many members. Over time, a member can have many trainers. That is a many-to-many relationship, and neither side can hold it: a column stores one value, so user.trainer_id would cap every member at one trainer forever, and user.member_ids is a list pretending to be a column.
The relational answer is a third table where each row is one pair. That’s a junction table (also called an association, bridge, or link table). The textbook version has two columns, both foreign keys, and together they are the primary key:
CREATE TABLE trainer_members (
trainer_id text NOT NULL REFERENCES "user"(id),
member_id text NOT NULL REFERENCES "user"(id),
PRIMARY KEY (trainer_id, member_id)
);No id column. The pair is the identity, and the composite primary key means the same pair can’t appear twice, which is exactly the rule you want for a plain roster.
So does mine need an id?
The rule I landed on: ask whether the pair is the identity, or the row is.
Look at the other columns on my table: assigned_at, start_date, end_date, status. Those don’t describe a pair. They describe an assignment, something that starts, gets paused, and ends. Ana can be Rey’s member in March, complete the programme, and come back in September. That is two rows with the same pair, and a composite key of (trainer_id, member_id) makes the second one impossible.
So the pair is not the identity here. The assignment is. It’s an entity with a lifecycle, and an entity gets its own key. The id stays.
That was the easy answer. The harder question followed straight after: if the pair isn’t unique, what is the rule? Because right now the table has none. I checked:
INSERT INTO member_assignments (member_id, trainer_id) VALUES ('m1', 't1');
INSERT INTO member_assignments (member_id, trainer_id) VALUES ('m1', 't1'); member_id | trainer_id | status | count
-----------+------------+--------+-------
m1 | t1 | active | 2Two active assignments for the same member. My assignMember mutation doesn’t check for this either. It looks the member up by email and inserts.
The rule the table actually needs
Once I said it out loud it was obvious: a member has at most one active assignment. Not one per pair. One per member, while it’s active. Completed and paused rows can pile up as history.
Postgres has a precise tool for “unique, but only among some rows”: a partial unique index.
CREATE UNIQUE INDEX member_assignments_one_active_per_member
ON member_assignments (member_id)
WHERE status = 'active';Now the second active insert fails:
ERROR: duplicate key value violates unique constraint "member_assignments_one_active_per_member"
DETAIL: Key (member_id)=(m1) already exists.Mark the first one completed, insert again, and it goes through. Both rows stay, which is the history I wanted. And because the database enforces it, two trainers clicking “assign” at the same moment can’t both win; one gets the error, no application-level check required.
And then I noticed there were no foreign keys
While writing that index I opened the migration drizzle-kit had generated for the whole schema. I searched it for FOREIGN KEY. Two hits, both from Better Auth’s own tables (session and account pointing at user). Every table I wrote myself had none. member_id text NOT NULL will accept any string you give it.
INSERT INTO member_assignments (member_id, trainer_id) VALUES ('nobody', 't1');
-- INSERT 0 1I know why I thought I had them. I had written this:
export const memberAssignmentsRelations = relations(
memberAssignments,
({ one }) => ({
member: one(user, {
fields: [memberAssignments.memberId],
references: [user.id],
}),
})
)That looks like a relationship, and in one sense it is. relations() is a map for Drizzle’s query builder, so that db.query.memberAssignments.findMany({ with: { member: true } }) knows which columns to join on. It emits no SQL. The database never hears about it. A foreign key is a different thing entirely, and in Drizzle it lives on the column:
memberId: text("member_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),With that in place, the orphan is refused:
ERROR: insert or update on table "member_assignments" violates foreign key constraint "member_assignments_member_id_user_id_fk"
DETAIL: Key (member_id)=(nobody) is not present in table "user".What a foreign key buys you
Three things, and only the first one is the obvious one.
No orphans. A row can’t point at a user that doesn’t exist, whether the bad id came from a bug, a typo in a script, or a user that was deleted last week.
A decision about deletion, made once. Every foreign key has to say what happens when the row it points at goes away. For this table I chose: delete a member and their roster rows go with them (cascade); deleting a trainer who still has members is refused (restrict); deleting a plan just blanks the reference (set null). Postgres then holds the line for me:
ERROR: update or delete on table "user" violates foreign key constraint "member_assignments_trainer_id_user_id_fk" on table "member_assignments"
DETAIL: Key (id)=(t1) is still referenced from table "member_assignments".What should disappear when a user is deleted is a bigger question than one table, so it gets its own post later in this series.
Documentation the tools can read. Drizzle Studio, pg_dump, and any diagramming tool draw the lines between tables from foreign keys. With none, my schema looked like fourteen islands.
The fix, in Drizzle
export const memberAssignments = pgTable(
"member_assignments",
{
id: uuid("id").primaryKey().defaultRandom(),
memberId: text("member_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
trainerId: text("trainer_id")
.notNull()
.references(() => user.id, { onDelete: "restrict" }),
workoutPlanId: uuid("workout_plan_id").references(() => workoutPlans.id, {
onDelete: "set null",
}),
// assignedAt, startDate, endDate, status unchanged
},
(t) => [
uniqueIndex("member_assignments_one_active_per_member")
.on(t.memberId)
.where(sql`${t.status} = 'active'`),
]
)The relations() block stays. It still does its job for the query API. The two are complementary: .references() tells Postgres, relations() tells Drizzle.
One practical warning before you run the migration on real data: adding a foreign key to a table that already contains orphans fails on the spot. Find them first.
SELECT id, member_id FROM member_assignments
WHERE member_id NOT IN (SELECT id FROM "user");What I’d tell past me
A junction table that holds only the pair gets a composite primary key and no id. A junction table with a lifecycle isn’t really a junction table; it’s an entity, so keep the id, and then write down the uniqueness rule the pair does obey, which is often “one active row per something” and a partial unique index.
And relations() is not a foreign key. If the generated SQL has no REFERENCES in it, the database knows nothing about your relationships, no matter how good the TypeScript looks.
Next in this series: this same table is quietly doing two jobs, which is why workout_plan_id is nullable.