CRM ER diagram and database schema

What this schema models

A complete CRM database design, built the way sales teams actually run a pipeline: 24 tables, 14 enums and 59 relationships covering users, teams and record ownership, campaigns and leads with a conversion record, accounts, email domains and contacts, configurable pipelines and stages, opportunities with buying committees and an append-only stage history, products, price books, line items and versioned quotes, and calls, emails, meetings, tasks and notes. THE FOUR DESIGN RULES 1. A LEAD is not a CONTACT. Leads are unqualified (form fills, badge scans, list imports) and live in their own table with a company name, not an account. Conversion writes one lead_conversions row naming the account, contact and optional opportunity the lead became, and the lead stays read-only so its source and campaign are credited with any revenue it wins. 2. Stage history is append-only, because forecasting depends on it. Every change to stage, amount, close date or forecast category writes a row carrying the full forecast state. "What did the Q3 commit look like on September 1?", stage conversion rates and slipped close dates become queries, not guesses. 3. Prices are snapshots. Products hold no price; price book entries do, per currency. Opportunity line items copy the list price and the negotiated unit price when added, and a sent quote is frozen: a change is a new version, and the old one is marked superseded. 4. Activities link to many records with real foreign keys. One meeting with two contacts about one deal at one account is one activity and four activity_links rows. Each link fills exactly one typed foreign key column, enforced by a CHECK, instead of a record_type plus record_id pair, so no link can dangle. DETAILS WORTH STEALING - Pipelines and stages are data. Each stage carries a default probability, forecast category and outcome (open, won, lost), so reports never parse stage names. - ownership_history records every reassignment, which settles commission disputes and answers "who owned this deal in Q2?" - account_domains matches incoming leads and logged emails to the right account by email domain. - Contacts carry a case-insensitive unique index on lower(email) as a duplicate guard. - Email consent is a timestamp (email_opted_out_at), not a boolean, so you can prove when it was withdrawn. - opportunity_contacts records each person's role on a deal (champion, economic buyer, blocker), because the same person plays different roles on different deals. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: users and ownership, leads and campaigns, accounts and contacts, sales pipeline, products and quotes, and activities and notes. Every table carries a note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building a CRM or sales tool, and for students who need a realistic CRM ER diagram for a DBMS project. This is a schema design reference; territory, assignment and approval rules belong in your own business logic.

Tables (24)

Relationships (59)

• account_domains.account_id references accounts.id many to one
• accounts.owner_id references users.id many to one
• accounts.parent_account_id references accounts.id many to one
• activities.owner_id references users.id many to one
• activity_links.account_id references accounts.id many to one
• activity_links.activity_id references activities.id many to one
• activity_links.contact_id references contacts.id many to one
• activity_links.lead_id references leads.id many to one
• activity_links.opportunity_id references opportunities.id many to one
• campaign_members.campaign_id references campaigns.id many to one
• campaign_members.contact_id references contacts.id many to one
• campaign_members.lead_id references leads.id many to one
• campaigns.owner_id references users.id many to one
• contacts.account_id references accounts.id many to one
• contacts.owner_id references users.id many to one
• lead_conversions.account_id references accounts.id many to one
• lead_conversions.contact_id references contacts.id many to one
• lead_conversions.converted_by references users.id many to one
• lead_conversions.opportunity_id references opportunities.id many to one
• leads.campaign_id references campaigns.id many to one
• leads.id references lead_conversions.lead_id one to one
• leads.owner_id references users.id many to one
• notes.account_id references accounts.id many to one
• notes.author_id references users.id many to one
• notes.contact_id references contacts.id many to one
• notes.lead_id references leads.id many to one
• notes.opportunity_id references opportunities.id many to one
• opportunities.account_id references accounts.id many to one
• opportunities.owner_id references users.id many to one
• opportunities.pipeline_id references pipelines.id many to one
• opportunities.price_book_id references price_books.id many to one
• opportunities.stage_id references pipeline_stages.id many to one
• opportunity_contacts.contact_id references contacts.id many to one
• opportunity_contacts.opportunity_id references opportunities.id many to one
• opportunity_line_items.opportunity_id references opportunities.id many to one
• opportunity_line_items.price_book_entry_id references price_book_entries.id many to one
• opportunity_line_items.product_id references products.id many to one
• opportunity_stage_history.changed_by references users.id many to one
• opportunity_stage_history.from_stage_id references pipeline_stages.id many to one
• opportunity_stage_history.opportunity_id references opportunities.id many to one
• opportunity_stage_history.to_stage_id references pipeline_stages.id many to one
• ownership_history.account_id references accounts.id many to one
• ownership_history.changed_by references users.id many to one
• ownership_history.contact_id references contacts.id many to one
• ownership_history.from_user_id references users.id many to one
• ownership_history.lead_id references leads.id many to one
• ownership_history.opportunity_id references opportunities.id many to one
• ownership_history.to_user_id references users.id many to one
• pipeline_stages.pipeline_id references pipelines.id many to one
• price_book_entries.price_book_id references price_books.id many to one
• price_book_entries.product_id references products.id many to one
• quote_line_items.product_id references products.id many to one
• quote_line_items.quote_id references quotes.id many to one
• quotes.created_by references users.id many to one
• quotes.opportunity_id references opportunities.id many to one
• teams.manager_id references users.id many to one
• teams.parent_team_id references teams.id many to one
• users.manager_id references users.id many to one
• users.team_id references teams.id many to one

How to use this template

1. Open the ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the CRM database schema?

24 tables and 59 relationships, across 216 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, as PostgreSQL, MySQL, or SQL Server DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.