Skip to content

Design Journey: tbl_marketing_team_member

Purpose: This document records the structural design decisions behind the marketing team hierarchy. It documents all three options considered — including why the first two were rejected — because the reasoning matters if this ever needs to be revisited.

The Requirement

Marketing staff are divided into flat teams of ~3 people. Each team has exactly one head, and there is no multi-level hierarchy (a head is not a "senior" or a manager in a reporting-chain sense — just a designated point person for that team).

Option 1 (rejected): Two-table design

Structure: tbl_marketing_team + TEAM_ID on tbl_user

CREATE TABLE saar_biotech.tbl_marketing_team (
  "TEAM_ID"           SERIAL PRIMARY KEY,
  "TEAM_NAME_C"        VARCHAR(100) NOT NULL,
  "TEAM_HEAD_USER_ID"  VARCHAR(255) NOT NULL REFERENCES saar_biotech.tbl_user("USER_ID"),
  "CREATED_TS"         TIMESTAMP NOT NULL DEFAULT now(),
  "UPDATED_TS"         TIMESTAMP NOT NULL DEFAULT now(),
  "CREATED_BY"         VARCHAR(100),
  "UPDATED_BY"         VARCHAR(100)
);

ALTER TABLE saar_biotech.tbl_user
  ADD COLUMN "TEAM_ID" INT REFERENCES saar_biotech.tbl_marketing_team("TEAM_ID");

Reasoning at the time: A dedicated tbl_marketing_team table holds team-level facts (name, who's the head) exactly once per team, avoiding repetition. TEAM_HEAD_USER_ID living on the team row (not as a flag on each user) meant it was structurally impossible for two people in the same team to both be accidentally marked "head" — no separate guard needed. tbl_user.TEAM_ID gives each person exactly one team membership, matching the flat/non-hierarchical requirement.

Why it was rejected: Explicitly rejected by the business owner — did not want to maintain two separate new tables (a team table and a membership concept) for what should be one simple fact ("who belongs to which team").

Option 2 (rejected): Zero new tables

Structure: Columns directly on tbl_user

ALTER TABLE saar_biotech.tbl_user
  ADD COLUMN "TEAM_NAME_C"        VARCHAR(100),
  ADD COLUMN "TEAM_HEAD_USER_ID"  VARCHAR(255) REFERENCES saar_biotech.tbl_user("USER_ID");

Reasoning at the time: Leanest possible option — no new table at all. TEAM_NAME_C as a repeated text label per member, TEAM_HEAD_USER_ID as a self-reference back into tbl_user pointing at whichever row is the head.

Known issue, flagged before rejection: TEAM_NAME_C is free text repeated across every member's row, not a single canonical value. A typo on one member's row ("Team Alpha " vs "Team Alpha") would silently create a phantom second team with no error raised. Renaming a team means updating every member's row individually rather than one place.

Why it was rejected: The business owner explicitly asked for a separate table to record "who belongs to which team" instead — this option didn't provide one.

Option 3 (FINAL — implemented): Single dedicated membership table

Structure: No separate team-master table.

CREATE TABLE saar_biotech.tbl_marketing_team_member (
  "TEAM_MEMBER_ID"      SERIAL PRIMARY KEY,
  "TEAM_NAME_C"          VARCHAR(100) NOT NULL,
  "USER_ID"              VARCHAR(255) NOT NULL REFERENCES saar_biotech.tbl_user("USER_ID"),
  "IS_TEAM_HEAD_B"       BOOLEAN NOT NULL DEFAULT FALSE,
  "CREATED_TS"           TIMESTAMP NOT NULL DEFAULT now(),
  "UPDATED_TS"           TIMESTAMP NOT NULL DEFAULT now(),
  "CREATED_BY"           VARCHAR(100),
  "UPDATED_BY"           VARCHAR(100),

  CONSTRAINT uq_user_team UNIQUE ("USER_ID")
);

CREATE INDEX idx_marketing_team_member_team ON saar_biotech.tbl_marketing_team_member ("TEAM_NAME_C");

CREATE UNIQUE INDEX uq_one_head_per_team
ON saar_biotech.tbl_marketing_team_member ("TEAM_NAME_C")
WHERE "IS_TEAM_HEAD_B" = TRUE;

Why this is the right final shape: - Satisfies the explicit requirement for one separate table, no more, no less — not two tables (Option 1), not zero tables (Option 2). - UNIQUE("USER_ID") constraint enforces one person belongs to exactly one team at a time. - IS_TEAM_HEAD_B lives on the membership row itself. This carries a risk: nothing in a plain boolean column stops two members of the same team from both being marked head by mistake. This gap is explicitly closed with a partial unique index (uq_one_head_per_team). This restores, at the index level, the same guarantee Option 1 got for free structurally. - TEAM_NAME_C is still technically free text on every membership row (same repetition characteristic as Option 2). This risk was not eliminated, only accepted as low-stakes given the small team sizes (~3 people) and infrequent renaming.

Summary of the full journey: started with two tables (rejected — too much), collapsed to zero new tables (rejected — wanted a dedicated table), landed on exactly one table that captures membership directly, with a partial unique index standing in for the "no double head" guarantee that a separate team-master table would otherwise have provided structurally.