Data normalisation explained with worked examples

Sooner or later, every builder meets the spreadsheet. You’ve carefully planned separate Contact and Deal data types for your CRM, and then someone in the business says: “Right now we track all of this in one big sheet. Name, email, deal name, deal value, date, phone, all in one row.” Is that wrong? Should your database look like their spreadsheet?

That question is the door into data normalisation, and the honest first answer is: maybe, maybe not. It depends on the business logic you’re trying to capture, and there’s a set of questions that settles it.

Data normalisation is structuring your database so each piece of information is stored once: entities are split into their own tables and linked with relationships, instead of repeated across the rows of one big table. It’s the foundational idea of a relational database, and the structure to aim for in almost every app you build.

The questions that decide your structure

Before judging the spreadsheet, interrogate the relationship it’s hiding:

• What is the relationship between contacts and deals?
• Can a contact only ever have one deal, or more than one?
• Can they have more than one at the same time, or one at a time but many over the years?
• Can a deal ever belong to more than one contact?
• What are the statuses of a deal, and do we need to track them as the deal moves through stages?

The answers tell you how many entities you really have and what the business actually needs. And to be fair to the spreadsheet: if you’re a one-person business tracking inbound leads, one flat sheet with a row per lead genuinely works. It stops working when you grow: when three people share the data, five things are on the go at once, or Will comes back wanting a second project and you have nowhere to put it.

Denormalised versus normalised, honestly compared

Denormalised data is the one-big-table shape: every field you need, all in one place, with repetition. If Will has five deals, Will’s details appear on five rows. It duplicates data and creates redundancy you don’t need; try building a filter dropdown on that table and you’ll get seven Wills where there’s really one. But it’s not useless: denormalised structures are genuinely good for analytics, and they often carry aggregated fields like the total value of all the deals.

Normalised data is the split-out shape: Will’s data stored once on a Contact row, and deal one, deal two each linking to Will; deal three linking to Aidan. Redundancy drops away, filters behave, and every fact lives in exactly one place. This is the ideal structure for a transactional database, which is what we’re building in Bubble the vast majority of the time.

If you’re a spreadsheet person, here’s the translation: normalised data is a workbook with multiple tabs, one per entity, where the tabs are joined with VLOOKUPs. Denormalised data is one giant tab holding everything.

The rule of thumb: normalise for apps; consider denormalising when the purpose is analytics.

The three common relationships

Once data is split into entities, relationships connect them, and they come in three shapes.

One-to-one. Imagine each contact only ever had one deal: one contact, one deal. Real use cases include:

• Two data types that represent the same concept, deliberately split, for example so each half can carry different privacy rules, or to improve query efficiency (a lean Profile type and a heavier Portfolio type, linked one-to-one).
• Two data types that represent different concepts with a direct link. The classic is User and User Profile: the user controls access to the app, the profile holds the publicly displayed information. Different jobs, one link each.
• A temporary reference you want to persist beyond the page reload or session, like storing the user’s current job on the user. Only ever one at a time, updated as it changes.

One-to-many. Far more likely for our CRM: Will has deal one, comes back a year later for deal two, then deal three. One-to-many relationships are usually hierarchical: a higher-level concept with lower-level rows attached. An order and its order line items. A report, its report areas, and each area’s report criteria: parent, child and grandchild in one structure.

Many-to-many. Think social media: one profile sends friend requests to many profiles, and each profile receives requests from many others. Or blog tagging: an article carries several tags, and each tag belongs to many articles. Both sides can have many of the other.

Four options for referencing data types in Bubble

Knowing the shape of the relationship is half the job. The other half is choosing how to store it. For the CRM (one contact has zero, one or many deals; a deal has exactly one contact) Bubble gives us four options.

Option 1: the child knows its parent. On Deal, create a field called contact with type Contact. Each deal stores who it belongs to.

Option 2: the parent knows its children. On Contact, create a field called deals, choose type Deal, and tick This field is a list. Each contact now holds a list of the deals it owns.

Option 3: both know each other. Do both of the above: the deal has its contact field, the contact has its list of deals.

Option 4: a link table. Create a third data type, Contact Deal, sitting between the two. It has a field contact (type Contact) and a field deal (type Deal), and each row captures one pairing, plus any extra fields describing that relationship.

When to use which

Child knows parent is the best approach generally, and close to essential. The reason comes back to how you read data. Searches, filters and sorts all hang off constraints, and you can filter deals by contact only because the deal knows its contact. The same applies in the App Data tab: with the parent stored on the child, you can open your list of deals and ask for all the deals belonging to one contact. Without it, that search is impossible, and your app becomes genuinely hard to debug and manage. It also prevents orphaned records: if someone deletes a parent and the children carried no reference to it, you’re left with rows that hold valuable data but have lost all context. Twenty thousand orphaned records is not a hypothetical, and there’s nothing useful you can do with them.

Parent knows children buys you fast retrieval. The stored list saves a database search: the contact already knows its deals, no Do a search for required. It’s convenient in workflows too, because the list is right there. This is why an order often stores its line items, or an invoice its invoice lines.

Both ways gives you the advantages of both: the searchability of option 1 and the fast retrieval of option 2. The cost is discipline. Every time you create or update the relationship, you must do it in both places; miss one side and the two versions of the truth drift apart. Maintained properly, it’s a really helpful pattern.

The link table handles complexity and time. Beyond enabling many-to-many, a link table lets you track how a relationship changes over time. Consider a patient and a doctor. If the patient row simply has a doctor field and the patient changes doctors, do you just overwrite Doctor A with Doctor B? Then how do you know the patient ever saw Doctor A? With a link table, each row records a pairing with its own dates: Doctor A plus the patient, start date, end date; then a new row for Doctor B. (In data-warehouse language this is a slowly changing dimension.) In the CRM, the same logic applies if deals get passed between contacts or between salespeople.

You don’t have to start there. It might be enough during your MVP for the patient to know only their current doctor (in which case name the field current doctor, not doctor, so the meaning is explicit) and add the link table later, when audit or reporting demands the history.

One thing Bubble won’t do for you

Whichever option you choose, remember: creating the reference does not enforce the relationship. Bubble doesn’t know your contact-to-deal link is meant to be one-to-many or many-to-many. You have to write the logic that keeps the rules true, on every workflow that creates or updates those records. That’s also why it’s worth documenting your business logic outside the app: so you, and whoever inherits the project, can understand what the structure is meant to do.

This skill in the AI era

Most of the tangled apps that arrive on our bench have the same root cause: a denormalised structure that grew until filters broke and nobody could tell which of the seven Wills was real. Untangling that, and deciding whether the fix is a child-knows-parent field or a proper link table, is the heart of our Bubble rescue and migration work, and AI hasn’t changed the diagnosis one bit. What it has changed is the cost of the fix: with Claude Code, a well-specified normalisation can be scripted and migrated in hours. The judgement about what to normalise remains a human skill, and it’s one that moved straight across from Bubble to code.