How to Model an Insurance Policy Without Joining Dozens of Tables

Ask an underwriter to describe a policy, and you’ll get a coherent answer: A contract, for a customer, covering a car or a house, with a handful of coverages, each with a limit and a deductible, plus a history of every change since it was issued. One policy—with parts. Many parts.

Now ask your database the same question. In most insurance core systems, that single policy is sliced across dozens of relational tables: one each for coverages, vehicles, named parties, endorsements, and billing schedules. Every time a screen loads, the system reassembles the policy by joining those tables back together. Every time a product changes, several of the tables need altering. When Manulife rebuilt its policy servicing platform, its chief architect described replacing more than 500 core tables with one document per policy and reported that the new system won overwhelming approval from its developers.

That gap—between how the business thinks about a policy and how the database stores it—is the central problem a document database like MongoDB solves. This blog post explains the one design decision that matters most when you make that move and why it’s an insurance decision as much as a technical one.

One rule drives everything

A document database stores records as nested structures, closer to a folder than a spreadsheet row. A policy document can hold its coverages, its vehicles, its parties, and its event history all within itself. The temptation is to simply pour the old table diagram into this new shape. That’s the wrong instinct.

The core idea is to model the data around what the business does with it, not around how the entities look on a diagram. Concretely, that means designing for the policy lifecycle (quote, underwrite, issue, bill, endorse, renew, cancel, claim) and asking, at each step, what gets read and written together.

Once you ask that question, every design choice reduces to one decision: whether to embed or reference.

Embed or reference: The decision that matters

For any two related pieces of data, you have two options. Embed one inside the other, so they live in a single document. Or reference one from the other, storing just an identifier that points to a separate document, the way a relational foreign key does.

The test can be reduced to two very simple statements:

• Embed data that is part of something and is always read alongside it.

• Reference data that has its own lifecycle or is shared across many records.

Apply that to a policy and the answers fall out naturally. Coverages belong inside the policy. Nobody ever looks up collision coverage without the policy it sits within. The insured vehicle belongs inside the policy—as does the event history, because an endorsement or a cancellation is meaningless apart from the contract it changed. In document form, that looks like this:

Anyone in the business can read that. It looks like the policy.

The claim follows the same logic, with a payoff that finance teams will appreciate. A claim’s reserves and payments belong inside the claim document. Because a single document is always written atomically, posting a reserve and updating the claim’s total becomes one operation that either fully succeeds or doesn’t happen at all. There’s no window where the ledger and the claim disagree.

Now let’s look at the other side of the test. A customer’s policies should not be embedded inside the customer record. Policies are large, they change constantly, and they’re almost always looked up by policy number rather than through the customer record. Instead, store policies in their own collection and have each policy reference its customer by ID. Claims should not be embedded inside policies either, because a claim can outlive the policy that spawned it: a canceled policy can still have open claims. Store claims separately, with each claim referencing the policy it belongs to.

The agent who sold the policy and the product it was written on should also be referenced, not embedded, and here the reasoning is operational. Thousands of policies share one agent and one product. Embed the agent’s details, and a change to their license number becomes a write across every policy in their book. Keep the product catalog as the single, versioned source of truth, and let the policy store only a point-in-time snapshot of the coverage terms it was bound against.

Two storms that test the design

Insurance also stress-tests data models in a way few other industries do: catastrophes.

The hurricane claim

Most auto and property claims carry fewer than 10 supporting attachments (photos, estimates, or a police report, for example). Embedding them in the claim is fine. But a major-loss claim after a hurricane can accumulate 60 or more attachments. MongoDB has a hard 16 MB size limit per document, but even well below that, a bloated minority of claims slows down every screen that loads them.

The wrong fix is to move all claim documents into a separate collection “just in case,” penalizing the 99% for the 1%. The right fix is what MongoDB’s engineers call the outlier pattern: embed by default, and when a claim crosses a threshold, flip an overflow flag and store the excess in a side collection. The design serves the common case and handles the exception explicitly.

Here’s what that looks like for a hurricane claim. Once the claim crosses the threshold, the application sets the flag and moves the extra documents to the side collection, so the claim record stays small:

The wildfire intake surge

When a database outgrows one server, MongoDB splits it across several, and a field called the shard key decides which server each record lands on. It seems sensible to shard claims by catastrophe (CAT) event code, since the CAT dashboard queries by it. It’s a trap. During an active wildfire, nearly every new claim for days shares one CAT code, so every new claim routes to one server—precisely when intake throughput matters most. Use a hashed shard key on the claim number to spread new claims evenly, and keep the CAT code as an ordinary index for the dashboard.

Both examples carry the same lesson: The data model is where operational resilience is decided, long before the storm arrives.

Three patterns to adopt on day one

Beyond the embed-versus-reference rule, there are three more design patterns to adopt:

1. Computed totals. Store “total incurred” and “total paid” on the claim rather than re-summing reserves and payments on every read. Claim detail is among the busiest screens in the company; don’t make it do math.

2. Schema versioning. Stamp every policy and claim with a version number. Insurance records survive for decades under regulatory retention and litigation discovery, and your document shape will change over that time. Without a version marker, that drift becomes an audit problem, not just a technical one. Travelers Insurance is reported to have eliminated a three-day wait for schema changes after moving off a rigid relational model; versioning is what makes that agility safe.

3. Append-only history. Policy events, claim activity, and increasingly, the step-by-step decision trace of any automated underwriting or fraud-screening agent should only ever be added to, never edited or deleted. In most industries, that’s good hygiene. In insurance, it’s compliance. As regulators turn their attention to AI-driven decisions, an auditable trace of what an automated system decided and why will be the difference between a quick review and a drawn-out one.

What this means for you

None of this requires your engineers to abandon what they know. Multi-document transactions still exist for the moments that need them, such as binding a submission into a policy. The shift is in the starting question. Instead of “What are the entities and how do they relate?” it becomes, “What does the business read and write together, and what changes on its own?”

Keep what’s always read together, well, together. Separate what lives its own life. Plan explicitly for the outlier and the surge. Get those right, and the database finally describes a policy the way your underwriters do.

References:
1. Why Leading Insurer Manulife Ditched SQL For MongoDB
2. Travelers Insurance speeds up business ask-to-production process with MongoDB