Data Modeling Process

Data modeling is the discipline of deciding what to represent before deciding how to store it — and it works by descending through three levels of commitment, so that a wrong idea is caught while it is still cheap to change. You move from conceptual (which real-world things exist and how they relate) to logical (the exact tables, columns and keys) to physical (the engine-specific types, indexes and layout). Each level answers questions the one above deliberately refused to, and each is a checkpoint: the earlier a mistake surfaces, the fewer artifacts you have to rewrite to fix it.

The three levels: what each decides, what it ignores

The single most useful thing to internalize is the division of labor. Cramming every decision into one step is exactly how beginners produce brittle schemas.

Level	Decides	Deliberately ignores	Portable to
Conceptual	Entities and the relationships/cardinalities between them (the ER model)	Columns, data types, keys, performance, even whether it’s SQL at all	Any database, any vendor, the whiteboard
Logical	Tables, columns, primary/foreign keys, normalization form	Index choices, physical types, storage, partitioning	Any relational engine (Postgres, MySQL, Oracle)
Physical	Exact SQL types, indexes, partitioning, storage & access paths	Nothing — this is the concrete, runnable schema	One specific engine and version

One running example, traced through the process

Abstractions don’t teach modeling; a traced example does. Take an e-commerce order system from a sentence of requirements to a runnable Postgres schema. Watch the judgment at each step — the “why,” not just the “what.”

Step 1 · Requirements — capture the verbs

You interview the business and hear one sentence: “A customer places many orders; an order lists many products, with a quantity for each.” The nouns become entities (Customer, Order, Product) and the verbs become relationships (places, lists). This is where you write down cardinalities in plain English before any box is drawn — “many orders,” “many products” are the raw material of the model.

Step 2 · Conceptual — entities, cardinality, and a born junction

Draw the boxes and the lines between them:

• Customer 1:N Order — one customer, many orders; each order belongs to exactly one customer.
• Order N:M Product — an order contains many products, and a product appears on many orders.

The judgment: a relational database has no native many-to-many construct, so every N:M relationship forces a junction entity. That is where OrderLine is born — here, at the conceptual level, not improvised later when you’re already writing SQL. And the quantity from the requirement (“a quantity for each”) has an obvious home: it is an attribute of the line, not of Order or Product. Reviewing these boxes with the business is the cheapest checkpoint you will ever get: it is exactly here that a stakeholder says “oh, an order also has a shipping address distinct from the customer’s” — adding an entity now costs one box; discovering it after migrations are written costs a schema change plus a data backfill.

Step 3 · Logical — tables, keys, and normalizing to 3NF

Now give the entities columns, keys, and abstract types (still no engine):

customers  (customer_id PK, name, email)
products   (product_id  PK, name, unit_price)
orders     (order_id    PK, customer_id FK -> customers, order_date)
order_line (order_id FK -> orders,
            product_id FK -> products,
            line_no, qty, unit_price,
            PRIMARY KEY (order_id, line_no))

Judgment — surrogate vs natural key: orders gets a synthetic order_id rather than a “natural” key like (customer_id, order_date). A customer can place two orders in the same second, so the natural key isn’t truly unique; and natural keys change (people mistype dates), while a surrogate never does. Stable, compact, meaningless-on-purpose keys make foreign keys and joins cheap.

Judgment — why 3NF here: normalize so that every fact has exactly one home. A product’s catalog price lives once, on products. If you copied it into every order_line as the source of truth, a price change would mean updating thousands of rows and risking disagreement (an update anomaly). Third normal form removes transitive dependencies: no non-key column may depend on another non-key column. For a transactional order system with heavy writes, 3NF is the right default because it makes each write touch one place and keeps integrity automatic.

A subtlety, not a contradiction: order_line also carries a unit_price. That is not a redundant copy of the catalog price — it is the price at the time of sale, a genuinely different fact that must be frozen so a later catalog change never rewrites history on past invoices. Recognizing when a “duplicate-looking” column is actually a distinct fact is exactly the modeling judgment 3NF is meant to sharpen, not suppress.

Step 4 · Physical — pick the engine and make it fast

Choose PostgreSQL and turn the logical model into a schema that runs well:

• Types: order_id BIGINT GENERATED ALWAYS AS IDENTITY; unit_price NUMERIC(12,2) — never FLOAT for money, because binary floating point cannot represent 0.10 exactly and cents drift; order_date TIMESTAMPTZ.
• Indexes: add them on the FK columns orders(customer_id) and order_line(order_id). Postgres does not auto-create indexes on foreign keys (it indexes only the primary key), so “show this customer’s order history” and “show this order’s lines” would sequential-scan without them.
• Partitioning: only if orders is expected to exceed roughly 100M rows — then range-partition by order_date (e.g. monthly) so queries and archival touch one partition. Below that scale, partitioning is complexity you don’t need.

Steps 5–7 · Validate, implement, refine

Validate against representative volume: load realistic data and run EXPLAIN on your top few queries. If the order-history query shows a Seq Scan, the model or its indexes are wrong — fix it before shipping, not after. Implement the tables, keys, and constraints. Then refine in production: watch pg_stat_statements; if one report query is repeatedly slow and can’t be indexed away, that — and only that — is the evidence that justifies a documented denormalization.

The ordering is not bureaucracy. Skip the conceptual review (Step 2) and you discover a missing entity only after writing migrations. Skip the FK indexing (Step 4) and validation (Step 5) collapses under load. Each level exists to make the next one cheap to get right.

Pitfalls

• Skipping conceptual and modeling tables directly. Jumping to CREATE TABLE means you never validated the entities with the business, so missing entities and wrong cardinalities surface only after migrations exist — the most expensive place to find them.
• Premature physical optimization. Choosing indexes and partitions before you know the query patterns is guessing. Model logically first; let real (or realistic) queries drive the physical choices.
• FLOAT for money. Floating point can’t represent most decimal fractions exactly; sums drift by cents and reconciliations fail. Use NUMERIC/DECIMAL for any exact quantity.
• Over- or under-normalizing. Under-normalizing (copying facts) causes update anomalies; over-normalizing (splitting a table five ways “for purity”) forces joins on every read for no benefit. 3NF is the default; deviate only with evidence.
• Assuming FKs are indexed. In Postgres, Oracle, and SQL Server a foreign key constrains but does not auto-create an index, so the join column needs its own. (MySQL/InnoDB is the exception — it auto-creates an index on FK columns.)

Selection & trade-offs: normalize, denormalize, or stop early

Normalize (3NF) vs. denormalize. Normalization optimizes for write correctness: one fact, one place, no anomalies — the right default for OLTP systems like this order database. Denormalization optimizes for read speed: pre-joining or duplicating data so a hot query avoids joins — useful for read-heavy reporting or analytics, but it buys speed with the cost of keeping the copies in sync (via triggers, application code, or a batch job). The senior move: start normalized, then denormalize a specific query only when profiling proves the join is the bottleneck, and document why the copy exists so the next engineer doesn’t “fix” it back.

When to stop at the logical model. The conceptual and logical models are the durable, portable design; the physical model is disposable and engine-specific. For a design review, an interview whiteboard, or a cross-team contract, stopping at a clean logical model is correct — the physical tuning is an implementation detail you fill in once the target engine and query patterns are known. Descend to physical only when you’re about to actually build.

Takeaways

• Model in three levels so mistakes are caught while cheap: conceptual (what exists) → logical (tables & keys) → physical (engine tuning).
• Anchor to a concrete example: verbs in the requirements become relationships, every N:M forces a junction entity (OrderLine), and 3NF gives every fact one home.
• Physical choices are query-driven: pick exact types (DECIMAL for money), index the FKs your queries join on, and partition only at real scale.
• Normalize by default for write-heavy systems; denormalize only a proven-slow read, and document it.

---

Sources: Elmasri & Navathe, Fundamentals of Database Systems (three-schema architecture); Kleppmann, Designing Data-Intensive Applications (data models & normalization); PostgreSQL documentation (identity columns, foreign-key indexing, table partitioning). Re-authored/Deepened for this guide.

I just read a lesson on **Data Modeling Process** (Databases) and want to truly understand it. Explain Data Modeling Process from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Data Modeling Process** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Data Modeling Process** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Data Modeling Process** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.