How to design a simple CRM data model: tables, fields and relationships

A simple CRM data model needs five tables: Users (the people who own records), Companies, Contacts, Deals and Activities. Contacts belong to a company, deals belong to a company and an owner, activities attach to a deal or a contact, and the pipeline stage is an ordered single select on the deal. Add a stage history table if you want conversion and cycle-time reports, and a deal contacts table if several people matter on one deal.

Below is each table with its fields, the relationships between them, the mistakes that make a CRM unreliable, and a Postgres schema you can run as is. The model works in any database or no-code builder.

What tables does a CRM database need?

Leads are deliberately missing. Many CRMs keep a separate Leads table and convert leads into contacts, which copies data and loses history at the moment of conversion. For a small team, a lead is a contact with no deal yet, or a deal in the first stage.

Which fields go in each table?

These are the fields worth having on day one. Anything else can wait until someone asks for it twice. Users needs only a name, a unique email and an active flag. In an app builder it can double as the list of people who sign in, so owner filters can match the signed-in person.

Companies

Contacts

Deals

Activities

How should CRM tables relate to each other?

Most CRM relationships are one-to-many, and the reference always lives on the "many" side: each contact stores its company, each deal stores its company, each activity stores its deal. A company never stores a list of its contacts, because that list is a query.

The interesting case is many-to-many. A deal involves several contacts (the champion, the budget holder, the person who signs), and one contact can be on several deals. There are two ways to model it:

The join table wins as soon as the relationship has attributes. "Maria is the decision maker on the Acme renewal but only an end user on the Acme pilot" cannot live in a list of links; it needs a row with a Role field. Join tables also make counting easy, because each link is a row you can filter and aggregate.

How do you model pipeline stages?

Stage is a single select on the deal with a fixed, ordered list: Lead, Qualified, Proposal, Negotiation, Won, Lost. Order matters because boards, funnels and reports read left to right. Give each stage an exit rule someone can check ("Proposal: a priced proposal has been sent"), or stages turn into moods.

Keep the list short: four to six open stages plus Won and Lost is a sensible ceiling for a small team. If two stages never get different treatment (a different owner, next step or probability), merge them.

Overwriting the stage field throws away the pipeline's history. You know where each deal is now, but not when it got there, so you cannot answer "what share of proposals are won?" or "how long do deals sit in Negotiation?". A stage history table fixes that with one row each time a deal enters a stage: deal, stage, entered at. Conversion rates, average days per stage and the pipeline as it stood on any past date all come from that one table.

Activities or notes: what is the difference?

Activities are structured events: a type, a date, an owner and a link to a deal or contact. Because they are structured, they drive the useful lists: deals with no activity in 14 days, overdue follow-ups, calls logged per rep this week.

Notes are the free text that explains what happened. Store them on the activity they belong to (a note is an activity of type Note), not in one long Notes field on the contact. A single notes field has no dates, no author and no link to a deal, and two people editing it at once overwrite each other.

Which computed fields are worth adding?

Compute anything that can be derived. A number someone types by hand goes stale the day after they type it.

What are the most common CRM data model mistakes?

• Company name typed as text on each contact. "Acme", "ACME Inc" and "Acme Corp" become three companies in every report. Use a reference.
• One big notes field. No dates, no author, no filtering, and edits that overwrite each other.
• Losing stage history. Overwriting the stage is fine for the board and fatal for reporting. Log stage changes from day one; you cannot reconstruct them later.
• Duplicate contacts. Without a unique email, imports and quick adds create the same person twice and activities split between the copies. Make email unique and compare it case-insensitively.
• Too many statuses. Twelve stages nobody agrees on, or a Stage, a Status and a Phase on the same deal. Use one ordered stage field and a Lost reason.
• Owners stored as names. "Dana" in a text field breaks the day a second Dana joins. Reference the Users table.

A Postgres schema for a simple CRM

The DDL below creates the core tables with primary keys, foreign keys, check constraints for stage and activity type, case-insensitive unique emails, triggers that keep the stage history, and a view with the computed fields. It runs on PostgreSQL 11 or later.

-- PostgreSQL 11 or later
create table users (
  id         bigint generated always as identity primary key,
  full_name  text not null,
  email      text not null,
  active     boolean not null default true
);
create unique index users_email_key on users (lower(email));

create table companies (
  id          bigint generated always as identity primary key,
  name        text not null,
  domain      text,
  industry    text,
  size_band   text check (size_band in ('1-10', '11-50', '51-200', '201-1000', '1000+')),
  owner_id    bigint references users (id) on delete set null,
  created_at  timestamptz not null default now()
);
create unique index companies_domain_key on companies (lower(domain));
create index companies_owner_id_idx on companies (owner_id);

create table contacts (
  id          bigint generated always as identity primary key,
  company_id  bigint references companies (id) on delete set null,
  full_name   text not null,
  email       text,
  phone       text,
  job_title   text,
  status      text not null default 'Active'
              check (status in ('Active', 'Left company')),
  created_at  timestamptz not null default now()
);
create unique index contacts_email_key on contacts (lower(email));
create index contacts_company_id_idx on contacts (company_id);

create table deals (
  id                bigint generated always as identity primary key,
  name              text not null,
  company_id        bigint not null references companies (id) on delete restrict,
  owner_id          bigint references users (id) on delete set null,
  stage             text not null default 'Lead'
                    check (stage in ('Lead', 'Qualified', 'Proposal', 'Negotiation', 'Won', 'Lost')),
  amount            numeric(12, 2) check (amount >= 0),
  expected_close    date,
  stage_entered_at  timestamptz not null default now(),
  lost_reason       text,
  created_at        timestamptz not null default now(),
  check (stage <> 'Lost' or lost_reason is not null)
);
create index deals_company_id_idx on deals (company_id);
create index deals_owner_id_idx on deals (owner_id);

create table deal_contacts (
  deal_id     bigint not null references deals (id) on delete cascade,
  contact_id  bigint not null references contacts (id) on delete cascade,
  role        text check (role in ('Decision maker', 'Champion', 'Influencer', 'Signer', 'User')),
  primary key (deal_id, contact_id)
);
create index deal_contacts_contact_id_idx on deal_contacts (contact_id);

create table activities (
  id          bigint generated always as identity primary key,
  type        text not null check (type in ('Call', 'Email', 'Meeting', 'Task', 'Note')),
  subject     text not null,
  deal_id     bigint references deals (id) on delete cascade,
  contact_id  bigint references contacts (id) on delete cascade,
  owner_id    bigint references users (id) on delete set null,
  happens_at  timestamptz not null default now(),
  done        boolean not null default false,
  notes       text,
  check (deal_id is not null or contact_id is not null)
);
create index activities_deal_id_idx on activities (deal_id, happens_at);
create index activities_contact_id_idx on activities (contact_id, happens_at);

create table deal_stage_history (
  id          bigint generated always as identity primary key,
  deal_id     bigint not null references deals (id) on delete cascade,
  stage       text not null,
  entered_at  timestamptz not null
);
create index deal_stage_history_deal_idx on deal_stage_history (deal_id, entered_at);

-- Stamp the moment a deal enters a new stage.
create function deals_stamp_stage() returns trigger
language plpgsql as $$
begin
  if new.stage is distinct from old.stage then
    new.stage_entered_at := now();
  end if;
  return new;
end;
$$;

create trigger deals_stamp_stage
  before update of stage on deals
  for each row execute function deals_stamp_stage();

-- Log every stage a deal enters, the first one included.
create function deals_log_stage() returns trigger
language plpgsql as $$
begin
  if tg_op = 'UPDATE' then
    if new.stage is not distinct from old.stage then
      return null;
    end if;
  end if;
  insert into deal_stage_history (deal_id, stage, entered_at)
  values (new.id, new.stage, new.stage_entered_at);
  return null;
end;
$$;

create trigger deals_log_stage
  after insert or update of stage on deals
  for each row execute function deals_log_stage();

-- Computed fields, read through a view.
create view deal_overview as
select d.*,
       current_date - d.stage_entered_at::date as days_in_stage,
       current_date - d.created_at::date as deal_age_days,
       (select max(a.happens_at)
          from activities a
         where a.deal_id = d.id and a.done) as last_activity_at
from deals d;

• A check constraint, not an enum, for stage. A Postgres enum sorts in declaration order, which is handy, but existing values cannot be removed or reordered without dropping and recreating the type. A check constraint changes with one ALTER TABLE. If stages change often, a lookup table with a position column is more flexible still.
• Indexes on foreign keys. Postgres does not create an index on the referencing column when you declare a foreign key, so the schema adds one for each column the app filters or joins on.
• Two triggers. The BEFORE trigger stamps stage_entered_at when the stage changes, and the AFTER trigger writes the history row. A trigger declared with UPDATE OF stage fires whenever stage appears in the SET list, even when the value is unchanged, so the function compares old and new before logging.
• Restrict, not cascade, on deals. Deleting a company that has deals fails instead of quietly removing pipeline history.

How to build this CRM in Emberblocks

Every table above maps to Emberblocks field types, which include text, email, phone, number, date, date-time, boolean, select, reference and multi-reference. You can create the tables in the built-in database, or connect a Postgres database that already has this schema and build on its tables directly; a table accepts edits once writes are enabled for it and it has a primary key. Because the stage triggers live in the database, they run whichever app writes the deal. Putting a front end on Postgres covers that route in detail.

The pipeline is a kanban block on Deals with Stage as the lane field. Dragging a card writes the new stage, and only for roles allowed to update that field. Point Card age at Stage entered on and each card shows its days in stage ("12d"), set a WIP limit on lanes that tend to overflow, and draw a progress bar from a 0 to 100 probability field.

On the built-in database, turn on activity tracking: it records every change with the old and new value, and an activity block limited to the Stage field shows a deal's stage changes on its record page. A drag on the board writes only the stage, so if you rely on Stage entered on there, move deals with buttons whose Set value action writes both fields.

Formulas follow the model. [Owner].[Email] = USEREMAIL() gives each rep a My deals view, [Company].[Industry] follows a reference, and COUNT and SUM roll up a child table, so a company can count its deals and total their value. On a deal's record page, a timeline of activities filtered with [Deal] = RECORDID() and a stat tile showing the max of their dates gives the last activity date. The expressions guide lists every function.

One limit to know: multi-reference fields render as chips and are picked from the target table, but formulas cannot read them yet. That is one more reason to model deal contacts as a join table with two reference fields, which COUNT can roll up and filters can narrow.

To weigh building your own CRM against buying one, see the no-code CRM builder page. Emberblocks is the wrong fit if you need inbox sync, email sequences or built-in calling; a dedicated sales CRM does those well. For the data side of moving off a spreadsheet first, turning a spreadsheet into an app walks through the cleanup.