Data Modeling Fundamentals

Data Engineering & Modern Data Platforms · Lesson 09 · Data Engineering Foundations

Lesson Overview

TechLearningPro once ran enrollments from a spreadsheet with a column per lesson and a free-text "payment info" cell. It worked for one cohort. The second instructor reused a course title, a learner changed email addresses, and a team purchase for twelve seats was stored as twelve copies of the payment amount. Revenue was twelve times too high. Progress for lesson 3 lived in a column that lesson 4's import overwrote. The bug was not PostgreSQL. The bug was a model that mixed three different facts in one row and then asked SUM to understand the business.

This lesson is how you stop that class of bug before the pipeline exists. A data model is a decision about what the business considers a thing, how those things relate, which attributes belong to which thing, and which of those decisions the database is allowed to enforce. You will separate three layers that teams often collapse into one CREATE TABLE. The conceptual model names entities and relationships in the language of learners, orders, seats, and courses, with no column types. The logical model names attributes, keys, and cardinality, still without committing to PostgreSQL types or index types. The physical model is the tables, types, constraints, and indexes that Relational Database Internals taught you to cost. A column type is a physical choice. "An order has many lines, and a line is for one course" is a logical choice. "We sell seats in courses to learners and to teams" is a conceptual choice. Changing a uuid to a bigint does not fix a model that stored the payment on every seat.

Normalization is the discipline that puts each fact in one place so an update cannot contradict itself. Analytics often wants the opposite shape: fewer joins, facts repeated on purpose, tables designed for a query grain. Both can be correct, and they are correct in different systems. This lesson keeps the operational store in third normal form, with named exceptions, and it shows the denormalized read models you are allowed to build beside it. The next lesson, Dimensional Modeling and Data Warehousing, is the analytical model. Module 2's ETL and ELT jobs will move rows from the model you design here into that one. If the source grain is wrong, the warehouse will be a faster copy of the wrong number.

Learning Objectives

By the end of this lesson, you should be able to:

1. Separate conceptual, logical, and physical models, and say which review comments belong in which layer.
2. Identify entities, attributes, and relationships in TechLearningPro's commerce and learning domain, including associative entities that carry facts of their own.
3. State cardinality and optionality, and translate a many-to-many relationship into a logical entity with a primary key.
4. Choose a surrogate key and a natural key together, and place a uniqueness constraint on the natural key that the business actually means.
5. Explain first, second, and third normal form with a dependency, and give one Boyce-Codd example from this domain.
6. Name an update, insert, and delete anomaly that a specific denormalized table would suffer.
7. Decide when a repeated attribute is a stored historical label, which is a different fact, rather than a transitive dependency.
8. Place constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, NOT NULL) so the database rejects rows the model says cannot exist.
9. Split operational tables from append-only events, and explain why a progress percentage column is a different fact from a completion event.
10. Shape a physical PostgreSQL design that matches the logical keys without indexing every column the internals lesson told you not to index.
11. Compare a normalized write model with a denormalized read model, and say who is allowed to rebuild the read model.
12. Review a schema change for grain, anomalies, and compatibility with the pipelines Module 2 will run.
13. Produce an order-domain model a warehouse grain in lesson 10 can sit on without reinterpreting "what is an order."

Prerequisites

• Relational Database Internals: pages, indexes, vacuum, and the cost of updating indexed columns. Physical design in this lesson assumes that cost model.
• SQL Fundamentals for Data Engineers for keys, joins, and constraints as SQL.
• The event and enrollment examples in lessons 7 and 8. This lesson is where those tables get a defensible shape.
• No prior data-warehouse course. Star schemas appear here only as the destination you are not building yet.

Detailed Core Concepts

Three models, three conversations

The conceptual model answers "what exists in the business, and how does it relate?" Learners exist. Courses exist. An order is a commercial commitment. A seat is the right to take a course, and it may be assigned to a learner after the order is paid. A lesson completion is something that happened, not a column that is the learner. You can draw this on a whiteboard with boxes and verbs. If the whiteboard contains varchar(64) or btree, it has slipped into a later model and the business stakeholder has been asked to approve a storage decision.

The logical model lists attributes and rules. Order has an identifier, a purchasing account, a status, a currency, and a placed-at time. Order line has an order, a course, a quantity of seats, and a unit price in minor units captured at purchase. Seat has an order line, an optional assigned learner, and a status. The rules are cardinality and dependency: a line belongs to one order; the unit price on the line does not change when the course catalog price changes; a seat belongs to one line. Still no PostgreSQL.

The physical model implements the logical model on an engine. PostgreSQL types, timestamptz, bigint cents, uuid or identity columns, foreign keys, check constraints, fillfactor on the tables lesson 8 expects to update, and indexes that match real predicates. A physical model can add partitioning by month on events because of volume. That partition is not a new entity. It is storage. Partitioning is the engine lesson. Confusing it with a business entity is how "partition" starts appearing in executive reviews as if it were a product concept.

Review comments sort themselves once the layers are named. "Can one order pay for two courses?" is conceptual or logical. "Should the primary key be uuid or bigint?" is physical. "Should course_title be copied onto the order line?" is logical, and the answer depends on whether the title is "the title we sold" or "whatever the catalog says now." Those are different attributes. The first belongs on the line. The second belongs on the course and is joined.

Entities, relationships, and the order domain

TechLearningPro's commercial domain, which is the retail-order problem for a course business, uses these conceptual entities:

Relationships:

• An account places zero or many orders. An order has one paying account.
• An order has one or many lines. A line has one order and one course.
• A line creates one or many seats equal to the quantity. A seat comes from one line.
• A seat may be assigned to one learner. A learner may hold many seats.
• A payment applies to one order. An order may have several payments if you allow retries and partial refunds. The successful charge is a row with a status, not a boolean overwritten in place. Lesson 7 already ranked attempts. The model has to keep the attempts.
• A completion refers to one learner and one lesson of one course. It is append-only.

Many-to-many relationships do not stay as a line on a diagram in the logical model. Learner and course are many-to-many. The relationship has attributes (when, through which seat, in what status), so seat or enrollment is an associative entity with its own identifier. A hidden join table with only two foreign keys is appropriate when the relationship has no facts. Enrollment has facts. Give it a name the business uses.

Optionality matters as much as cardinality. A seat without a learner is legal: the company bought twelve seats and assigned nine. A payment without an order is not legal in this model. A completion without a learner is not legal. Optional foreign keys are explicit nullability. Mandatory foreign keys are NOT NULL. Teams that leave every foreign key nullable "until the app is finished" have postponed the model, and the data will use the postponement.

Keys

A natural key is an attribute the business already uses to identify something: an email, a course slug, an external payment-provider charge id. Natural keys change, get reused, or are not as unique as a meeting claimed. Email changes when a learner changes jobs. Slugs change for marketing. A provider charge id is an excellent natural key for a payment, because the provider minted it and it does not change, and it is how you reconcile.

A surrogate key is an identifier the system mints, with no business meaning: a bigint identity or a uuid. Surrogates stay stable when email changes. They are what foreign keys should reference. The natural key, when it exists and is stable enough to be unique, gets a UNIQUE constraint beside the surrogate. Choosing a surrogate is not a reason to allow two courses with the same slug if the business promised slugs are unique.

Composite keys are honest when the identity is a combination: a completion might be uniquely (learner_id, lesson_id) if the product allows one completion, or it might allow many attempts and then the key is a surrogate event id plus a unique business idempotency key. Do not invent a single-column primary key and also forget the uniqueness rule the product stated. The surrogate makes joins uniform. The unique constraint keeps the business rule.

Lesson 10 will use surrogate keys again inside a warehouse, for a different reason: dimension rows are versioned, and the source system's key is a business key on the dimension, not the primary key of a slowly changing row. Do not "fix" the operational model now by versioning rows in courses on every title edit. That is an analytical pattern. In the operational store, the course row is current state, and a title change is an update. If finance must know the title printed on the receipt, that title is a column on the order line, captured at purchase.

Normalization, stated as dependencies

A functional dependency A → B means that for one value of A you expect only one B. Normalization removes dependencies that are not "the key determines the attribute."

First normal form. Each attribute holds one value of its domain, and repeating groups are not stored as duplicated columns (lesson_1, lesson_2, …) or as a single text cell that is the only place lines exist. A PostgreSQL array of tags can be a physical convenience for a true set of labels. It is a modeling defect when it hides order lines you need to constrain with foreign keys and quantities. Prefer a child table for anything you will join, count per child, or reference.

Second normal form. The table is in first normal form, and every non-key attribute depends on the whole primary key, not on part of it. This bites composite keys. If order-line identity is (order_id, course_id) and course_title_at_purchase is allowed, that title depends on the line. account_email on the line depends only on the order, not on the course, so it is a partial dependency. It belongs on the account or on the order as a captured contact, not on every line. In practice TechLearningPro uses a surrogate order_line_id, and second normal form issues hide inside "I copied the account onto the line because the join was annoying." The dependency test still works: if the attribute does not change when the course on the line changes, it does not belong to the line.

Third normal form. The table is in second normal form, and non-key attributes do not depend on other non-key attributes. If orders contains country_code and country_name, and the name is whatever the reference data currently says about the code, the name depends on the code, not on the order. Put countries in a reference table. If country_name means "the label we rendered on the invoice," it depends on the order, it is allowed to differ from today's reference name, and it is not a transitive dependency. The column name has to say which fact you stored. country_name does not say it. invoice_country_label does.

Boyce-Codd normal form. Every determinant is a candidate key. A table of (learner_id, course_id, instructor_id) where each course has exactly one instructor has a dependency course_id → instructor_id, and course_id is not a candidate key of that table. The instructor belongs on the course. BCNF matters when a table has overlapping candidate keys. You can teach third normal form well and still trip on BCNF in that instructor example. For this domain, putting instructor on the course, not on the enrollment, is the fix, unless a course truly has a different instructor per learner, in which case the dependency does not exist and a cohort-instructor entity does.

Anomalies you should be able to point at in a bad table:

Those anomalies are the operational argument for third normal form. They are not an argument against a warehouse table that repeats the title on purpose, because that table is rebuilt from the normalized source and is not the place you update the title.

Where denormalization is a choice, not a leak

A read model can repeat data. The learner's "my courses" page can read a table of (learner_id, course_id, title, progress_percent, last_active_at) maintained by a job or a trigger. The source of truth for the title remains courses. The source of truth for progress is the completion events, and the percentage is derived. If the read model is wrong, you rebuild it. You do not hand-edit it. You do not let the application update the title in the read model and forget the catalog.

Derived attributes on the operational row are the dangerous middle. enrollments.progress_percent updated by the application on every lesson view will drift from the events, and lesson 7's funnel will not match the profile page. Store the events. Compute the percentage. If the computation is too slow to do on the profile read path, store the derived value in a table whose owner is the job, with computed_at, and let the job be the only writer. Lesson 8's HOT and vacuum discussion is why that derived table should be rebuilt in batches or updated carefully, not designed as a second source of truth with no owner.

Snapshots of attributes at the time of a business event are not denormalization leaks. unit_price_cents on the order line is the price sold. It must not be a foreign key lookup to the current price at query time. People who "normalize" the price off the line and join to courses.price_cents rewrite history every time marketing changes a price. The current price is a catalog attribute. The sold price is a line attribute. Both exist. They are allowed to differ.

Constraints are part of the model

A model the database does not enforce is a suggestion. Foreign keys from lines to orders and from seats to lines prevent orphan seats when an order is deleted badly. ON DELETE behavior is a product decision: restricting deletes of courses that have been sold is usually right; cascading deletes of payments is almost never right. Check constraints encode invariants that fit in one row: quantity > 0, unit_price_cents >= 0, status IN (...). Cross-row invariants, such as "seats created must equal quantity," are transactions in the application or a constraint trigger you are prepared to maintain. Leaving them entirely to application code means every importer and every ad-hoc SQL session can break them. Module 2 will have importers. Put the invariant as close to the data as you can explain it.

Names are constraints on human readers. id as the only primary-key name in every table makes joins ambiguous in a 15-table query. order_id on the order table and order_id as the foreign key on the line is boring and clear. Schemas separate ownership: catalog, billing, learning. A table that two teams "share" without an owner will gain columns from both until it is the spreadsheet again.

What this lesson will not collapse

Identity and access, content files, and video transcoding are outside the order model. A lesson entity in the catalog (the outline) is not a completion entity (the fact that a learner finished it). Mixing them produces the column-per-lesson spreadsheet. Multi-currency is in scope only as currency on the order and minor units on the line. Converting currencies in the operational table on read is how yesterday's revenue moves. Store the charged currency. Convert in an analytical model with an explicit rate and rate date, which lesson 10 can host.

Architecture and Design Diagrams

The three layers are a decision flow. Business language enters at the top. PostgreSQL appears at the bottom. Pipelines in Module 2 read the physical tables and must preserve the logical grain.

mermaidCopy
flowchart TD
  BIZ[Business rules: who pays, who studies, what was sold]
  CON[Conceptual model: entities and verbs]
  LOG[Logical model: attributes, keys, cardinality, dependencies]
  PHY[Physical model: types, constraints, indexes, partitions]
  OLTP[(PostgreSQL operational schemas)]
  READ[Derived read models rebuilt from source]
  WH[Lesson 10 warehouse grain]
  ETL[Module 2 extract and load]

  BIZ --> CON --> LOG --> PHY --> OLTP
  OLTP --> READ
  OLTP --> ETL --> WH
  READ -.->|never the only copy of a fact| OLTP

The order domain's relationships:

mermaidCopy
erDiagram
  ACCOUNT ||--o{ ORDER : places
  ORDER ||--|{ ORDER_LINE : contains
  COURSE ||--o{ ORDER_LINE : "sold as"
  ORDER_LINE ||--|{ SEAT : creates
  LEARNER ||--o{ SEAT : "assigned to"
  ORDER ||--o{ PAYMENT : settles
  SEAT ||--o| ENROLLMENT : activates
  LEARNER ||--o{ COMPLETION : achieves
  COURSE ||--o{ COMPLETION : "outline of"

A price change in the catalog must not flow into lines already sold. The diagram of that rule is a write boundary:

mermaidCopy
flowchart LR
  CAT[catalog.courses.list_price_cents]
  LINE[billing.order_lines.unit_price_cents]
  CAT -->|copied only at checkout insert| LINE
  CAT -.->|later UPDATE does not touch| LINE

Technical Explanation

Logical attributes worth arguing about

Account and learner. A learner who buys a single seat with their own card is both. A company account that buys twelve seats is not a learner. If you store only learner_id on orders, the company purchase has nowhere to sit except as a fake learner. The logical split is account (payer) and learner (student). A self-serve purchase creates both and links them. The physical tables can still be created in one migration. The conceptual mistake is harder to migrate later, because every foreign key assumed the payer studies.

Order and payment. An order is the intent and the commercial document. A payment attempt is a row. Status on the attempt changes or, better, new attempts are inserted and the order stores a status derived from attempts. Overwriting one orders.payment_status loses the decline that support needs. Lesson 7's ROW_NUMBER for "latest attempt" assumes the attempts exist as rows. Give them a table: attempt_id, order_id, provider_charge_id unique, amount_cents, status, attempted_at.

Quantity and seats. A line with quantity = 12 that does not create seat rows forces every downstream question to invent learners. Creating twelve seat rows with null learner_id matches the business object "unassigned seat." The check that seat count equals quantity is the cross-row invariant. A transaction that inserts the line and the seats together holds it. A later job that finds mismatches is the backstop Module 2 should not have to invent quietly.

Completions. The catalog lesson (catalog.lessons: lesson_id, course_id, slug, position) is an outline. learning.completions (completion_id, learner_id, lesson_id, completed_at, event_id unique) is a fact. Progress percentage is COUNT(completions) / COUNT(outline) for that course, computed, not stored on the learner as the only record. If the outline gains a lesson, the percentage drops. That is honest. A stored percentage from last week is a different, stale fact and should be labeled as_of if you keep it.

Normal-form walkthrough on a bad sales table

Start from the spreadsheet shape, written as a table so the dependencies are visible:

textCopy
sales_sheet(
  email,
  course_title,
  instructor_name,
  price_paid,
  seat_count,
  lesson_1_done,
  lesson_2_done,
  payment_status
)

email → nothing stable, and email is being used as a learner key. course_title → instructor_name if one instructor owns the course, so instructor is not a fact about this sale alone. lesson_1_done is a repeating group. price_paid might be the line price or the whole order; the column does not say, and seat_count suggests someone has already multiplied them once. payment_status overwrites history.

A third-normal-form operational set for the same business:

textCopy
account(account_id, account_type, billing_email)
learner(learner_id, account_id, email)  -- email unique where it is the login
course(course_id, slug, title, instructor_id, list_price_cents, currency)
instructor(instructor_id, display_name)
order(order_id, account_id, status, currency, placed_at)
order_line(order_line_id, order_id, course_id, quantity, unit_price_cents, title_at_purchase)
seat(seat_id, order_line_id, learner_id null, status)
payment_attempt(attempt_id, order_id, provider_charge_id, amount_cents, status, attempted_at)
lesson(lesson_id, course_id, slug, position)
completion(completion_id, learner_id, lesson_id, completed_at)

title_at_purchase is deliberate snapshot data. list_price_cents is current catalog state. instructor_id lives on the course, so a rename of the instructor updates one row. Historical "who taught this when we sold it" is a snapshot column if finance needs it; otherwise the current instructor is enough and lesson 10 can version it for analysis.

Dependencies you can defend in a review:

• order_line_id → unit_price_cents, quantity, course_id, order_id
• course_id → list_price_cents, instructor_id, title for current catalog state
• provider_charge_id → one attempt, enforced by a unique constraint
• learner_id, lesson_id → one completion if the product says completion is idempotent, enforced by a unique constraint; the surrogate completion_id still exists for references

Anomalies removed

Update: changing instructor.display_name is one row. Invoice titles already stored on lines do not move, which is what you want for documents already sent. Insert: a course can exist with no orders. Delete: deleting a declined payment attempt does not delete the course or the learner. If your delete of an order is blocked by a foreign key from lines, that block is the model working. Decide whether orders are cancellable by status instead of deleted. Analytics prefers status. Hard deletes break lesson 7's history and Module 2's incremental extracts.

Physical shape, only as far as the logical model requires

sqlCopy
CREATE SCHEMA IF NOT EXISTS catalog;
CREATE SCHEMA IF NOT EXISTS billing;
CREATE SCHEMA IF NOT EXISTS learning;

CREATE TABLE catalog.courses (
  course_id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug              text NOT NULL UNIQUE,
  title             text NOT NULL,
  instructor_id     bigint NOT NULL REFERENCES catalog.instructors (instructor_id),
  list_price_cents  bigint NOT NULL CHECK (list_price_cents >= 0),
  currency          char(3) NOT NULL,
  published_at      timestamptz
);

CREATE TABLE billing.orders (
  order_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id    bigint NOT NULL REFERENCES billing.accounts (account_id),
  status        text NOT NULL CHECK (status IN ('draft', 'submitted', 'paid', 'cancelled', 'refunded')),
  currency      char(3) NOT NULL,
  placed_at     timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE billing.order_lines (
  order_line_id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id            bigint NOT NULL REFERENCES billing.orders (order_id),
  course_id           bigint NOT NULL REFERENCES catalog.courses (course_id),
  quantity            integer NOT NULL CHECK (quantity > 0),
  unit_price_cents    bigint NOT NULL CHECK (unit_price_cents >= 0),
  title_at_purchase   text NOT NULL,
  UNIQUE (order_id, course_id)
);

CREATE TABLE billing.payment_attempts (
  attempt_id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id             bigint NOT NULL REFERENCES billing.orders (order_id),
  provider_charge_id   text UNIQUE,
  amount_cents         bigint NOT NULL CHECK (amount_cents >= 0),
  status               text NOT NULL CHECK (status IN ('pending', 'succeeded', 'declined', 'unconfirmed')),
  attempted_at         timestamptz NOT NULL DEFAULT now()
);

UNIQUE (order_id, course_id) encodes "one line per course per order." If the product later allows two lines for the same course, that constraint is the thing you must change on purpose. Identity bigint keys keep indexes smaller than random uuids on insert-heavy tables. Uuids still win when ids must be minted by clients offline or merged across databases without a central sequence. TechLearningPro's operational store can use bigint identity for catalog and billing. Event ids from mobile clients stay uuid, because the client mints event_id before the server sees the row. That split is physical and it follows who creates the identifier. It is not a philosophical preference for one type.

Indexes follow lesson 8: foreign keys you join, the unique provider charge id, and the predicates you have plans for. Do not index title_at_purchase because it is text someone might search once. A GIN index on titles is a product search feature with its own workload, not part of the order model.

enrollments or seats that update status get a fillfactor below 100 only after you see HOT failing, as lesson 8 described. The logical model does not contain fillfactor. The physical migration can.

Read model, owned

sqlCopy
CREATE TABLE learning.course_progress_snapshot (
  learner_id        bigint NOT NULL,
  course_id         bigint NOT NULL,
  lessons_done      integer NOT NULL,
  lessons_total     integer NOT NULL,
  progress_ratio    numeric(6, 4) NOT NULL,
  computed_at       timestamptz NOT NULL,
  PRIMARY KEY (learner_id, course_id)
);

A job replaces rows for learners who had new completions since the last run. The application may read this table and must not update it. Completions remain the source. If the snapshot job is late, the profile page is stale and the funnel is still right. That is a freshness bug you can alert on, using computed_at, not a silent disagreement between two truths.

How Module 2 will misunderstand this if you are careless

An extract that does SELECT * FROM billing.orders and treats status as "the payment" will miss declined attempts and will break when status gains a new value. The contract you hand the pipeline is the grain and the keys: orders at order grain, attempts at attempt grain, seats at seat grain, completions append-only at completion grain. Incremental extract of orders by placed_at misses status updates. Incremental extract of a mutable order table needs an updated_at the application maintains, or a WAL-based change feed from lesson 8. Modeling updated_at as a real column, set in the same transaction as the change, is part of making the table extractable. It is not decorative metadata. A pipeline that assumes every table is append-only will drop enrollment status changes. Say, in the model notes, which tables are mutable state and which are facts.

Practical Implementation or Design Walkthrough

Problem

Replace the spreadsheet with a logical and physical model for TechLearningPro orders: self-serve purchases and company seat packs, catalog prices that change, receipts that must not change, and lesson completion that a funnel can count. The model has to load a team order for 12 seats with 9 assigned, one declined payment, and one success, without multiplying revenue by 12 or by 2 attempts.

Steps

1. Write the conceptual entities and the verbs. Confirm with a product statement: "We sell quantities of course seats. Assignment to a learner can happen later. A receipt price is frozen. The catalog price is current."
2. List functional dependencies you intend to store. Mark snapshot attributes separately from current-state attributes.
3. Place each dependency on one entity. Apply the anomaly test: update instructor, insert an unsold course, delete a declined attempt.
4. Choose keys. Surrogate on account, learner, course, order, line, seat, attempt, lesson, completion. Unique on course slug, learner login email, provider charge id, and (order_id, course_id) if one line per course is the rule. Unique on (learner_id, lesson_id) if completion is once.
5. Translate to PostgreSQL with the types and checks above. Foreign keys on. ON DELETE RESTRICT for catalog rows referenced by lines.
6. Write three validation queries that are the acceptance test, not an afterthought:
   • Revenue is SUM(quantity * unit_price_cents) on lines of orders whose latest succeeded payment exists, not SUM of a column copied onto seats.
   • Seat count per line equals quantity.
   • A catalog price change does not change SUM(unit_price_cents * quantity) for orders already placed.
7. Document mutable tables (orders.status, seat assignment) versus append-only completions and payment attempts. Note updated_at on mutable tables for the future extract.
8. Specify the progress snapshot as derived. Name the job as the only writer.

Worked numbers

One company order, one line, course list price 4900 cents, sold at 4900, quantity 12. One declined attempt for 58800 cents, one succeeded attempt for 58800 cents. Nine seats assigned.

Correct revenue for that order is 58800 cents once. Incorrect spreadsheet revenue is 58800 times 12 seats, or 58800 times 2 attempts. The validation query joins orders to lines and to attempts filtered to status = 'succeeded', and it does not join seats before the sum. Joining seats before aggregating is the physical way the model gets misread even when the tables are right. Lesson 7's grain comments exist for this reason. The model and the query grain have to match.

What you leave for lesson 10

You do not build a star schema here. You do record the grains the star will want: a sale at line grain, a payment at attempt grain, a completion at completion grain, a learner dimension that can change email without changing learner_id. The slowly changing treatment of email and course title in the warehouse is the next lesson. Doing it in catalog.courses now would turn every title edit into a new course id or a stack of history rows the product update path is not ready to own.

Enterprise Real-World Scenarios

TechLearningPro team orders and the revenue bug

The company launched team licenses by cloning the self-serve row. Each assigned learner became an "order" with the full pack price. Finance reported a record week. The payment provider's settlement file, which has one charge per checkout, did not match. The repair was the model in this lesson: one order, one line with quantity, seats as children, payments as attempts against the order. Backfill used the provider charge id, which was luckily stored in the free-text cell often enough to match. Where it was not, those rows went to an exception table instead of being forced into the new model with a guessed amount. The exception table is part of the design. A model that cannot represent dirty history without lying should quarantine the dirty history.

The operational volume was small by platform standards: tens of thousands of orders, not billions. The cost of the wrong model was not scan time. It was a decision about hiring and ad spend made on a number the settlement file contradicted. Normalization here is a finance control. The physical indexes can wait until lesson 8's plan says they are needed. The unique constraint on provider_charge_id cannot wait, because that is how the next replay of a webhook avoids a second payment row.

Access follows the schemas. The catalog role does not update billing.payment_attempts. The learning-events role does not update unit_price_cents. A model without owners becomes a shared writable schema, and the spreadsheet returns as a set of columns added under deadline. Module 2's extract role gets SELECT on the source tables and write access only to the staging schema it owns.

TechLearningPro progress stored in two places

The profile page updated enrollments.lessons_completed in the application. The event pipeline appended completions. After a retry, the event landed once and the application incremented twice. The funnel and the profile disagreed by a lesson. Support believed the profile. The data team believed the events. Both systems were "working." The model change made completions the source, added the unique (learner_id, lesson_id) constraint the product's "finish once" rule implied, and rebuilt the snapshot table overnight. The application kept a read path. It lost the write path. The incident review's durable sentence was: a derived number needs one writer, and a fact needs a key that makes the retry a no-op.

A second variant showed up when content added a lesson to a published course. Stored percentages meant "6 of 10" forever for old learners, which product liked, and meant new outlines were invisible, which content did not like. The snapshot definition was written down: lessons_total is the outline at computed_at. Historical "what the outline was on the day they enrolled" is a different metric and needs the outline's versions, which is a lesson-10 change-tracking problem. Until those versions exist, the product must not pretend the stored ratio is a contract with the past.

Retail order patterns that match this shape

Any catalog business with a cart, a frozen price, a payer who is not the recipient, and a later fulfillment assignment has the same logical picture: order, line, price snapshot, fulfillment unit, payment attempts. The names change in shipping (shipment instead of seat). The dependencies do not. Copying a wide "order item" table from a tutorial that repeats customer address, product description, warehouse name, and current price on every item will recreate the three anomalies. Address at ship time is a snapshot. Warehouse name as a lookup is a dependency to remove. The test is always: if this attribute changes, how many rows must change, and should history move with it?

Best Practices

• Write the conceptual picture in business nouns before you open a migration file. If you cannot name the entity without a column type, you are still in the meeting.
• Keep current-state attributes and point-in-time snapshots in different columns, with names that say which one they are.
• Reference surrogate keys. Uniquely constrain the natural keys the business still promises.
• Enforce the model with foreign keys and checks on the operational database. Importers in Module 2 are not a reason to drop constraints "for speed" until a measured bulk load says so, and then the load can disable and re-enable checks in a controlled window rather than never having them.
• Prefer status and effective dates over hard deletes on orders, payments, and enrollments.
• Separate outline (lessons in a course) from activity (completions). Derive percentages, or store them in a snapshot table with one writer and computed_at.
• Record which tables are mutable and maintain updated_at in the same transaction as the change so extracts can see updates.
• Split schemas by owner. Grant the pipeline SELECT on sources and writes on its own staging schema.
• Review every repeated column with the anomaly table: update, insert, delete. If none of the three can happen because the table is rebuilt, the repeat may be a read model. If the application updates it, the repeat is a future incident.
• Leave warehouse versioning, conformed dimensions, and fact grains to lesson 10. Do not version operational course rows "because Kimball said history" without a product path that can update the current row.

Common Mistakes and Anti-Patterns

One table that is the spreadsheet. Repeating lesson columns, packing payment text into a notes field, and using email as the only key. Consequence: overwrite, double revenue, no unsold courses. Fix: entities by dependency, then tables.

Normalizing away the sold price. Joining lines to the current catalog price for revenue. Consequence: a price edit rewrites history. Fix: unit_price_cents captured on the line; list_price_cents stays on the course.

Payer and learner forced into one id. Team purchases become fake learners or twelve full-price orders. Consequence: settlement will not match. Fix: account, order, quantity, seats.

Surrogate keys with no unique natural key. Two courses share a slug, two attempts share a provider charge id, two completions share a learner and a lesson. Consequence: the application retries create new truth. Fix: unique constraints on the business keys you still have.

Nullable everything. Optional seats are a real null. Optional order on a payment is not. Consequence: orphans and outer joins that hide lost data. Fix: NOT NULL where the logical model says mandatory, and foreign keys.

Derived progress as a second source of truth. The app increments a counter and the pipeline counts events. Consequence: funnel and profile diverge. Fix: one writer for the snapshot, events as source, a unique completion key.

Hard-delete of operational history. Extracts and funnels lose rows, and foreign keys either cascade into payments or get dropped to make the delete easy. Consequence: you cannot explain last Tuesday. Fix: status transitions.

Physical tuning presented as a model. A debate about uuid versus bigint, or about a BRIN index, substitutes for the missing account entity. Consequence: a fast wrong schema. Fix: close the logical model first. Physical choices still matter, and they belong in the migration review after the dependencies are agreed.

Building the star schema in the operational database by default. Wide fact tables maintained by the checkout transaction add write latency and mix owners. Consequence: the product path maintains analytical grain. Fix: operational third normal form here; dimensional model in the warehouse, fed by Module 2.

Troubleshooting and Design Review

Design review questions:

1. What is the entity, in a sentence a finance partner recognizes?
2. Which attributes are current state, and which are snapshots of a past decision?
3. What are the candidate keys, and which uniqueness constraints enforce them?
4. What update, insert, and delete anomalies remain, and are they accepted because the table is derived?
5. Which tables are mutable, and how will Module 2 see a change that is not a new row?
6. Who may write each table, and which derived numbers have exactly one writer?

A review that only checks naming style will approve a seat table that stores the pack price.

Interview Questions and Answers

Q1. What is the difference between a conceptual, a logical, and a physical data model?

The conceptual model names business entities and relationships: orders, seats, courses, payments. The logical model adds attributes, keys, cardinality, and dependencies, without engine types. The physical model is the implementation: PostgreSQL types, constraints, indexes, partitions, and storage parameters such as fillfactor. "Should a seat exist before a learner is assigned?" is logical. "Should the primary key be a uuid?" is physical. Teams that start in CREATE TABLE tend to freeze a physical accident before the business relationship is agreed.

Q2. Why is third normal form a good default for TechLearningPro's operational store?

Each fact lives in one place, so updating an instructor name, inserting a course nobody has bought, or deleting a declined payment attempt does not destroy or multiply unrelated facts. That is the update, insert, and delete anomaly test. The operational store is updated in transactions and must agree with settlement. A denormalized table maintained by checkout will drift. The warehouse in the next lesson is allowed to repeat attributes because it is rebuilt from this store and is not the system of record.

Q3. When is copying title onto an order line acceptable?

When the column means the title sold at that moment, and a later catalog rename must not change the receipt. Then it is a snapshot attribute of the line, not a transitive dependency on the course. Name it so the meaning is obvious, such as title_at_purchase. Copying the title so a query can avoid a join, while the application also updates it when the catalog changes, creates two current-state copies. One of them will be stale. Current title belongs on the course. Sold title belongs on the line.

Q4. What is wrong with using email as the primary key of a learner?

Email changes, and it can be reused by a different person if the product allows account recovery to release it. Every foreign key that stored the email must be updated, and history becomes ambiguous. A surrogate learner_id stays stable. A unique constraint on the current login email still enforces "one account per email" for the rows where that rule holds. The surrogate does not replace the unique constraint. It replaces the email as the thing other tables point at.

Q5. What is a partial dependency, and what is a transitive dependency, in this domain?

A partial dependency appears with a composite key: an attribute on (order_id, course_id) that depends only on order_id, such as the paying account's email copied onto every line. It depends on part of the key. A transitive dependency is a non-key attribute determined by another non-key attribute: country_name on the order determined only by country_code when the name is reference data. The fix is a reference table, unless the name is the invoice label captured at purchase, in which case it is a snapshot and the column should say so. Second normal form removes partial dependencies. Third normal form removes transitive ones.

Q6. A table (learner_id, course_id, instructor_id) has one instructor per course. Which normal form fails, and where does the instructor go?

course_id determines instructor_id, but course_id is not a candidate key of the enrollment-like table. That violates Boyce-Codd normal form. The instructor belongs on the course. It belongs on the enrollment only if the business truly assigns a different instructor per learner for the same course, in which case the dependency course_id → instructor_id is false and the model should show a cohort or section entity instead of pretending the course has one instructor.

Q7. How do you model a company buying 12 seats when only 9 learners are known?

One account for the company, one order, one order line for the course with quantity = 12 and a unit price captured at sale, and twelve seat rows. Nine seats reference learners. Three seats have a null assignee. Revenue is the line extended price associated with one succeeded payment, not the price times the number of seat rows and not the sum of payment attempts. A check or a scheduled assertion confirms seat count equals quantity. Forcing twelve learner rows on the day of purchase invents people. Storing one row per learner at the full pack price multiplies revenue.

Q8. Why are foreign keys and check constraints part of modeling rather than an application detail?

The model says an attempt cannot exist without an order and a quantity cannot be zero. Application code is one writer. Module 2 importers, support SQL, and future services are others. Constraints make the rule true for all of them. ON DELETE RESTRICT on a sold course prevents a catalog cleanup from removing history. Constraints do not replace a transaction that must insert a line and its seats together; they stop the orphans that transaction forgot. Dropping foreign keys for a bulk load is a timed maintenance action with the checks turned back on and validated, not a property of the model.

Q9. Completions and progress_percent both describe learning. Why are they not the same entity?

A completion is a fact with a time and an idempotency key. A percentage is a derived comparison of completions to the current or historical outline. Storing only the percentage loses which lessons were finished and cannot be rebuilt. Storing the percentage beside an application counter, while events are also stored, creates two writers. Keep completions as rows. Publish a snapshot table if the read path needs a number, with computed_at and a job as the only writer. When the outline gains a lesson, define whether the denominator changes. That definition is the metric, and it should be written next to the snapshot.

Q10. What should a model document about a table so an incremental pipeline does not lose updates?

Whether the table is append-only or mutable. For mutable tables, the column that moves on every committed change, usually updated_at, set in the same transaction as the business update, not by a trigger that a bulk load bypasses unless the trigger is actually reliable and tested. The pipeline key is the primary key. The watermark column has to move on status changes, not only on insert. Otherwise an extract filtered on placed_at will never see an order move from submitted to paid. Change data capture from the WAL is the alternative when you cannot trust application timestamps, and it still needs the primary key the model defined.

Q11. When would you denormalize on purpose in this architecture?

In a read model or a warehouse table that is rebuilt from the normalized source and is not updated by the product transaction. Examples: a progress snapshot, a search document, a lesson-10 fact that repeats course title for query convenience. The test is the writer. If checkout updates the copy, you have two operational truths. If a job replaces the copy from source, you have a cache with a freshness clock. Denormalizing to avoid a join in the operational path is rarely worth the anomaly, because the join on a primary key is cheap next to the cost of a wrong receipt.

Q12. How does this operational model hand off to a dimensional warehouse without changing the meaning of an order?

The warehouse may remodel shape. It may not remodel grain. An order remains the commercial header. A line remains the sold quantity and the frozen price. A seat remains an entitlement. A payment attempt remains an attempt. The dimensional model picks one of those grains for a fact table and uses the others as dimensions or as separate facts. It uses learner_id and course_id as business keys. It does not invent a new meaning in which each seat row is a full order. Lesson 10 declares the fact grain in writing. Module 2's job is to load that grain from these tables without multiplying rows through a careless join.

Hands-On Exercises

Exercise 1. From spreadsheet to dependencies

Problem. You receive sales_sheet(email, course_title, instructor_name, price_paid, seat_count, lesson_flags text, payment_status). Design the conceptual and logical model for self-serve and team purchases.

Tasks.

1. List entities and relationships, including optionality of seat assignment.
2. Mark each attribute of the sheet as current state, snapshot, repeating group, or ambiguous.
3. Propose keys and unique constraints.
4. Show one update, one insert, and one delete anomaly the sheet has, and the table that removes each.
5. Write the revenue validation query's grain in a sentence: which table, which filter, which multiply.

Acceptance. A team order of quantity 12 with one succeeded payment is one revenue amount. Instructor rename touches one row. A course with no sales can be inserted.

Exercise 2. Physical migration and the derived snapshot

Problem. Implement a minimal PostgreSQL version of courses, orders, lines, attempts, and completions. Include the progress snapshot table.

Tasks.

1. Create tables with primary keys, foreign keys, and the checks from this lesson.
2. Insert the worked example: quantity 12, two attempts, nine assigned seats. You may simplify seats if time is short, but then say you skipped the seat-count assertion and why that assertion matters.
3. Change list_price_cents and show that line revenue did not change.
4. Insert two completions for the same learner and lesson and show the unique constraint rejecting the second, or justify a different product rule and encode it.
5. Define who writes course_progress_snapshot and what computed_at is for.
6. Note which tables need updated_at for a future incremental extract.

Rubric.

Lesson Summary

A model is a set of decisions about entities, relationships, and which attributes depend on which keys. Conceptual language, logical dependencies, and physical PostgreSQL are three reviews, not one migration with business words mixed into index names. Third normal form keeps operational facts in one place so instructor edits, unsold courses, and declined payments do not corrupt each other. Snapshots such as unit_price_cents and title_at_purchase are facts about the sale and stay on the line even though a join to the catalog would look more "normalized." Derived progress is allowed as a rebuildable snapshot with one writer. It is not allowed as a second counter the application maintains beside events.

The order domain you can defend has accounts that pay, lines that freeze price and quantity, seats that can wait for a learner, payment attempts that reconcile to a provider id, and completions that are distinct from the course outline. Module 2 will extract those grains only if mutable tables expose changes and constraints stop duplicate charges. The next lesson builds the analytical model on top: facts at a declared grain, dimensions, star and snowflake schemas, and slowly changing history for the attributes this lesson deliberately kept as current state. Dimensional Modeling and Data Warehousing does not replace the operational schema. It consumes it.

Further Reading

• PostgreSQL, Constraints and Data Definition.
• TechLearningPro, Creating Tables, Constraints, and PostgreSQL Data Types.
• Codd, E. F., "A Relational Model of Data for Large Shared Data Banks," Communications of the ACM (1970), for the relational model these normal forms sit on. Read it as history and definition, not as a warehouse design.
• Kent, William, "A Simple Guide to Five Normal Forms in Relational Database Theory," Communications of the ACM (1983), for a short, precise account of normal forms. Use it to check the dependency language in this lesson.
• Kimball and Ross, The Data Warehouse Toolkit, for the analytical modeling this lesson intentionally defers. The next lesson applies it to TechLearningPro without copying the operational schema into a star by accident.