CRM Operational data model
Normalized (3NF) operational CRM application schema. Account/contact backbone, lead capture and conversion, opportunity -> line -> quote pipeline, campaign membership, activity logging, and case management. Person PII carries GDPR classification with masking and consent flags. To replicate an existing Salesforce org as a landing layer, use the Salesforce Core Objects (3NF Source Replica) template instead.
A vendor-neutral operational CRM application schema for sales and service: accounts and their contacts, inbound leads, the opportunity pipeline with line items and quotes, the product catalog, marketing campaigns and members, logged activities, and support cases. Contact and lead PII is classified for GDPR. Designed for building a CRM database, not replicating an existing SaaS CRM. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 13
- tables
- 120
- columns
- 18
- relationships
- 9
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Lead distinct from contact/account
Unqualified leads carry their own PII and lifecycle before conversion; keeping them separate (with a converted-account reference) mirrors how CRM systems handle lead conversion and avoids polluting the account graph with prospects.
Opportunity / line / quote normalization
Deals carry multiple product lines and may generate multiple quotes; modeling lines and quotes as children of the opportunity keeps amounts auditable and avoids repeating product data.
Polymorphic campaign membership via nullable FKs
A campaign member can be either a lead or a contact; two nullable FKs on the junction keep referential integrity without a circular lead<->contact dependency.
Contact/lead PII masked with consent flag
Email is hashed and other contact fields partially masked; a do_not_contact flag records GDPR/CCPA consent so downstream outreach can honor it.
Tables in the CRM Operational model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (11)
account
Grain: one row per customer/prospect organization
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| account_number | varchar | BK |
| account_name | varchar | |
| parent_account_number | varchar | |
| industry | varchar | |
| account_type | varchar | |
| annual_revenue | decimal | |
| employee_count | integer | |
| billing_country | varchar | |
| website | varchar | |
| owner_user_id | varchar | |
| is_active | boolean |
contact
Grain: one row per person associated with an account
Person-level PII classified for GDPR.
| Column | Type | Keys |
|---|---|---|
| contact_key | bigint | PK |
| account_key | bigint | |
| contact_external_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| mobile_phone | varchar | PII |
| job_title | varchar | |
| mailing_country | varchar | |
| do_not_contact | boolean | |
| is_primary | boolean |
lead
Grain: one row per unqualified inbound lead
Person-level PII classified for GDPR.
| Column | Type | Keys |
|---|---|---|
| lead_key | bigint | PK |
| lead_source_key | bigint | |
| campaign_key | bigint | |
| lead_external_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| company_name | varchar | |
| status | varchar | |
| rating | varchar | |
| converted_account_number | varchar | |
| created_at | timestamp |
product
Grain: one row per sellable product/SKU
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_code | varchar | BK |
| product_name | varchar | |
| product_family | varchar | |
| list_price | decimal | |
| currency_code | varchar | |
| is_active | boolean |
opportunity
Grain: one row per sales opportunity (deal)
| Column | Type | Keys |
|---|---|---|
| opportunity_key | bigint | PK |
| account_key | bigint | |
| primary_contact_key | bigint | |
| campaign_key | bigint | |
| opportunity_external_id | varchar | BK |
| opportunity_name | varchar | |
| stage | varchar | |
| amount | decimal | |
| currency_code | varchar | |
| probability_pct | decimal | |
| close_date | date | |
| is_closed | boolean | |
| is_won | boolean | |
| owner_user_id | varchar | |
| created_at | timestamp |
opportunity_line
Grain: one row per product line on an opportunity
| Column | Type | Keys |
|---|---|---|
| opportunity_line_key | bigint | PK |
| opportunity_key | bigint | |
| product_key | bigint | |
| quantity | decimal | |
| unit_price | decimal | |
| line_total | decimal | |
| discount_pct | decimal |
quote
Grain: one row per quote issued for an opportunity
| Column | Type | Keys |
|---|---|---|
| quote_key | bigint | PK |
| opportunity_key | bigint | |
| quote_number | varchar | BK |
| status | varchar | |
| total_amount | decimal | |
| currency_code | varchar | |
| valid_until | date | |
| issued_at | timestamp |
campaign
Grain: one row per marketing campaign
| Column | Type | Keys |
|---|---|---|
| campaign_key | bigint | PK |
| campaign_code | varchar | BK |
| campaign_name | varchar | |
| campaign_type | varchar | |
| status | varchar | |
| start_date | date | |
| end_date | date | |
| budgeted_cost | decimal | |
| actual_cost | decimal |
campaign_member
Grain: one row per contact/lead per campaign (junction)
| Column | Type | Keys |
|---|---|---|
| campaign_member_key | bigint | PK |
| campaign_key | bigint | |
| contact_key | bigint | |
| lead_key | bigint | |
| member_status | varchar | |
| responded_at | timestamp |
activity
Grain: one row per logged activity (call/email/meeting/task)
| Column | Type | Keys |
|---|---|---|
| activity_key | bigint | PK |
| account_key | bigint | |
| contact_key | bigint | |
| opportunity_key | bigint | |
| activity_external_id | varchar | BK |
| activity_type | varchar | |
| subject | varchar | |
| status | varchar | |
| due_date | date | |
| completed_at | timestamp | |
| owner_user_id | varchar |
case
Grain: one row per support case raised by a customer
| Column | Type | Keys |
|---|---|---|
| case_key | bigint | PK |
| account_key | bigint | |
| contact_key | bigint | |
| case_priority_key | bigint | |
| case_number | varchar | BK |
| subject | varchar | |
| description | varchar | |
| status | varchar | |
| origin | varchar | |
| opened_at | timestamp | |
| closed_at | timestamp | |
| owner_user_id | varchar |
Lookup tables (2)
lead_source
Grain: reference list of lead/marketing sources
| Column | Type | Keys |
|---|---|---|
| lead_source_key | bigint | PK |
| source_code | varchar | BK |
| source_name | varchar | |
| channel | varchar |
case_priority
Grain: reference list of case priorities
| Column | Type | Keys |
|---|---|---|
| case_priority_key | bigint | PK |
| priority_code | varchar | BK |
| priority_name | varchar | |
| sort_order | smallint |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- contact.account_key → account (many-to-one)
- lead.lead_source_key → lead_source (many-to-one)
- lead.campaign_key → campaign (many-to-one)
- opportunity.account_key → account (many-to-one)
- opportunity.primary_contact_key → contact (many-to-one)
- opportunity.campaign_key → campaign (many-to-one)
- opportunity_line.opportunity_key → opportunity (many-to-one)
- opportunity_line.product_key → product (many-to-one)
- quote.opportunity_key → opportunity (many-to-one)
- campaign_member.campaign_key → campaign (many-to-one)
- campaign_member.contact_key → contact (many-to-one)
- campaign_member.lead_key → lead (many-to-one)
- activity.account_key → account (many-to-one)
- activity.contact_key → contact (many-to-one)
- activity.opportunity_key → opportunity (many-to-one)
- case.account_key → account (many-to-one)
- case.contact_key → contact (many-to-one)
- case.case_priority_key → case_priority (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.