CRM data model

This example models a sales CRM: the companies you sell to (accounts), the people there (contacts), prospects not yet qualified (leads), deals moving through a pipeline (opportunities) with the products quoted on them, and the calls, emails, meetings and tasks logged against them.

#Design notes

• Accounts form corporate families. parent_account_id links a subsidiary to its parent, and the Corporate Family hierarchy makes the levels explicit for roll-up reporting.
• Leads are separate from contacts. A lead is an unqualified prospect. When it's converted, converted_opportunity_id records the opportunity it became.
• Pipeline stages are a reference list with a default probability, so forecast reports can weight each opportunity by stage.
• Opportunity lines are a separate entity between opportunity and product, holding the quantity and the discounted price.
• Activities link to an account and, optionally, a contact and an opportunity, so the timeline can be shown at each level.
• Every record has an owner (owner_user_id), which drives territories and permissions.

To extend it: add campaign and campaign_member for marketing attribution, quote for formal quotes, case for support tickets, or territory for sales coverage.

#Diagram

CRM (Customer Relationship Management): 8 entities, 5 reference lists and 14 relationships. Each box is an entity with its attributes (PK marks the primary key); solid lines are relationships in crow's foot notation, dashed lines point to reference lists.

Open this model in EntityModel.com to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or download model.json.

#Entities

#Attributes

#Account

A company the business sells to, or a prospect company. Subsidiaries point at their parent account.

#Activity

A logged call, email, meeting or task, linked to an account and optionally a contact or opportunity.

#Contact

A person at an account.

#Lead

An unqualified prospect, before it becomes an account, contact and opportunity.

#Opportunity

A potential deal with an account, tracked through the pipeline stages.

#Opportunity Line

A product quoted on an opportunity.

#Product

Something the business sells.

#User

A member of the sales team who owns records.

#Activity Type

Kinds of sales activity.

#Country

Countries, identified by their ISO 3166-1 alpha-2 code.

#Industry

Industry classification for accounts.

#Lead Source

Where a lead came from.

#Opportunity Stage

Pipeline stages, in order, with the default win probability.

#Relationships

#Reference data

#Activity Type

4 values · Source: Example values

#Country

6 values · Source: ISO 3166-1 (excerpt)

#Industry

6 values · Source: Example values

#Lead Source

5 values · Source: Example values

#Opportunity Stage

6 values · Source: Example values

#SQL DDL (PostgreSQL)

Generated from the model. In the app, Export → Code also writes MySQL, Snowflake and Databricks DDL, DBML, dbt schema.yml and data contracts.

-- CRM (Customer Relationship Management) — data model
-- PostgreSQL DDL, generated 2026-11-03
-- Made with EntityModel.com

-- Reference list: Opportunity Stage
CREATE TABLE opportunity_stage (
  stage_code TEXT NOT NULL,
  stage_name TEXT NOT NULL,
  probability INTEGER NOT NULL,
  PRIMARY KEY (stage_code)
);
COMMENT ON TABLE opportunity_stage IS 'Pipeline stages, in order, with the default win probability.';
COMMENT ON COLUMN opportunity_stage.probability IS 'Default win probability in percent.';

-- Reference list: Lead Source
CREATE TABLE lead_source (
  source_code TEXT NOT NULL,
  source_name TEXT NOT NULL,
  PRIMARY KEY (source_code)
);
COMMENT ON TABLE lead_source IS 'Where a lead came from.';

-- Reference list: Activity Type
CREATE TABLE activity_type (
  type_code TEXT NOT NULL,
  type_name TEXT NOT NULL,
  PRIMARY KEY (type_code)
);
COMMENT ON TABLE activity_type IS 'Kinds of sales activity.';

-- Reference list: Industry
CREATE TABLE industry (
  industry_code TEXT NOT NULL,
  industry_name TEXT NOT NULL,
  PRIMARY KEY (industry_code)
);
COMMENT ON TABLE industry IS 'Industry classification for accounts.';

-- Reference list: Country
CREATE TABLE country (
  country_code TEXT NOT NULL,
  country_name TEXT NOT NULL,
  PRIMARY KEY (country_code)
);
COMMENT ON TABLE country IS 'Countries, identified by their ISO 3166-1 alpha-2 code.';
COMMENT ON COLUMN country.country_code IS 'ISO 3166-1 alpha-2 code.';
COMMENT ON COLUMN country.country_name IS 'English short name.';

CREATE TABLE sales_user (
  user_id INTEGER NOT NULL,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  role TEXT NOT NULL,
  PRIMARY KEY (user_id)
);
COMMENT ON TABLE sales_user IS 'A member of the sales team who owns records.';

CREATE TABLE account (
  account_id BIGINT NOT NULL,
  account_name TEXT NOT NULL,
  industry_code TEXT,
  website TEXT,
  annual_revenue NUMERIC(12,2),
  employee_count INTEGER,
  country_code TEXT,
  parent_account_id BIGINT,
  owner_user_id INTEGER NOT NULL,
  PRIMARY KEY (account_id)
);
COMMENT ON TABLE account IS 'A company the business sells to, or a prospect company. Subsidiaries point at their parent account.';
COMMENT ON COLUMN account.annual_revenue IS 'Estimated annual revenue in USD.';
COMMENT ON COLUMN account.parent_account_id IS 'The parent company, for subsidiaries.';
COMMENT ON COLUMN account.owner_user_id IS 'Account owner.';

CREATE TABLE contact (
  contact_id BIGINT NOT NULL,
  account_id BIGINT NOT NULL,
  full_name TEXT NOT NULL,
  job_title TEXT,
  email TEXT,
  phone TEXT,
  do_not_contact BOOLEAN NOT NULL,
  PRIMARY KEY (contact_id)
);
COMMENT ON TABLE contact IS 'A person at an account.';
COMMENT ON COLUMN contact.do_not_contact IS 'The contact opted out of sales outreach.';

CREATE TABLE lead (
  lead_id BIGINT NOT NULL,
  full_name TEXT NOT NULL,
  company_name TEXT,
  email TEXT,
  lead_source_code TEXT NOT NULL,
  status TEXT NOT NULL,
  owner_user_id INTEGER NOT NULL,
  converted_opportunity_id BIGINT,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (lead_id)
);
COMMENT ON TABLE lead IS 'An unqualified prospect, before it becomes an account, contact and opportunity.';
COMMENT ON COLUMN lead.status IS 'New, working, qualified or disqualified.';
COMMENT ON COLUMN lead.converted_opportunity_id IS 'Set when the lead is converted.';

CREATE TABLE opportunity (
  opportunity_id BIGINT NOT NULL,
  opportunity_name TEXT NOT NULL,
  account_id BIGINT NOT NULL,
  primary_contact_id BIGINT,
  stage_code TEXT NOT NULL,
  amount NUMERIC(12,2),
  close_date DATE NOT NULL,
  owner_user_id INTEGER NOT NULL,
  PRIMARY KEY (opportunity_id)
);
COMMENT ON TABLE opportunity IS 'A potential deal with an account, tracked through the pipeline stages.';
COMMENT ON COLUMN opportunity.amount IS 'Expected deal value in USD.';
COMMENT ON COLUMN opportunity.close_date IS 'Expected or actual close date.';

CREATE TABLE product (
  product_id INTEGER NOT NULL,
  product_name TEXT NOT NULL,
  list_price NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (product_id)
);
COMMENT ON TABLE product IS 'Something the business sells.';
COMMENT ON COLUMN product.list_price IS 'Annual list price in USD.';

CREATE TABLE opportunity_product (
  opportunity_product_id BIGINT NOT NULL,
  opportunity_id BIGINT NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  sale_price NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (opportunity_product_id)
);
COMMENT ON TABLE opportunity_product IS 'A product quoted on an opportunity.';
COMMENT ON COLUMN opportunity_product.sale_price IS 'Discounted unit price.';

CREATE TABLE activity (
  activity_id BIGINT NOT NULL,
  activity_type_code TEXT NOT NULL,
  subject TEXT NOT NULL,
  account_id BIGINT NOT NULL,
  contact_id BIGINT,
  opportunity_id BIGINT,
  owner_user_id INTEGER NOT NULL,
  due_at TIMESTAMP,
  completed BOOLEAN NOT NULL,
  PRIMARY KEY (activity_id)
);
COMMENT ON TABLE activity IS 'A logged call, email, meeting or task, linked to an account and optionally a contact or opportunity.';

-- Keys between tables
ALTER TABLE sales_user ADD CONSTRAINT uq_sales_user_email UNIQUE (email);
ALTER TABLE account ADD CONSTRAINT fk_account_industry_code FOREIGN KEY (industry_code) REFERENCES industry (industry_code);
ALTER TABLE account ADD CONSTRAINT fk_account_country_code FOREIGN KEY (country_code) REFERENCES country (country_code);
ALTER TABLE lead ADD CONSTRAINT fk_lead_lead_source_code FOREIGN KEY (lead_source_code) REFERENCES lead_source (source_code);
ALTER TABLE opportunity ADD CONSTRAINT fk_opportunity_stage_code FOREIGN KEY (stage_code) REFERENCES opportunity_stage (stage_code);
ALTER TABLE activity ADD CONSTRAINT fk_activity_activity_type_code FOREIGN KEY (activity_type_code) REFERENCES activity_type (type_code);
ALTER TABLE account ADD CONSTRAINT fk_subsidiary_of FOREIGN KEY (parent_account_id) REFERENCES account (account_id);
ALTER TABLE account ADD CONSTRAINT fk_account_owned_by FOREIGN KEY (owner_user_id) REFERENCES sales_user (user_id);
ALTER TABLE contact ADD CONSTRAINT fk_works_at FOREIGN KEY (account_id) REFERENCES account (account_id);
ALTER TABLE lead ADD CONSTRAINT fk_lead_owned_by FOREIGN KEY (owner_user_id) REFERENCES sales_user (user_id);
ALTER TABLE lead ADD CONSTRAINT fk_converted_to FOREIGN KEY (converted_opportunity_id) REFERENCES opportunity (opportunity_id);
ALTER TABLE opportunity ADD CONSTRAINT fk_opportunity_for FOREIGN KEY (account_id) REFERENCES account (account_id);
ALTER TABLE opportunity ADD CONSTRAINT fk_primary_contact FOREIGN KEY (primary_contact_id) REFERENCES contact (contact_id);
ALTER TABLE opportunity ADD CONSTRAINT fk_opportunity_owned_by FOREIGN KEY (owner_user_id) REFERENCES sales_user (user_id);
ALTER TABLE opportunity_product ADD CONSTRAINT fk_line_on FOREIGN KEY (opportunity_id) REFERENCES opportunity (opportunity_id);
ALTER TABLE opportunity_product ADD CONSTRAINT fk_quotes FOREIGN KEY (product_id) REFERENCES product (product_id);
ALTER TABLE activity ADD CONSTRAINT fk_logged_on_account FOREIGN KEY (account_id) REFERENCES account (account_id);
ALTER TABLE activity ADD CONSTRAINT fk_with_contact FOREIGN KEY (contact_id) REFERENCES contact (contact_id);
ALTER TABLE activity ADD CONSTRAINT fk_about_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunity (opportunity_id);
ALTER TABLE activity ADD CONSTRAINT fk_assigned_to FOREIGN KEY (owner_user_id) REFERENCES sales_user (user_id);

-- Reference values
INSERT INTO opportunity_stage (stage_code, stage_name, probability) VALUES
  ('PROSPECT', 'Prospecting', 10),
  ('QUALIFY', 'Qualification', 20),
  ('PROPOSAL', 'Proposal', 50),
  ('NEGOTIATE', 'Negotiation', 75),
  ('WON', 'Closed won', 100),
  ('LOST', 'Closed lost', 0);

INSERT INTO lead_source (source_code, source_name) VALUES
  ('WEB', 'Website form'),
  ('WEBINAR', 'Webinar'),
  ('EVENT', 'Trade show or event'),
  ('REFERRAL', 'Referral'),
  ('OUTBOUND', 'Outbound prospecting');

INSERT INTO activity_type (type_code, type_name) VALUES
  ('CALL', 'Call'),
  ('EMAIL', 'Email'),
  ('MEETING', 'Meeting'),
  ('TASK', 'Task');

INSERT INTO industry (industry_code, industry_name) VALUES
  ('MANUF', 'Manufacturing'),
  ('FIN', 'Financial services'),
  ('HEALTH', 'Healthcare'),
  ('RETAIL', 'Retail'),
  ('TECH', 'Technology'),
  ('PUBLIC', 'Public sector');

INSERT INTO country (country_code, country_name) VALUES
  ('US', 'United States'),
  ('CA', 'Canada'),
  ('GB', 'United Kingdom'),
  ('DE', 'Germany'),
  ('FR', 'France'),
  ('AU', 'Australia');

-- Hierarchies (navigation over the relationships above):
--   Corporate Family: root account, 2 level(s)

#Mermaid

Paste into a mermaid code block on GitHub, GitLab or Notion to show the diagram there.

---
title: CRM (Customer Relationship Management)
---
%% Made with EntityModel.com
erDiagram
  sales_user {
    int user_id PK "User ID"
    string full_name "Full Name"
    string email "Email"
    string role "Role"
  }
  account {
    bigint account_id PK "Account ID"
    string account_name "Account Name"
    string industry_code FK "Industry Code"
    string website "Website"
    decimal annual_revenue "Estimated annual revenue in USD."
    int employee_count "Employee Count"
    string country_code FK "Country Code"
    bigint parent_account_id FK "The parent company, for subsidiaries."
    int owner_user_id FK "Account owner."
  }
  contact {
    bigint contact_id PK "Contact ID"
    bigint account_id FK "Account ID"
    string full_name "Full Name"
    string job_title "Job Title"
    string email "Email"
    string phone "Phone"
    boolean do_not_contact "The contact opted out of sales outreach."
  }
  lead {
    bigint lead_id PK "Lead ID"
    string full_name "Full Name"
    string company_name "Company Name"
    string email "Email"
    string lead_source_code FK "Lead Source Code"
    string status "New, working, qualified or disqualified."
    int owner_user_id FK "Owner User ID"
    bigint converted_opportunity_id FK "Set when the lead is converted."
    timestamp created_at "Created At"
  }
  opportunity {
    bigint opportunity_id PK "Opportunity ID"
    string opportunity_name "Opportunity Name"
    bigint account_id FK "Account ID"
    bigint primary_contact_id FK "Primary Contact ID"
    string stage_code FK "Stage Code"
    decimal amount "Expected deal value in USD."
    date close_date "Expected or actual close date."
    int owner_user_id FK "Owner User ID"
  }
  product {
    int product_id PK "Product ID"
    string product_name "Product Name"
    decimal list_price "Annual list price in USD."
  }
  opportunity_product {
    bigint opportunity_product_id PK "Opportunity Product ID"
    bigint opportunity_id FK "Opportunity ID"
    int product_id FK "Product ID"
    int quantity "Quantity"
    decimal sale_price "Discounted unit price."
  }
  activity {
    bigint activity_id PK "Activity ID"
    string activity_type_code FK "Activity Type Code"
    string subject "Subject"
    bigint account_id FK "Account ID"
    bigint contact_id FK "Contact ID"
    bigint opportunity_id FK "Opportunity ID"
    int owner_user_id FK "Owner User ID"
    timestamp due_at "Due At"
    boolean completed "Completed"
  }
  opportunity_stage {
    string stage_code PK "Stage Code"
    string stage_name "Stage Name"
    int probability "Default win probability in percent."
  }
  lead_source {
    string source_code PK "Source Code"
    string source_name "Source Name"
  }
  activity_type {
    string type_code PK "Type Code"
    string type_name "Type Name"
  }
  industry {
    string industry_code PK "Industry Code"
    string industry_name "Industry Name"
  }
  country {
    string country_code PK "ISO 3166-1 alpha-2 code."
    string country_name "English short name."
  }
  account }o--|| account : "Subsidiary Of"
  account }o--|| sales_user : "Owned By"
  contact }o--|| account : "Works At"
  lead }o--|| sales_user : "Owned By"
  lead }o--|| opportunity : "Converted To"
  opportunity }o--|| account : "For"
  opportunity }o--|| contact : "Primary Contact"
  opportunity }o--|| sales_user : "Owned By"
  opportunity_product }o--|| opportunity : "Line On"
  opportunity_product }o--|| product : "Quotes"
  activity }o--|| account : "Logged On"
  activity }o--|| contact : "With"
  activity }o--|| opportunity : "About"
  activity }o--|| sales_user : "Assigned To"
  account }o..|| industry : "Industry Code"
  account }o..|| country : "Country Code"
  lead }o..|| lead_source : "Lead Source Code"
  opportunity }o..|| opportunity_stage : "Stage Code"
  activity }o..|| activity_type : "Activity Type Code"

#Frequently asked questions

#What is the difference between a lead and a contact in a CRM?

A lead is an unqualified prospect, often a name and a company from a form or an event. A contact is a person at an account you already work with. When a lead is qualified, it is converted into an account, a contact and usually an opportunity.

#How do you model parent and child companies in a CRM?

Give the account table a parent_account_id that points at another account. That self-relationship forms a hierarchy you can roll revenue and opportunities up through.