# CRM data model

> An example CRM (customer relationship management) data model with ER diagram — accounts and parent companies, contacts, leads, opportunities and pipeline stages, quoted products, activities and sales users — with SQL DDL.

Source: https://entitymodel.com/docs/examples/crm/

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.

<!-- diagram -->

**[Open this model in EntityModel.com](https://entitymodel.com/#/share/v1.vRvZcts48ldQ3JfZWtojyZHt6GkUWUo0sSVFktc1s5ViQSQsYUICDADa0Uzl37cA8AIJ6rKTJA8Rju5GX-huNP9xHimLoHB6DiICi21EAxQi5rjphPeEGMeUOL2263CaMB85PWcjRMx7v_5a2nPu08hxHQIjucBn8ofAIpS_BvM78Msg4YJGiIE5CqHAlPANjsEdJHCNIkTEvx3XoWwNCf5bTTs9Z6jA32XgAfoGozhE3HGdAHGf4Thd2AcchoiDwfyuB6Dv04QIDp6x2AAIYsgQEWc-jWJItmCDEYPM32xd4FMioC-4C0IEA-4CGseUiYRggREHYsNoslYgcIxCTJALYkaDxBccUGKudgH0BX7SOyEJUooSjhg_d1wn56PTPm85rgMTsaGsfkjHdbiAIuFOz4FxzOgTChzX8RmCAgWeElWn1bk8a7fPWhfLVqun_p23Wq0_HddJ4uCgdUpyGHGn979_MqEpij1JsWQw5nEIt146d69HtcA9sY3VBgFJAJmij4aUeT4N5Pi_LoaX_euRRUwRilaIAfoIxAalLBIIRuB5QwF9Jhww5FMWKJZBIRheJcKkUtLnYYkzhCsUprSB8Y1UOE3XeLJ0XAdzL2Y4gmzrfUFbpydYglyHoa8JZijIfqc65fScNx3nu5ujeUzCUB--QDRKwhBM9FiKarGcjyfvNbZ0mR1PjHEd5e-UBZCAW4TKqFEEcVhCO0x_V1FWUSQEf03QToR_KYS_QT9C59loCTOjYfm8c_1zH94CfF-bHhh-Q34i8BNyvn-W-swSXyilkZL87DqQIej0nCWCURl9arl17evnEwcq4FW_O7geWhQw8wJS_VYJxwRxDjgKQw4EdQFl0toZ5THyRbb4HCySFccBhkwad0wxEQAKCQOz1L1kXqdZb9MFpupmDCtr77vx-9MUuNNqXbQt_KyqcYb0RE0uEL4P6Qp9AwPKYsq00y5hxyRIuGBbLZQC_TgdBwM9nuKfD0fD-XAyGHrDyXK8_EMp2iNiiMgr559U8iW48r5IRJwIL2d4Dev3Mrl3_cn9qEzhM1pxLMq0PeQjVa4UYLLLb61ObzMjSEgCQ4-hJ0QSg_NqAszziRTLzXAwvuvfOq4TM-Tj9MLtuA73ocTZqSrykAscSUcPNC6Q4gKYgPvFzblBcKfbUn_OWy3TzcQh3SLkZbZV-Bs9AQbphOFWC7jtjglQwanLe6CHTxV3CtUubQOlIez7RZk0baSe1QZn2oB3mqLJ_eUGZWafuggXPFIGeMlNVETQarUM06TPBDGvfpdN5ThouNGqllhxbukBFGgTvbzadjniLDbjpjhVdFT3xoN84kBv3B5dXlxeWrxxjBinRPpSSPZ70JQgk2EpMa_jQa-u2p2WzYMe7rSPcM8_JcSYMbyFYA4jaDjnv-jK0-F5gft3ugLLdKzZ_X1AMJAB3IxRP2EqdD8leLESG0tif2t2rPGGkjLFs_T3QcD_0wbdbhe02magF1CPUOEV2p4Bv6FgQgUotD0T9XR6O-xP9tqj9BIpVEBj6atpIiTndNhLE8EQ9DempT7CkO-JmqzGKhOYuqXe6tEDzfRt_02_3aqbKQEJ-ZrAED9iFOTRkQtW6JEyBLAAK-TTSOU9mRXnyZXKhYpMadts3vIIpqFJ8l_HsN--rfjfn2J7CxiBKRP4b9OtqhujinyQBqYN-AugY4IF8jevZnMcRr9hDdNmckoqOvGvXuxKPAs1derlXoJuv-DLmL_vkvDD8N140p-XSU_z6ILgRTawL6MxDWCCnl3wTNkXTNYuKCyBMhBgnv82DflhOv8ogb_2nd-Ur_qUPCEmU_-SsdWuSr0ETIslh8Q7CyTA8wYRlTVJkQHMQY7w3CCjqFOUEOtB0C950eX4brhY9u9me-7MzuVZ6-1Zu7Nsd3UF48_d3nGWlmoMxhfHrfvIqTF5oKt8039z0-3YIhoqJAgYggDBMC1BlbyiYND_goK8tiQ5mlWXABdwjXaUPholu1eex_rLi3ardd3AwqrnKiN_rZTyDMxCKGQJEjBE0DMMQafVufppoVnGLGvIOdOTYGfouSOoVGKuOtOFHDzVjZalo6A3ONMC8U5fOptPZ9NF_9Zgd1TJEvuRmR0en8N-k2EECrSlPMGwIX1tX1uSVz-kHHmy1ln2M3IQ3MByAn_TXw73evicFFn_8YVMqBUCIBFUMzlZU-2ctbs_2LUf6-TSynTdwc3yiQOdW2fUvbm6qN8CNEJig8naUjxr9lkpWVULUoOvULS9srCg6qAybC92TrlPOgNDIhCLGeZmsIS5kPT7RpiEuQCzdPBwY9mT7Ouyj0QIFEKr6VzmlvOCK9Nr1Kyy57_FKhM7UMW6g6vRW1t9NsUFviZU2SORt-dB6YOF4uZL0qaAJ1-W3dZFu9XEvOPu6SNu5CMN60AT-ppAfakUMD8VQwdCvDBuOxiimk0sYIhOsIl9pmpq0w3mKhpAAZAs13ZiGkj3zakGkr72WQLKfjFzoDX0251R-9piDSFdr1EAfBiGLlBZngsihIR0wpQBAfkXF4SYqIiSlgLNNOuWgGAYbgEsKhHsMHPKzleNr_To6xjO9XWrVm9LsUrQ1RApR77cxieHSgYGe5hU4N4ZJd0Nh8tKhseT1V_IKCIt8pEXXDsM-1LiDD1h9PzTYuDjy607Yt7TXeKupORHJtRBgsws9iZBjRlsNT5sn7W6y_ZlnrGa5Z8QCRRUaj_Z2N4i41G1wuoLqy1R2HGpL9Ilph8rDKuWFg-vBteDmiObmQmuK2MVygLEXJ0gy3gyQI8wCQV4xkQGASu4wuFOB3VwFlW2uKPcky0N0lgrAabG-vLwktGYchhWbvmMFeY1Xxo94o3oxs5lKZAYMR_p55dyaNOgX7kOeCpx05IpiURxbzEbDpa6oSVjmqJd1Y8xWaurvjhJz0nDKAPOp_v-7Xj0RxXMJ1128_WzcxVQxwIoF2edIM32KpCuBchk-H66HOuc0oAyQWsqsJ2aq24d0MN0UgWh0tcAPFsgtFsWWm6nixp3Uxgh5aIGRMuyKjkv7-oaarGDVKL2IrD9kSEtA7_cVTxsEEMA6iqjDyMEHhmNdngBa2XaWpQ-2Q_YSssabdUPaLQvdgQPaIUJZEeZXokPimLHNYjMWxvkQ7W-Empb1CEt2zQttR3D_w6VxzHXLxkMEOAb-ixDTdmVICx7Vbg217ZobJ_Lw7HUB5p7pvfLd9P7yU1tzzQRK5qQ4mlKupZTVb0aHzYE9yoGfbm6f8Qk4MWbYIa8Wd9tcbElHD5Z1wf9W-O-U_gqaq7wvVjJBzAMj9Hw4uQplW6JuAyca64b3vXH1YX6cay6MgvmzbV3OteqrV72Fx8rS5eQfzlZ6UotTRV9GxczL1S1vO3KDyHn-dWpGleyltlmvTuymetk_av1aOWIK0qYI36xIt5BkjzKqi_L_MaBGmnyJKPdrZBcQ-DWd47GE8u-ESaQ-PItiSP2hP1UZ6qbPwz7t8sPlv0fEAzFxocMWffNh0ttHdV9cyQyE6nuWQ4HNkxL5G8IDel6a901u393Ox5Y9s2SVYh9wJEvKDvZeooOsVqjUjbxQtvRgFSXNw4kLPX-u9qmLaDjxRRctC8vz9oAhvEGnnWABLirk-mITrkjbali9c2kNfXKZcTVGhQ0cSfaW-XBhaxDzDcyTmACSEwVimRDQiDzT8m5I2zS4Kw6mWseqAbbrW0a9OubBpDAANpWv3_XiOIjJuuARrZdN8P6rveIRZBsbctH8_ryEYNShS2r-_f11X1peDDEcI-ZlRTmF_TNRywW_063FJ9tmCF41vW49eijU_pKpGjnFpCtkTCGfMgCTGCamkx6bf3JyTNkgVcqnqWQwfRR5bbyKwqUz6edm2puhQPMZOwnK552pcv6lBF4hBEOMeI9AIumza3u7OayHRELvq-x-7OiF-E10dZo6TG1VOpkySrwVts9fDI-xTiEVbLkFYB3WwuXpvKziqKD_gBG7T2qWXgrd1RT9oXrsll-vKKz7kQ1eJBAdemtejbdqsxf6Vh20ank1ya3tOfuxwktbd_7oRIr-oYE3XU4s4fmkNMV7UZLajlhMT1i6rOnA46ZNyLBUhuWpSFpHzcam6UaSqWPlJV5Y_LiRK0eKZhVrnyAHJgdST9OsStNLgecsFh5yAkrfTI2B17ppJmSVzqwpX-nQbQ2w244_avb9-sLutHMZfndU4VF2ylLT_wvMHjZAqBFaNPqtEHgVU7ZaLSqbYAfccpi6JATfsrAVw_4SbcrvKL-5o_7ZSGq52CPEq9wNaUoIn92PtEj3erX5kYJlh62f5xTkg9CNo9kO95x7ugBi43lZGPyRMMnFIDxawnP7nTgiibCM61q5-mON8C-RPFzhNdsgrK4tCa1eMJ2wqMdaj-FbY8ostkf40s_u0726TiuZvNpXuGpvMJaiMgyj1G2glFqWqjtq-8IfvMCFIuN07t0HRiG9NmLklBgTycbTk-9AbtOiJ5QqIlS_1XdMiuZIG5MWstfsuZ1EYOOcp5nye5yvsoHaC0e-dCkcx-NUX05TIXz_bP8q9-iUw4TmjL7-_8B)** to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or [download model.json](/docs/examples/crm/model.json).

## Entities

| Entity | Type | Description | Primary Key | Display Attribute | Attributes |
| --- | --- | --- | --- | --- | --- |
| Account | Entity | A company the business sells to, or a prospect company. Subsidiaries point at their parent account. | account_id | account_name | 9 |
| Activity | Entity | A logged call, email, meeting or task, linked to an account and optionally a contact or opportunity. | activity_id | subject | 9 |
| Contact | Entity | A person at an account. | contact_id | full_name | 7 |
| Lead | Entity | An unqualified prospect, before it becomes an account, contact and opportunity. | lead_id | full_name | 9 |
| Opportunity | Entity | A potential deal with an account, tracked through the pipeline stages. | opportunity_id | opportunity_name | 8 |
| Opportunity Line | Entity | A product quoted on an opportunity. | opportunity_product_id | sale_price | 5 |
| Product | Entity | Something the business sells. | product_id | product_name | 3 |
| User | Entity | A member of the sales team who owns records. | user_id | full_name | 4 |
| Activity Type | Reference | Kinds of sales activity. | type_code | type_name | 2 |
| Country | Reference | Countries, identified by their ISO 3166-1 alpha-2 code. | country_code | country_name | 2 |
| Industry | Reference | Industry classification for accounts. | industry_code | industry_name | 2 |
| Lead Source | Reference | Where a lead came from. | source_code | source_name | 2 |
| Opportunity Stage | Reference | Pipeline stages, in order, with the default win probability. | stage_code | stage_name | 3 |

## Attributes

### Account

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| account_id | BIGINT | PK, Required |  | 20031 |  |
| account_name | STRING | Label, Required |  | Globex Corporation |  |
| industry_code | REFERENCE |  | → industry.industry_code | MANUF |  |
| website | STRING |  |  | https://globex.example |  |
| annual_revenue | DECIMAL(12,2) |  |  | 25000000.00 | Estimated annual revenue in USD. |
| employee_count | INT |  |  | 1200 |  |
| country_code | REFERENCE |  | → country.country_code | US |  |
| parent_account_id | BIGINT |  |  | 20001 | The parent company, for subsidiaries. |
| owner_user_id | INT | Required |  | 42 | Account owner. |

### Activity

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| activity_id | BIGINT | PK, Required |  | 880120 |  |
| activity_type_code | REFERENCE | Required | → activity_type.type_code | MEETING |  |
| subject | STRING | Label, Required |  | Pricing review |  |
| account_id | BIGINT | Required |  | 20031 |  |
| contact_id | BIGINT |  |  | 77120 |  |
| opportunity_id | BIGINT |  |  | 31008 |  |
| owner_user_id | INT | Required |  | 42 |  |
| due_at | TIMESTAMP |  |  | 2026-11-05T16:00:00Z |  |
| completed | BOOLEAN | Required |  | false |  |

### Contact

A person at an account.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| contact_id | BIGINT | PK, Required |  | 77120 |  |
| account_id | BIGINT | Required |  | 20031 |  |
| full_name | STRING | Label, Required, PII |  | Priya Raman |  |
| job_title | STRING |  |  | Head of Procurement |  |
| email | STRING | PII |  | priya@globex.example |  |
| phone | STRING | PII |  | +1 555 0142 |  |
| do_not_contact | BOOLEAN | Required |  | false | The contact opted out of sales outreach. |

### Lead

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| lead_id | BIGINT | PK, Required |  | 99001 |  |
| full_name | STRING | Label, Required, PII |  | Sam Ortiz |  |
| company_name | STRING |  |  | Initech |  |
| email | STRING | PII |  | sam@initech.example |  |
| lead_source_code | REFERENCE | Required | → lead_source.source_code | WEBINAR |  |
| status | STRING | Required |  | WORKING | New, working, qualified or disqualified. |
| owner_user_id | INT | Required |  | 42 |  |
| converted_opportunity_id | BIGINT |  |  |  | Set when the lead is converted. |
| created_at | TIMESTAMP | Required |  | 2026-09-12T15:00:00Z |  |

### Opportunity

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| opportunity_id | BIGINT | PK, Required |  | 31008 |  |
| opportunity_name | STRING | Label, Required |  | Globex - Platform renewal 2027 |  |
| account_id | BIGINT | Required |  | 20031 |  |
| primary_contact_id | BIGINT |  |  | 77120 |  |
| stage_code | REFERENCE | Required | → opportunity_stage.stage_code | PROPOSAL |  |
| amount | DECIMAL(12,2) |  |  | 180000.00 | Expected deal value in USD. |
| close_date | DATE | Required |  | 2026-12-15 | Expected or actual close date. |
| owner_user_id | INT | Required |  | 42 |  |

### Opportunity Line

A product quoted on an opportunity.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| opportunity_product_id | BIGINT | PK, Required |  | 50310 |  |
| opportunity_id | BIGINT | Required |  | 31008 |  |
| product_id | INT | Required |  | 7 |  |
| quantity | INT | Required |  | 3 |  |
| sale_price | DECIMAL(12,2) | Label, Required |  | 54000.00 | Discounted unit price. |

### Product

Something the business sells.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| product_id | INT | PK, Required |  | 7 |  |
| product_name | STRING | Label, Required |  | Platform - Enterprise |  |
| list_price | DECIMAL(12,2) | Required |  | 60000.00 | Annual list price in USD. |

### User

A member of the sales team who owns records.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| user_id | INT | PK, Required |  | 42 |  |
| full_name | STRING | Label, Required, PII |  | Jordan Lee |  |
| email | STRING | Required, PII |  | jordan@acme.example |  |
| role | STRING | Required |  | Account Executive |  |

### Activity Type

Kinds of sales activity.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| type_code | STRING | PK, Required |  | CALL |  |
| type_name | STRING | Label, Required |  | Call |  |

### Country

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| country_code | STRING | PK, Required |  | US | ISO 3166-1 alpha-2 code. |
| country_name | STRING | Label, Required |  | United States | English short name. |

### Industry

Industry classification for accounts.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| industry_code | STRING | PK, Required |  | MANUF |  |
| industry_name | STRING | Label, Required |  | Manufacturing |  |

### Lead Source

Where a lead came from.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| source_code | STRING | PK, Required |  | WEBINAR |  |
| source_name | STRING | Label, Required |  | Webinar |  |

### Opportunity Stage

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| stage_code | STRING | PK, Required |  | PROPOSAL |  |
| stage_name | STRING | Label, Required |  | Proposal |  |
| probability | INT | Required |  | 50 | Default win probability in percent. |

## Relationships

| Relationship | Source | Target | Cardinality | Forward Label | Reverse Label | Key | Description |
| --- | --- | --- | --- | --- | --- | --- | --- |
| subsidiary_of | Account | Account | N:1 | Subsidiary Of | Parent Of | account.parent_account_id | Corporate families: a subsidiary points at its parent account. |
| account_owned_by | Account | User | N:1 | Owned By | Owns Account | account.owner_user_id |  |
| works_at | Contact | Account | N:1 | Works At | Employs | contact.account_id |  |
| lead_owned_by | Lead | User | N:1 | Owned By | Owns Lead | lead.owner_user_id |  |
| converted_to | Lead | Opportunity | N:1 | Converted To | Converted From | lead.converted_opportunity_id | Set when a qualified lead is converted. |
| opportunity_for | Opportunity | Account | N:1 | For | Has Opportunity | opportunity.account_id |  |
| primary_contact | Opportunity | Contact | N:1 | Primary Contact | Primary Contact On | opportunity.primary_contact_id |  |
| opportunity_owned_by | Opportunity | User | N:1 | Owned By | Owns Opportunity | opportunity.owner_user_id |  |
| line_on | Opportunity Line | Opportunity | N:1 | Line On | Has Line | opportunity_product.opportunity_id |  |
| quotes | Opportunity Line | Product | N:1 | Quotes | Quoted On | opportunity_product.product_id |  |
| logged_on_account | Activity | Account | N:1 | Logged On | Has Activity | activity.account_id |  |
| with_contact | Activity | Contact | N:1 | With | Involved In | activity.contact_id |  |
| about_opportunity | Activity | Opportunity | N:1 | About | Has Activity | activity.opportunity_id |  |
| assigned_to | Activity | User | N:1 | Assigned To | Assigned | activity.owner_user_id |  |

## Reference data

### Activity Type

4 values · Source: Example values

| type_code | type_name |
| --- | --- |
| CALL | Call |
| EMAIL | Email |
| MEETING | Meeting |
| TASK | Task |

### Country

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

| country_code | country_name |
| --- | --- |
| US | United States |
| CA | Canada |
| GB | United Kingdom |
| DE | Germany |
| FR | France |
| AU | Australia |

### Industry

6 values · Source: Example values

| industry_code | industry_name |
| --- | --- |
| MANUF | Manufacturing |
| FIN | Financial services |
| HEALTH | Healthcare |
| RETAIL | Retail |
| TECH | Technology |
| PUBLIC | Public sector |

### Lead Source

5 values · Source: Example values

| source_code | source_name |
| --- | --- |
| WEB | Website form |
| WEBINAR | Webinar |
| EVENT | Trade show or event |
| REFERRAL | Referral |
| OUTBOUND | Outbound prospecting |

### Opportunity Stage

6 values · Source: Example values

| stage_code | stage_name | probability |
| --- | --- | --- |
| PROSPECT | Prospecting | 10 |
| QUALIFY | Qualification | 20 |
| PROPOSAL | Proposal | 50 |
| NEGOTIATE | Negotiation | 75 |
| WON | Closed won | 100 |
| LOST | Closed lost | 0 |

## 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.

```sql
-- 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.

```mermaid
---
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.