Data Modeling — From Tables to ER Diagrams

Every feature you’ll ever build comes down to the same question: where does the data live, and how is it shaped? Get the shape right and the code almost writes itself. Get it wrong and you’ll fight the same bugs forever — like the issuer_id is NULL bug we’ll dissect at the end of this page. We start from “what is a table?” and finish by reading a real slice of the Assure PAT database from memory.

⏱ ~20 min · 🟡 Intermediate · Prerequisites: Foundations, Layered Architecture

🎯 What you’ll be able to do after this page

• Explain a table, row, column, and NULL to anyone, with no jargon
• Tell a primary key from a foreign key, and read the id / org_id pattern on sight
• Recognise one-to-one, one-to-many, and many-to-many relationships — and know when you need a junction table
• Normalise a messy table and explain why in one sentence
• Say what an index is, why it speeds reads, and what it costs
• Read a real Assure PAT ER diagram relationship by relationship

1. From zero: what is a table?

A database table is a spreadsheet. That’s the whole idea. If you’ve used Excel or Google Sheets, you already understand 80% of databases.

• A column is a vertical field — it has a name and a type (e.g. email holds text, is_active holds true/false).
• A row (also called a record) is one horizontal entry — one actual thing. One user. One card. One organization.
• A table is the whole grid: all rows of the same kind of thing.

Here’s the users table as a spreadsheet:

Three rows (three users), four columns (four facts about each user). A real database has dozens of tables like this, all related to each other. Modeling is the craft of deciding what the tables are and how they connect.

Data types — the short version

Every column has a type that constrains what can go in it. You only need a handful:

Types are guardrails: the database physically refuses to put the word "hello" into an int column. That’s a feature — it catches bugs before they happen.

What NULL means — and why it bites

NULL is the database’s word for “no value here / unknown / not set.” It is not zero, and not an empty string "" — it is the absence of any value. Think of a blank cell in a spreadsheet.

This sounds harmless. It is not.

The real Assure bug — profiles.issuer_id is NULL

A profile can be created without an issuer attached — for a multi-brand profile, the issuer isn’t known yet, so profiles.issuer_id is left NULL. Code that assumed “every profile has an issuer” and read profile.issuer_id directly got back NULL, then tried to use it as a real id — and the profile selection silently failed.

NULL always means “the model is telling you this fact might be missing.” When you see a nullable column, ask: what’s the plan when it’s empty? We’ll see the full fix in section 7 — it’s the spine of this whole page.

2. Keys — how rows find each other

A table full of rows is useless if you can’t point at one specific row, or connect rows across tables. That’s what keys are for.

Primary key (PK) — the unique name tag

A primary key is a column whose value is unique for every row and never empty. It’s the row’s permanent ID badge. In Assure, almost every table has one:

• organizations.id
• users.user_id
• profiles.id

No two users share a user_id. Given a user_id, the database can find that exact row instantly. A PK answers: “which row do you mean?”

Foreign key (FK) — a pointer to another table

A foreign key is a column that stores the primary key of a row in another table. It’s a pointer — a link.

profiles.org_id is a foreign key. It holds an organizations.id. It means: “this profile belongs to that organization.”

erDiagram
    ORGANIZATION ||--o{ PROFILE : "owns"
    ORGANIZATION {
        int id PK
        string name
    }
    PROFILE {
        int id PK
        int org_id FK
        string profile_name
    }

Read it: PROFILE.org_id points back to ORGANIZATION.id. Follow the pointer and you’ve found the owner.

The id / org_id pattern — memorise this

Open almost any Assure table and you’ll see the same two columns:

• id (or user_id, issue_id…) — this row’s own primary key.
• org_id — a foreign key to organizations, marking which tenant owns this row.

That org_id everywhere is multi-tenancy in the data layer: every row knows which organisation it belongs to, so one customer can never see another’s data. You’ll meet this again on the APIs & Multi-Tenancy page — but it starts here, as a foreign key.

3. Relationships — the three shapes

There are only three ways two tables can relate. Learn these three and you can model anything. (Crow’s-foot recap: the symbol nearest a table says how many of that table take part — || exactly one, o{ zero-or-many, |{ one-or-many.)

3.1 One-to-one (1:1)

Each row on the left matches at most one row on the right, and vice-versa. Rare, but used when you split rarely-used or sensitive columns into a side table.

erDiagram
    USER ||--o| USER_PROFILE_EXTRA : "has"
    USER {
        int user_id PK
        string email
    }
    USER_PROFILE_EXTRA {
        int user_id FK
        string bio
    }

Read it: “one user has zero-or-one extra-profile row; that row belongs to exactly one user.”

3.2 One-to-many (1:N) — the workhorse

One row on the left owns many rows on the right; each right-row belongs to exactly one left-row. This is the most common relationship in any database.

In Assure: one organization has many users; one issuer has many bins.

erDiagram
    ORGANIZATION ||--o{ USER : "employs"
    ISSUER ||--o{ BIN : "owns"
    ORGANIZATION {
        int id PK
        string name
    }
    USER {
        int user_id PK
        int org_id FK
    }
    ISSUER {
        int id PK
        int org_id FK
    }
    BIN {
        int id PK
        int issuer_id FK
    }

The “many” side always carries the foreign key. USER.org_id points up to its one organization; BIN.issuer_id points up to its one issuer. The child holds the pointer to the parent. Remember that and you’ll never put the FK on the wrong side.

3.3 Many-to-many (M:N) — needs a junction table

Sometimes both sides can have many of the other. A user can belong to many organizations; an organization has many users. You cannot express this with a single FK column — where would you even put it? You need a junction table (also called a join or bridge table) in the middle.

In Assure, users ↔ organizations is exactly this, bridged by user_org_roles:

erDiagram
    USER ||--o{ USER_ORG_ROLES : "membership"
    ORGANIZATION ||--o{ USER_ORG_ROLES : "membership"
    USER {
        int user_id PK
        string email
    }
    ORGANIZATION {
        int id PK
        string name
    }
    USER_ORG_ROLES {
        int user_id FK
        int org_id FK
        int role_id FK
    }

The trick: a many-to-many is just two one-to-many relationships pointing at a table in the middle. Each row in user_org_roles is one fact: “this user is in that org, with that role.” It even carries a bonus column — role_id — so the same person can be an admin in one org and a tester in another. Junction tables are the natural home for “facts about the pairing itself.”

Quick check — model it right

Before you move on to normalization, prove the three shapes stuck. Pick, then peek.

4. Normalization — every fact in exactly one place

Normalization is a fancy word for one plain idea:

The whole of normalization in one line

Don’t repeat data. Store every fact in exactly one place, and point to it from everywhere else.

Why? Because duplicated data drifts. If an issuer’s name lives in 500 rows and the name changes, you now have 500 places to update — and you’ll miss some. Then your data lies to you.

Before — a single fat, repetitive table

Discover Test Issuer is typed out twice. Rename that issuer and you must edit every matching row. This is unnormalized.

After — split the repeated fact into its own table

erDiagram
    ISSUER ||--o{ BIN : "issues"
    BIN ||--o{ CARD : "stamped on"
    ISSUER {
        int id PK
        string issuer_name
        string card_type
    }
    BIN {
        int id PK
        int issuer_id FK
        string bin
    }
    CARD {
        int id PK
        int bin_id FK
    }

Now the issuer’s name exists once in ISSUER. A card points to a bin; a bin points to an issuer. Rename the issuer in one row and every card sees the new name instantly. That’s normalization.

The three “normal forms”, in plain words

You’ll hear “1NF, 2NF, 3NF.” They’re just three rules, each building on the last:

• 1NF — one value per cell. No comma-lists like "discover,dci,pulse" stuffed in one column; no repeating groups. One fact per box.
• 2NF — every column depends on the whole primary key. (Matters when the PK is two columns, like a junction table — no column should depend on just half of it.)
• 3NF — no column depends on another non-key column. If issuer_name is determined by issuer_id, it doesn’t belong in the cards table — it belongs in the issuers table. This is the rule that kills most duplication.

A handy memory hook: “every non-key column depends on the key, the whole key, and nothing but the key.” That single sentence is essentially 2NF + 3NF.

Denormalization — breaking the rule on purpose

Sometimes you deliberately copy data to make reads faster — e.g. caching a count, or storing a snapshot like card_request_selected_profiles.issuer_id even though the issuer could be looked up. That’s denormalization: trading some duplication for speed or for freezing a decision at a moment in time. It’s a valid tool — but a choice, made with eyes open, not an accident. We’ll see Assure do exactly this in section 7.

5. Indexes — the book’s index for your data

Imagine a 900-page book with no index. To find every mention of “issuer” you’d read all 900 pages. That’s a database scanning every row — a full table scan. Painful when a table has millions of rows.

An index is the same thing as a book’s index: a separate, sorted structure that lets the database jump straight to the rows you want without reading everything.

The B-tree intuition

Most indexes are a B-tree — picture a sorted phone book. To find “Sharma” you don’t read from “Aarav” onwards; you flip to the middle, see you’re too early, flip forward, narrow in. Each flip halves what’s left. A million rows is found in ~20 hops instead of a million reads. That halving-each-step behaviour is why indexed lookups feel instant.

When to add one — and what it costs

• Add an index on columns you frequently search by or join on — foreign keys (org_id, issuer_id), and columns in your WHERE clauses.
• The cost: an index must be kept sorted, so every INSERT, UPDATE, and DELETE now also updates the index. Indexes make reads faster but writes slower, and they take disk space. Don’t index everything — index what you query.

A special index — UNIQUE (and the real Assure one)

A unique index does double duty: it speeds lookups and it forbids duplicate values — the database physically rejects a second row with the same combination.

Assure has exactly this on card_request_selected_profiles:

UNIQUE INDEX uq_crsp_request_profile_issuer
    ON card_request_selected_profiles (card_request_id, profile_id, issuer_id)

Read it: “within one card request, you cannot select the same profile for the same issuer twice.” Notice it’s a composite key — three columns together. The same profile can appear twice if it’s for two different issuers (a multi-brand profile!), but the exact triple can never repeat. The database enforces this rule itself, so no buggy code path can ever create a duplicate. The data model defends its own integrity.

6. THE BIG ONE — a real Assure PAT slice

Time to put it together. Below is a real slice of the Assure PAT database — the part that turns organizations, users, profiles, issuers and bins into an actual card request. Don’t panic at the size; we’ll walk it line by line right after.

erDiagram
    ORGANIZATION ||--o{ USER_ORG_ROLES : "members"
    USER ||--o{ USER_ORG_ROLES : "memberships"
    ORGANIZATION ||--o{ PROFILE : "owns"
    ORGANIZATION ||--o{ ISSUER : "owns"
    ORGANIZATION ||--o{ CARD_REQUEST : "owns"
    ISSUER ||--o{ BIN : "issues"
    BRAND ||--o{ BIN : "categorizes"
    BIN ||--o{ CARD : "stamped on"
    PROFILE }o--o| ISSUER : "may belong to"
    PROFILE_TEMPLATE ||--o{ PROFILE : "instantiated as"
    PROFILE_TEMPLATE ||--o{ TERMINAL_TEMPLATE_LINKS : "linked to"
    BRAND ||--o{ TERMINAL_TEMPLATE_LINKS : "for brand"
    CARD_REQUEST ||--o{ CARD_REQUEST_TERMINAL_DETAILS : "configures"
    CARD_REQUEST ||--o{ CARD_REQUEST_SELECTED_PROFILES : "selects"
    PROFILE ||--o{ CARD_REQUEST_SELECTED_PROFILES : "chosen in"
    ISSUER ||--o{ CARD_REQUEST_SELECTED_PROFILES : "resolved to"

    ORGANIZATION {
        int id PK
        string name
        string status
    }
    USER {
        int user_id PK
        string email
        string user_type
    }
    USER_ORG_ROLES {
        int user_id FK
        int org_id FK
        int role_id FK
    }
    PROFILE {
        int id PK
        int org_id FK
        int issuer_id FK
        int source_template_id FK
        string brand
    }
    ISSUER {
        int id PK
        int org_id FK
        string issuer_name
        string card_type
    }
    BIN {
        int id PK
        int org_id FK
        int issuer_id FK
        int brand_id FK
        string bin
    }
    BRAND {
        int id PK
        string name
    }
    CARD {
        int id PK
        int org_id FK
        int bin_id FK
        string status
    }
    CARD_REQUEST {
        int id PK
        int org_id FK
        string status
    }
    CARD_REQUEST_TERMINAL_DETAILS {
        int card_request_id FK
        string terminal_type
        string brand_ids
    }
    CARD_REQUEST_SELECTED_PROFILES {
        int id PK
        int card_request_id FK
        int profile_id FK
        int issuer_id FK
        string source
    }
    PROFILE_TEMPLATE {
        int id PK
        int org_id FK
        int source_system_template_id FK
        string template_name
    }
    TERMINAL_TEMPLATE_LINKS {
        int id PK
        int profile_template_id FK
        int brand_id FK
        string terminal_type
    }

Reading it, relationship by relationship

Take it one line at a time — that’s how professionals read a strange schema:

1. ORGANIZATION ||--o{ USER_ORG_ROLES }o--|| USER — the many-to-many from section 3. One org has many membership rows; one user has many membership rows; each membership row joins exactly one user to exactly one org (with a role_id). A user can work in several orgs.
2. ORGANIZATION ||--o{ PROFILE / ISSUER / CARD_REQUEST — the org_id pattern. Everything is owned by one organization. This is multi-tenancy, drawn out.
3. ISSUER ||--o{ BIN — one issuer owns many bins (the 6-digit prefixes of card numbers). The bin carries issuer_id.
4. BRAND ||--o{ BIN — each bin also belongs to one brand (Discover=9, DCI=14, Pulse US Debit=15). So a bin knows both its issuer and its brand.
5. BIN ||--o{ CARD — one bin is stamped on many physical test cards. A card’s identity flows up: card → bin → issuer + brand.
6. PROFILE }o--o| ISSUER — read the crow’s feet carefully: a profile relates to zero-or-one issuer. That o| is the whole bug. A profile might not have an issuer yet — profiles.issuer_id is nullable.
7. PROFILE_TEMPLATE ||--o{ PROFILE — profiles are instantiated from a template via source_template_id. The template is the blueprint; the profile is the built thing.
8. PROFILE_TEMPLATE ||--o{ TERMINAL_TEMPLATE_LINKS }o--|| BRAND — a template is linked to terminal types per brand. This is how the system knows which terminals/brands a template applies to.
9. CARD_REQUEST ||--o{ CARD_REQUEST_TERMINAL_DETAILS — one request configures terminal details (the brand_ids it targets, as JSON).
10. CARD_REQUEST ||--o{ CARD_REQUEST_SELECTED_PROFILES plus PROFILE ||--o{ ... and ISSUER ||--o{ ... — this junction is the heart of the request. Each selected-profile row ties together one request, one profile, and one resolved issuer. Hold that thought — section 7 is entirely about it.

If you can narrate those ten lines aloud, you can read any ER diagram. The skill never changes — only the table names do.

7. How the model encodes “which issuer for this profile?”

This is the real model behind a bug we debugged — and it’s the perfect capstone, because it uses everything on this page: nullable FKs, brand→bin→issuer joins, a junction table, and a NOT NULL constraint working together.

The problem the model has to solve

A profile describes a card’s behaviour. Some profiles are tied to one issuer — then profiles.issuer_id is filled in. But a multi-brand profile isn’t tied to any single issuer up front, so profiles.issuer_id is left NULL. (Org 260, for instance, has three issuers: Discover Test Issuer (6), DCI Test Issuer (9), Pulse Test Issuer (10) — a multi-brand profile could resolve to any of them.)

So at the moment someone selects a profile into a card request, the system must answer: “which concrete issuer does this profile mean, right here, right now?”

The resolution path: brand → bins → issuers

When profiles.issuer_id is NULL, the system resolves the issuer through the brand. It walks the relationships you just learned:

flowchart TD
    Start(["Select a profile into a request"]) --> Q{"profiles.issuer_id is set?"}
    Q -->|"Yes (single-issuer profile)"| Use["Use that issuer_id directly"]
    Q -->|"No (NULL, multi-brand)"| Brand["Take the profile's brand"]
    Brand --> Bins["Find bins WHERE brand_id matches"]
    Bins --> Issuers["Collect the issuer_id of each matching bin"]
    Issuers --> Count{"Exactly one candidate issuer?"}
    Count -->|"Yes"| Auto["Auto-resolve (source = auto)"]
    Count -->|"No (several)"| Manual["User picks one (source = manual)"]
    Use --> Store
    Auto --> Store
    Manual --> Store["Write row in card_request_selected_profiles<br/>with NOT NULL issuer_id"]
    Store --> Done(["Selection saved, issuer frozen"])

Read the path: brand → bins (filtered by brand_id) → the issuer_id on those bins. If exactly one issuer comes back, the system fills it in automatically (source = auto); if several, the user must choose (source = manual).

Where the answer gets frozen — and why it’s NOT NULL

The resolved issuer is written into card_request_selected_profiles, where issuer_id is NOT NULL. That single constraint is the whole lesson:

The model’s two-layer defence

• profiles.issuer_id is nullable — because at design time, a multi-brand profile genuinely has no single issuer. The model honestly admits “unknown.”
• card_request_selected_profiles.issuer_id is NOT NULL — because at selection time, the issuer must be known. The model refuses to store an unresolved selection.

The bug we hit lived in the gap between those two facts: code read the nullable profiles.issuer_id, got NULL, and never ran the brand→bin→issuer resolution. The fix wasn’t in the database — the model was already correct. The fix was making the code respect what the model was telling it: “this might be NULL; resolve it before you store it.”

And remember the unique index from section 5 — (card_request_id, profile_id, issuer_id). Because the issuer is resolved and frozen here, that index can guarantee no duplicate (request, profile, issuer) triple, while still allowing the same profile to be selected for two different issuers. Nullable source, NOT NULL snapshot, unique constraint — three small modeling decisions that together make a whole feature correct.

🧭 Recap — the mental model to keep

• A table is a spreadsheet; a row is one thing; a column is one fact; NULL means “no value — handle me.”
• PK = this row’s unique id. FK = a pointer to another table’s PK. The child holds the FK.
• Three relationship shapes: 1:1, 1:N (FK on the many side), M:N (needs a junction table).
• Normalize = every fact in one place; denormalize = copy on purpose for speed or to freeze a snapshot.
• Indexes speed reads (B-tree, halving each step), slow writes. UNIQUE indexes also enforce no-duplicates.
• Read any ER diagram one relationship at a time, narrating the crow’s feet aloud.

Do this before moving on

Open the real Assure code and find card_request_selected_profiles. Confirm issuer_id is NOT NULL there, and nullable on profiles. Then redraw the section 7 flowchart from memory. That single exercise will lock in this entire page.

Next up: APIs, Auth & Multi-Tenancy — how that org_id foreign key you saw on every table becomes the wall that keeps one customer’s data invisible to another. 👉

Check Yourself

Cover the answers. Say each one aloud before you peek — retrieval is what makes it stick.

Still Unclear?

Paste any of these into Claude to go deeper on the part that didn’t land:

• “Walk me through designing the tables for a simple feature — say a blog with posts, authors, and tags — and at each step explain whether each relationship is one-to-one, one-to-many, or many-to-many, and where the foreign key goes. Ask me to decide before you reveal the answer.”
• “Show me a table that violates 3NF, then walk me through normalizing it step by step into 1NF, 2NF, and 3NF, explaining in plain words what each step removed and why.”
• “Explain when I should add an index versus when an index will hurt me, using a concrete table with read-heavy versus write-heavy access patterns. Make me predict the trade-off before you confirm it.”

Why AI Can’t Do This For You

AI can generate a CREATE TABLE statement in seconds — but it cannot decide what your tables should be. That decision comes from understanding your domain: knowing that a multi-brand profile genuinely has no single issuer until selection time, so profiles.issuer_id must be nullable while the snapshot must be NOT NULL. The issuer_id is NULL bug wasn’t a missing line of SQL — it was code that didn’t respect what the model was telling it. Reading a strange ER diagram one relationship at a time, spotting that a nullable FK needs a resolution path, and choosing where to denormalize on purpose are judgment calls grounded in your system. A prompt can mass-produce schema syntax; it can’t hold your domain’s truth in its head — that’s the part that keeps you employed.

Module done? Add it to today’s tracker