Designing a CRM Database Schema in MySQL

Shaz3e blog cover on designing a crm database schema in MySQL, showing rows of vintage file folders in a dim archive.

A crm database schema looks simple on a whiteboard: contacts, companies, deals. It gets harder the first time a deal ends up pointing at a stage from the wrong pipeline, a deleted contact blocks a new one with the same email, or a query returns another tenant’s rows because somebody forgot a WHERE. Most of those bugs can be stopped in the schema instead of in code review.

This is the hands-on version. My guide to building a CRM covers the architecture and the product side. This post is about MySQL schema design in practice: the actual tables, the keys that hold them together, the indexes behind the list views, and checks that show which mistakes the database rejects. Everything below ran on MySQL 8.4.11 (the LTS line, default utf8mb4_0900_ai_ci collation and strict SQL mode). The full files are schema.sql, seed.sql, bulk.sql, queries.sql and checks.sh.

The tables in this CRM data model

TableWhat it holds
tenantsOne row per customer account. Every other table carries tenant_id.
users, roles, role_userPeople who log in, and what they’re allowed to do.
companies, contactsWho you sell to. Contacts can belong to a company, or not.
pipelines, stages, dealsSales pipelines, their ordered stages, and the money moving through them.
activitiesThe timeline: notes, calls, emails, meetings on any record.
tasksThings someone has to do, optionally tied to a contact or a deal.
tags, taggablesFree-form labels on contacts, companies and deals.
custom_fieldsPer-tenant field definitions. Values live in a JSON column.
audit_log, deal_stage_changesWho changed what, and how deals moved between stages.

Primary keys are BIGINT UNSIGNED AUTO_INCREMENT. UUIDs work too, but they make every secondary index bigger. If you need unguessable public IDs, add a separate column.

Multi-tenant keys: make tenant_id part of every foreign key

This is the decision I’d make first. In a shared-database setup (the trade-offs are in my post on multi-tenant SaaS architecture), every row carries tenant_id. The usual mistake is to add the column and stop there. Then deals.contact_id is a plain foreign key to contacts.id, and nothing stops a deal in tenant 1 from pointing at a contact in tenant 2.

The fix is a composite unique key on each parent, (tenant_id, id), and composite foreign keys on each child. Here’s deals:

CREATE TABLE deals (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id    BIGINT UNSIGNED NOT NULL,
  pipeline_id  BIGINT UNSIGNED NOT NULL,
  stage_id     BIGINT UNSIGNED NOT NULL,
  contact_id   BIGINT UNSIGNED NULL,
  company_id   BIGINT UNSIGNED NULL,
  owner_id     BIGINT UNSIGNED NULL,
  title        VARCHAR(190) NOT NULL,
  amount       DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  currency     CHAR(3) NOT NULL DEFAULT 'USD',
  expected_close_on DATE NULL,
  closed_at    TIMESTAMP NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at   TIMESTAMP NULL,
  UNIQUE KEY deals_tenant_id (tenant_id, id),
  KEY deals_board (tenant_id, pipeline_id, stage_id, deleted_at),
  KEY deals_owner (tenant_id, owner_id, expected_close_on),
  CONSTRAINT deals_stage_fk   FOREIGN KEY (tenant_id, pipeline_id, stage_id) REFERENCES stages (tenant_id, pipeline_id, id),
  CONSTRAINT deals_contact_fk FOREIGN KEY (tenant_id, contact_id) REFERENCES contacts (tenant_id, id),
  CONSTRAINT deals_company_fk FOREIGN KEY (tenant_id, company_id) REFERENCES companies (tenant_id, id),
  CONSTRAINT deals_owner_fk   FOREIGN KEY (tenant_id, owner_id) REFERENCES users (tenant_id, id),
  CONSTRAINT deals_amount_chk CHECK (amount >= 0)
) ENGINE=InnoDB;

Two things are going on. deals_contact_fk uses (tenant_id, contact_id), so the contact has to exist in the same tenant. And deals_stage_fk covers three columns, so the stage has to belong to the deal’s pipeline as well as its tenant. The stages table has a matching unique key on (tenant_id, pipeline_id, id). In the checks, a deal pointing at another tenant’s contact and a deal with a stage from a different pipeline both fail with error 1452.

From the MySQL foreign key docs: if any column in a composite foreign key is NULL, the constraint isn’t checked. That’s what you want for optional links like company_id. It also means tenant_id must be NOT NULL everywhere, or the whole safety net goes away.

This doesn’t replace scoping queries by tenant in the application. MySQL has no row-level security, so your app still adds WHERE tenant_id = ? to every query (in Laravel that’s usually a global scope, as in my single-database multi-tenancy tutorial). What the keys add is that a bug in that scope can’t write cross-tenant links.

Users and roles

Users get UNIQUE (tenant_id, email), so one person can be a user in two accounts. Roles are per tenant, and role_user uses the same composite keys, so a tenant 2 role can’t be given to a tenant 1 user. Keep permissions in code (role name to allowed actions) until customers ask for custom roles, then add a permissions table.

Contacts, soft deletes and the unique email problem

CRMs soft-delete. People delete a contact by accident, and the activity history should survive. The standard deleted_at column clashes with one rule you almost always want: one live contact per email per tenant. A plain UNIQUE (tenant_id, email) blocks re-adding an email after the old contact was deleted.

MySQL lets several rows share a NULL in a unique index, so the trick is a generated column that is NULL once the row is deleted:

active_email VARCHAR(190) AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL,
UNIQUE KEY contacts_tenant_id (tenant_id, id),
UNIQUE KEY contacts_tenant_active_email (tenant_id, active_email),

The checks confirm the behavior: a second live AYESHA@northwind.test is rejected (1062; the default collation is case-insensitive, so case doesn’t help), the same email in another tenant is fine, re-adding an email after a soft delete works, and restoring the old row while a live duplicate exists fails. That last failure is correct. Your restore flow has to offer a merge. Duplicates come in from every intake path, which is why keeping contact data clean is mostly about catching them at entry.

Pipelines, stages and deals

Stages have a position (unique per pipeline) and an outcome of open, won or lost. Putting the outcome on the stage rather than the deal means a team can rename “Won” to “Signed” without breaking reports. Lead status and deal stage are different things, and EloSync’s write-up on deal stage vs lead status covers that from the user’s side. In this schema a lead is a contact without a deal yet.

Money is DECIMAL(15,2) with a CHAR(3) currency, never FLOAT. A CHECK (amount >= 0) rejects negative values, and MySQL 8.0.16+ does enforce CHECK constraints (error 3819 in the checks). The board query that feeds a React Kanban pipeline groups open deals by stage:

EXPLAIN SELECT s.id AS stage_id, s.name, COUNT(d.id) AS deals, COALESCE(SUM(d.amount), 0) AS total
FROM stages s
LEFT JOIN deals d
  ON d.tenant_id = s.tenant_id AND d.pipeline_id = s.pipeline_id
 AND d.stage_id = s.id AND d.deleted_at IS NULL
WHERE s.tenant_id = 1 AND s.pipeline_id = 1
GROUP BY s.id, s.name, s.position
ORDER BY s.position;

Activities are polymorphic, tasks are not

activities is polymorphic: subject_type plus subject_id, like Laravel’s morph relations. One table, one index for every timeline, and adding a new record type is an ENUM change. The cost is that the database can’t enforce subject_id, since a foreign key can only point at one table. Deleting a deal leaves orphan activities unless your code cleans them up, which is acceptable for an append-only timeline.

CREATE TABLE activities (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     BIGINT UNSIGNED NOT NULL,
  subject_type  ENUM('contact','company','deal') NOT NULL,
  subject_id    BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  kind          ENUM('note','call','email','meeting') NOT NULL,
  body          TEXT NULL,
  occurred_at   DATETIME NOT NULL,
  KEY activities_timeline (tenant_id, subject_type, subject_id, occurred_at),
  CONSTRAINT activities_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT activities_user_fk   FOREIGN KEY (tenant_id, user_id) REFERENCES users (tenant_id, id)
) ENGINE=InnoDB;

tasks uses an “exclusive arc” instead: one nullable foreign key per parent type and a CHECK that at most one is set. Tasks get edited, reassigned and completed, and a task pointing at a deal that doesn’t exist is a real bug, so I want the foreign keys. A task linked to both a contact and a deal fails with 3819.

CONSTRAINT tasks_one_parent  CHECK ((contact_id IS NOT NULL) + (deal_id IS NOT NULL) <= 1)

Tags use the polymorphic pattern too (taggables), with a composite foreign key to tags so the tag at least belongs to the right tenant.

Custom fields: EAV vs JSON columns

Every CRM customer wants fields you didn’t plan for. The two common designs:

EAV (a values table)JSON column
Shapecustom_field_values(entity_id, field_id, value_text, value_number, value_date)contacts.custom JSON, keys defined in custom_fields
Reading one recordOne join or extra query per recordAlready on the row
FilteringJoins per filter, typed columns are indexableFunctional index per field you filter on
Type safetyTyped value columnsValidate in the app against custom_fields
FitsLots of fields filtered in many combinationsFields mostly shown and edited, a few filtered

I went with JSON. Most custom fields are displayed and edited, rarely filtered, and loading a contact shouldn’t need a join. For a field people do filter on, MySQL supports functional key parts. You can mix them with normal columns, so the index starts with tenant_id:

KEY contacts_lead_source (tenant_id, (CAST(custom->>'$.lead_source' AS CHAR(40)) COLLATE utf8mb4_bin)),

The COLLATE utf8mb4_bin matters because ->> returns a binary-collated string. With it, a plain WHERE custom->>'$.lead_source' = 'referral' used the index in my test: ref access, 100 estimated rows, which matched the 100 rows with that value. For values that cover a third of the tenant’s contacts, the optimizer chose the tenant index instead, which is reasonable. A functional index is only worth it for selective values.

Indexes for the list views

Design indexes from the screens, not the tables. To get real plans I loaded 50,000 contacts and 20,000 deals into tenant 1 with bulk.sql and ran EXPLAIN on each list query. (My post on database indexing for Laravel developers covers composite index order in more depth.)

ScreenIndexEXPLAIN result
My contacts, newest first(tenant_id, owner_id, deleted_at, created_at)contacts_owner_list, range, backward index scan, no filesort
Kanban board(tenant_id, pipeline_id, stage_id, deleted_at)deals_board on all four columns
Deal timeline(tenant_id, subject_type, subject_id, occurred_at)activities_timeline, backward index scan
Filter by lead source(tenant_id, (lead_source expr))contacts_lead_source, ref

The contacts list uses keyset pagination (created_at < ?) rather than OFFSET, because a deep offset reads and discards every skipped row. The board query reported “Using temporary; Using filesort” on stages, which is four rows. That’s fine. I’m not quoting timings, because this dataset says little about production. The plans are there to confirm the right index gets picked.

Audit log and stage history

audit_log is append-only and written by the application, because only the app knows the user and the request. Store a JSON diff of changed fields, not full row copies.

Stage history is the one place I’d use a trigger, because pipeline reports (time in stage, conversion between stages) depend on it being complete, and a trigger catches updates from scripts and admin tools too:

CREATE TRIGGER deals_stage_history AFTER UPDATE ON deals
FOR EACH ROW
BEGIN
  IF NOT (NEW.stage_id <=> OLD.stage_id) THEN
    INSERT INTO deal_stage_changes (tenant_id, deal_id, from_stage_id, to_stage_id, changed_by)
    VALUES (NEW.tenant_id, NEW.id, OLD.stage_id, NEW.stage_id, @app_user_id);
  END IF;
END//

The app sets @app_user_id on its connection before writing. The <=> comparison means only real stage changes are logged. In the checks, moving a deal from Qualified to Proposal wrote one row with the right user, and editing the amount wrote nothing. If you use a connection pool, reset that variable per request, or a later request can be logged under the wrong user.

Running the checks

checks.sh loads the schema and seed into a fresh database, then expects each of 16 statements to succeed or fail with a specific MySQL error code:

MYSQL="mysql -uroot -S /tmp/mysql.sock" ./checks.sh
# ...
# passed=16 failed=0

What I didn’t test

  • MariaDB. Its JSON type and index support differ from MySQL’s, so test there before copying this. I also didn’t run it on MySQL 8.0 or 9.x.
  • Production-sized data or concurrency. The 50,000 rows were for EXPLAIN, not load testing.
  • Data migrations, archiving or partitioning for large tenants.
  • An application layer. Tenant scoping, permissions and custom field validation still live in your code.

FAQ

Should each tenant get its own database instead?

It’s a valid choice with stronger isolation and harder migrations. If you start with a shared database, the composite keys here are what keep tenants apart at the data level.

Do I need a separate leads table?

Not usually. A lead is a contact with no deal yet, plus a status column if your team tracks lead stages. A separate table means copying data across when the lead converts, and that’s where duplicates start.

When would EAV beat JSON for custom fields?

When users build reports that filter on many custom fields in arbitrary combinations. Typed EAV columns can be indexed generically. JSON needs one functional index per field.

If you’d rather use a CRM than build one, EloSync is the CRM I build. Leads, Tasks and ToDos are free. If you’re building your own crm database schema, start with tenant_id in every foreign key. Adding it later means rewriting every foreign key on tables that already hold data.

I’m Shahrukh Akhtar (Shaz3e), CTO & Co-Founder of Diligent Creators Pvt. Ltd., a Technology Executive & Product Builder. I run client delivery and build SaaS products, including EloSync. You can find me on X, LinkedIn, Peerlist and GitHub.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *