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, which are stored as data rather than as a hard-coded status. Sales reps own deals. Every call or email lands in an activities log, which may or may not be tied to a deal yet.

Below, ErdDocs draws this schema from its SQL, every table and every relation. The Data Dictionary tab has the printable reference, complete and free on this page. The SQL is further down.

Your schema

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';

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';

Your diagram appears here.

Hover a table to light up its relations, and click to pin them.

What each table does

sales_repsDeal owners and activity authors. They are the "assigned to" column everywhere else.
companiesThe accounts: the durable party a deal is really with.
contactsPeople, optionally attached to a company. A lead is a contact whose links are not filled in yet.
pipeline_stagesThe pipeline as rows: sort_order drives the board columns, is_won drives reporting.
dealsThe money object: company, primary contact, stage, owner and value in one row.
activitiesThe 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, because the deal must survive the champion changing jobs. 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. You are free to copy it.

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. It is 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. With one contacts table, conversion is 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. Then 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

Restaurant Management System ER diagram
Northwind Database ER diagram
All example schemas
Data dictionary generator: the documentation side of the same tool.