ErdDocs

Example schemas / CRM

CRM ER diagram

A CRM database revolves around three linked entities: companies are the accounts, contacts are the people at them, and deals are the money in flight. Deals move through pipeline stages stored as data rather than a hard-coded status, sales reps own deals, and every call or email lands in an activities log that may or may not be tied to a deal yet.

Your schema

Hover a table to light up its relations, click to pin. Switch to Data Dictionary for the printable reference version of this schema.

What each table does

  • sales_reps — Deal owners and activity authors — the "assigned to" column everywhere else.
  • companies — The accounts; the durable party a deal is really with.
  • contacts — People, optionally attached to a company — a lead is a contact whose links are not filled in yet.
  • pipeline_stages — The pipeline as rows: sort_order drives the board columns, is_won drives reporting.
  • deals — The money object — company, primary contact, stage, owner and value in one row.
  • activities — The interaction log; deal_id stays NULL for touches that precede any deal.

Design decisions worth copying

  • pipeline_stages is a table, not a varchar status: renaming "Negotiation" or inserting a stage becomes an UPDATE, not a migration plus a code deploy, and sort_order gives the kanban board its column order for free.
  • deals carries both company_id and contact_id even though a contact already knows its company. The company is the durable party — the deal must survive the champion changing jobs — and the redundancy creates one rule the schema cannot express: the primary contact should belong to the deal’s company. That check lives in a trigger or the application.
  • activities.deal_id is nullable on purpose: the first three calls usually happen before there is a deal. NULL beats inventing a placeholder deal, and backfilling the link later is a plain UPDATE.

The SQL

PostgreSQL dialect; paste it into the tool above (it is already there), into your own database, or into a migration. Copy freely — example schemas are meant to be taken.

CREATE TABLE sales_reps (
    id         bigserial    PRIMARY KEY,
    email      varchar(255) NOT NULL UNIQUE,
    full_name  varchar(120) NOT NULL
);

CREATE TABLE companies (
    id        bigserial    PRIMARY KEY,
    name      varchar(160) NOT NULL,
    industry  varchar(80),
    website   varchar(255)
);

CREATE TABLE contacts (
    id          bigserial    PRIMARY KEY,
    company_id  bigint       REFERENCES companies(id),
    email       varchar(255) UNIQUE,
    full_name   varchar(120) NOT NULL,
    phone       varchar(40)
);
COMMENT ON COLUMN contacts.company_id IS 'NULL until the person is linked to an account';

CREATE TABLE pipeline_stages (
    id          bigserial   PRIMARY KEY,
    name        varchar(60) NOT NULL UNIQUE,
    sort_order  int         NOT NULL,
    is_won      boolean     NOT NULL DEFAULT false
);
COMMENT ON TABLE pipeline_stages IS 'Stages are rows, not an enum: teams rename and reorder them';

CREATE TABLE deals (
    id              bigserial     PRIMARY KEY,
    company_id      bigint        NOT NULL REFERENCES companies(id),
    contact_id      bigint        REFERENCES contacts(id),
    stage_id        bigint        NOT NULL REFERENCES pipeline_stages(id),
    owner_id        bigint        NOT NULL REFERENCES sales_reps(id),
    title           varchar(200)  NOT NULL,
    value           numeric(12,2),
    expected_close  date
);
COMMENT ON COLUMN deals.contact_id IS 'Primary contact; should belong to the same company';

CREATE TABLE activities (
    id           bigserial   PRIMARY KEY,
    rep_id       bigint      NOT NULL REFERENCES sales_reps(id),
    contact_id   bigint      NOT NULL REFERENCES contacts(id),
    deal_id      bigint      REFERENCES deals(id),
    kind         varchar(20) NOT NULL,
    note         text,
    happened_at  timestamptz NOT NULL DEFAULT now()
);
COMMENT ON COLUMN activities.kind IS 'call, email, meeting, note';
COMMENT ON COLUMN activities.deal_id IS 'NULL when the touch is not tied to a deal yet';

FAQ

Why is there no leads table?

A lead is a contact whose company and deal do not exist yet — an early state, not a different kind of row. Modeling leads as their own table duplicates every contact column and forces a fragile "conversion" routine that copies rows across; keeping one contacts table makes conversion just filling in company_id and creating a deal.

How would multiple contacts per deal work?

Add a deal_contacts junction (deal_id, contact_id, role) for the buying committee, and either keep deals.contact_id as the shortcut to the primary contact or replace it with a role = "primary" row in the junction.

More example schemas