Part 1 ended on a loose thread: workout_plan_id on member_assignments is nullable, and I promised that was a clue. Pulling on it unravelled the table.

The two jobs

Here is what actually happens in the app. When a trainer types a member’s email into “Assign member”, the mutation looks the user up and inserts a row with member_id, trainer_id, and status: "active". No plan. That row means “Ana is Rey’s member”. It is the roster.

Later, when Rey puts Ana on his eight-week strength programme, the same table is supposed to get a row with workout_plan_id filled in. That row means “Ana is following this plan”. It is a plan assignment.

Two different facts, one table, told apart by whether one column is null. And then there is meal_plan_assignments, which I created by copying the whole table and renaming one column:

ts
export const mealPlanAssignments = pgTable("meal_plan_assignments", {
  id: uuid("id").primaryKey().defaultRandom(),
  memberId: text("member_id").notNull(),
  mealPlanId: uuid("meal_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"),
})

So the roster fact can now live in two tables, each with its own status.

Why a row should say one thing

The normal forms have intimidating names, but the idea behind the first three is a sentence: a row should state one fact about one thing, and every column in it should depend on the key and nothing else. My row states two facts about two things, and one of its columns depends on a different table entirely. Each of those gives the database a way to be wrong.

The copied column. trainer_id sits on the assignment, but which trainer a plan belongs to is a fact about the plan, and workout_plans.trainer_id already stores it. Two copies of one fact will disagree the first time one of them changes. I made it happen:

sql
UPDATE workout_plans SET trainer_id = 't2' WHERE id = '1111…';

SELECT a.member_id, a.trainer_id AS assignment_says, p.trainer_id AS plan_says
FROM member_assignments a
JOIN workout_plans p ON p.id = a.workout_plan_id;
 member_id | assignment_says | plan_says
-----------+-----------------+-----------
 m1        | t1              | t2

Two answers to “who is Ana’s trainer for this plan”, and both came from the database in good faith. This is the update anomaly, and there is no query that resolves it, because the database has no idea which copy is the truth.

The null that means “different kind of row”. After the roster insert and a plan assignment, the table holds this:

 member_id |           workout_plan_id            | trainer_id
-----------+--------------------------------------+------------
 m1        | 11111111-1111-1111-1111-111111111111 | t1
 m1        |                                      | t1

Same member, same trainer, two rows, two meanings. Every query now has to know the trick. My dashboard counts “active members” as active rows in this table, so Ana counts twice the moment she is on a plan. The uniqueness rule from part 1, one active row per member, is also wrong now: the roster row and the plan row are both legitimately active.

The twin. With meal_plan_assignments duplicating the roster columns, Ana can be active with Rey in one table and paused in the other. Nothing stops it, and nothing tells you which one the app should believe.

Three tables, three facts

The fix is to give each fact its own table, named for what it says.

sql
-- Who coaches whom. One active row per member.
CREATE TABLE coaching_assignments (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  member_id  text NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  trainer_id text NOT NULL REFERENCES "user"(id) ON DELETE RESTRICT,
  status     text NOT NULL DEFAULT 'active',
  start_date date NOT NULL DEFAULT CURRENT_DATE,
  end_date   date
);
CREATE UNIQUE INDEX coaching_one_active_per_member
  ON coaching_assignments (member_id) WHERE status = 'active';

-- Which workout plan a member is following. The trainer is implied by the plan.
CREATE TABLE workout_plan_assignments (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  member_id       text NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  workout_plan_id uuid NOT NULL REFERENCES workout_plans(id) ON DELETE CASCADE,
  status          text NOT NULL DEFAULT 'active',
  start_date      date NOT NULL DEFAULT CURRENT_DATE,
  end_date        date
);

-- Same shape for meal plans, pointing at meal_plans instead.

Three things changed on purpose.

trainer_id is gone from the plan assignments. The plan knows its trainer; ask it with a join. If the product later lets a trainer assign a colleague’s plan, that is a new fact, “who did the assigning”, and it gets a column named for it, assigned_by, not a copy of the plan’s owner.

No column is nullable to signal a row type. Each table has exactly one kind of row.

workout_plan_id is NOT NULL now, because a plan assignment without a plan was only ever the roster in disguise.

The question I expected to be hard, “who are Rey’s members and what are they on”, is a join:

sql
SELECT u.name, wp.name AS workout_plan
FROM coaching_assignments c
JOIN "user" u ON u.id = c.member_id
LEFT JOIN workout_plan_assignments wa
  ON wa.member_id = c.member_id AND wa.status = 'active'
LEFT JOIN workout_plans wp ON wp.id = wa.workout_plan_id
WHERE c.trainer_id = 't1' AND c.status = 'active';

Members with no plan yet come back with a null workout_plan, which is the honest answer, and each member appears once.

Why not one assignments table with a kind column?

I considered plan_assignments (kind, plan_id) covering both workouts and meals. It fails the test from part 1: plan_id would have to point at workout_plans for some rows and meal_plans for others, and a foreign key can only point at one table. You’d be back to a column the database can’t check. Two small tables with real foreign keys beat one flexible table with none.

Moving the data

The migration is less scary than it looks, because the old table already contains both new ones, mixed together.

  1. Create the three new tables.
  2. Roster rows: INSERT INTO coaching_assignments … SELECT DISTINCT ON (member_id, trainer_id) … from the old table, keeping the newest status.
  3. Plan rows: INSERT INTO workout_plan_assignments … SELECT … WHERE workout_plan_id IS NOT NULL.
  4. Point the app’s queries at the new tables. The dashboard count now reads coaching_assignments, and it is right.
  5. Drop member_assignments once nothing reads it.

What I’d tell past me

When a column is nullable because “sometimes this row is a different kind of thing”, you have two tables wearing one name. When a column repeats a fact that lives on another table, you have built a place for two answers to one question. And when you copy a whole table to make a second one, ask what the shared part is, because that is usually a third table you haven’t named yet.

Next in this series: every foreign key I added in part 1 came with a deletion rule, and I chose them in one sentence. That sentence deserves a whole post.