Separate aggregates by responsibility
CRM database design in Indotalent’s Blazor CRM Source Code keeps five responsibilities in separate tables: the lead opportunity, the people and activities attached to it, the campaign plan with its money rows, the sales team structure, and the shared customer master data. None of these is folded into a single wide deal table.
This guide examines the source model and its EF Core configurations. It is a logical design walkthrough of the entities and foreign keys, not an exported schema from a running production database. The CRM Database Schema article goes deeper into individual property types and configured indexes.
Read the core CRM relationships
The diagram lists the foreign-key properties from the entity classes. Every CRM relationship in the inspected configurations is mapped from the child side, and the FK properties are nullable strings that reference the parent’s string Id. Lead and Campaign both point to SalesTeam, which means the diagram uses two arrows to the same parent.
Keep the lead opportunity in one row
The Lead table carries company profile, dates, amounts, scores, pipeline stage, and closing status in one row, then references Campaign and SalesTeam. The references give each lead a plan and an owner team without repeating team or campaign data on the lead. Because the child collection lives on the parent entity, queries that need campaign title or team name include the navigation to the referenced row.
One practical consequence: many leads can reference the same campaign, and reports can group by campaign without storing campaign attributes on every lead. The same holds for sales teams.
Attach people and follow-ups to a lead
LeadContact and LeadActivity are child tables of Lead, each with a LeadId foreign key. A person belongs to one lead, and an activity belongs to one lead. The lead entity exposes both collections as navigation properties, but the pipeline UI manages contacts and activities on separate tab screens with a lead selector rather than nested child editors on the lead form.
Designing them as separate tables keeps the lead row readable and keeps follow-up history queryable independently. A list of phone calls and meetings, for example, is a query over LeadActivity with its type and date fields, not a scan of free text on the lead.
Connect campaigns to leads, budgets, and expenses
Campaign is a parent that references SalesTeam and owns three child collections: LeadList, BudgetList, and ExpenseList. Budget and Expense rows carry a CampaignId, so money is always attached to a plan. The child-side configuration for both Budget and Expense maps the relationship to Campaign with OnDelete(DeleteBehavior.NoAction), and both sides of the model are declared in the entity configuration files in this codebase.
Keep that in mind when you extend the model: budget and expense are planned and actual money rows for a campaign, while a lead is a commercial opportunity. If you need an expense linked to a specific lead, that link is an additional relationship to design, not something the current tables express.
Apply team structure to deals and plans
SalesTeam references no parent but owns a collection of SalesRepresentative rows, each storing a SalesTeamId. Both Lead and Campaign reference the same team table, so the assignment structure is uniform: a campaign belongs to a team, and each deal in that campaign can be assigned to the same or another team. Representative details stay out of the team row, which keeps the team list and the person list independently manageable.
This design answers a common question before you extend: does the new information describe the team, a person, or a deal? Put it on the table whose lifecycle it follows.
Treat lifecycle rules as part of the design
All CRM entities inherit audit fields, an Id, and IsDeleted from BaseEntity. AppDbContext converts deleted rows into soft-delete updates and installs a query filter that normally hides deleted rows. That means a delete is not a database DELETE; reporting and historical queries must consider the filter. Soft deletion and the configured DeleteBehavior.NoAction foreign keys are different mechanisms, and both appear in the inspected source.
Stage and status lifecycles are also design decisions: leads move through seven pipeline stages and three closing statuses defined as enums, and closing is expressed as updated columns on the lead rather than a separate deal table. A design that needs immutable audit of every stage change would add a history table; the current model keeps only the current values plus the standard audit trail.
Use the design to review the application
Follow one scenario end to end: a campaign row references a team, budget and expense rows reference the campaign, leads reference the campaign and a team, contacts and activities reference a lead, and closing values are edited on the lead. The C# business model guide explains what each row means, while Building a CRM with ASP.NET Core shows how the entities are registered and queried. See this architecture implemented in a complete Blazor CRM application.