CRM Database ER Diagram

A CRM revolves around companies, the contacts that work there, and the deals you negotiate with them. Every interaction — calls, emails, meetings — is an activity linked to a contact or a deal, which is what makes the pipeline history searchable later.

The interesting decision is on contacts.company_id: ON DELETE SET NULL. People outlive the companies they work for — when a company record is removed, its contacts stay, each with a null employer instead of vanishing with the account. Compare that with activities.contact_id, which cascades: deleting a contact (a privacy request, a duplicate) takes the interaction history with it, since an activity about nobody is noise. The same contact appears in both rules because the answer to what should survive genuinely differs per relationship.

deals carries two optional foreign keys, company_id and contact_id, because a deal often starts as a company-level opportunity before a named contact is identified — the schema tolerates that vagueness rather than forcing a placeholder row. owner_id is NOT NULL and points at users, the internal sales reps: every deal has exactly one accountable person. In the diagram this is a separate edge from users down to deals, distinct from the edges companies and contacts send, so pipeline position and ownership stay readable independently.

activities is the table that makes a CRM useful six months in. It references both a contact and a deal, either of which can be null, so one row records a call that touched a deal, a contact, or both — a small compromise compared to separate call, email and meeting tables, paid for by the type column. notes mirrors the same shape for longer text. stage is a VARCHAR defaulting to 'lead': the pipeline's stage order lives in application configuration, which is fine until someone wants per-team pipelines — at that point the column becomes a foreign key.

The first extension is nearly always deal_stage_history (deal_id, from_stage, to_stage, changed_at): with only a current stage column you can report on the pipeline as it is, but not on how long deals sat in each stage or where they stall. Configurable per-team pipelines come next — a pipelines table plus stages, with deals pointing at a stage id instead of free text. Teams that log email heavily split a dedicated emails table out of activities, with threading columns, keeping the generic log for calls and meetings.

The diagram

Tables (7)

Relationships (12)

• 1:Ncompanies.id→contacts.company_idON DELETE SET NULL
• 1:Ncompanies.id→deals.company_id
• 1:Ncontacts.id→deals.contact_id
• 1:Nusers.id→deals.owner_id
• 1:Ncontacts.id→activities.contact_idON DELETE CASCADE
• 1:Ndeals.id→activities.deal_idON DELETE CASCADE
• 1:Nusers.id→activities.user_id
• 1:Ncompanies.id→notes.company_idON DELETE CASCADE
• 1:Ncontacts.id→notes.contact_idON DELETE CASCADE
• 1:Nusers.id→notes.user_id
• 1:Ndeals.id→tasks.deal_idON DELETE CASCADE
• 1:Nusers.id→tasks.assignee_id

The SQL

CREATE TABLE companies (
  id SERIAL PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  website VARCHAR(200),
  industry VARCHAR(80),
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE contacts (
  id SERIAL PRIMARY KEY,
  company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL,
  first_name VARCHAR(60) NOT NULL,
  last_name VARCHAR(60) NOT NULL,
  email VARCHAR(255) UNIQUE,
  phone VARCHAR(30)
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL
);

CREATE TABLE deals (
  id SERIAL PRIMARY KEY,
  company_id INTEGER REFERENCES companies(id),
  contact_id INTEGER REFERENCES contacts(id),
  owner_id INTEGER NOT NULL REFERENCES users(id),
  title VARCHAR(150) NOT NULL,
  amount DECIMAL(12,2),
  stage VARCHAR(30) NOT NULL DEFAULT 'lead',
  expected_close DATE,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE activities (
  id SERIAL PRIMARY KEY,
  contact_id INTEGER REFERENCES contacts(id) ON DELETE CASCADE,
  deal_id INTEGER REFERENCES deals(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id),
  type VARCHAR(20) NOT NULL,
  summary VARCHAR(255) NOT NULL,
  occurred_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE notes (
  id SERIAL PRIMARY KEY,
  company_id INTEGER REFERENCES companies(id) ON DELETE CASCADE,
  contact_id INTEGER REFERENCES contacts(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  deal_id INTEGER REFERENCES deals(id) ON DELETE CASCADE,
  assignee_id INTEGER NOT NULL REFERENCES users(id),
  title VARCHAR(200) NOT NULL,
  due_date DATE,
  done BOOLEAN NOT NULL DEFAULT false
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides