Logical models and schemas — extended reference

This reference preserves the earlier detailed chapter and its citations. Start with the guided explanation, then use these sections for additional mechanisms and qualifications. References to the original lesson sequence are historical.

Part 2: Representing and locating data

Prerequisites: Lesson 06 and the preceding foundations.

Learning outcomes

• Represent ownership using keys, constraints, relationships, and relational operations.
• Explain normalization and denormalization through update obligations and access costs.
• Choose a relational, document, or key-value representation against explicit atomicity and query requirements.

One fact needs an authoritative representation

A game stores a player’s inventory as an array inside a player document. A trading service also stores an owner on each item. A crash occurs after one representation changes and before the other changes. The sword now appears in both Alice’s and Bob’s inventories even though its item record names only one owner. The problem is not primarily the choice between documents and tables. It is the duplication of an authoritative fact without an atomic maintenance rule.

Logical modeling identifies the facts the system represents and the relationships that make them meaningful. Physical design decides how those facts are stored and accessed. Keeping the two levels distinct lets an engineer change storage layout without accidentally changing ownership, uniqueness, or query meaning.

Relations, tuples, and identities

A relation is a set-like collection of tuples described by attributes. Practical SQL tables have additional semantics, including possible duplicate rows unless constraints prevent them. A key identifies a row or fact uniquely. A primary key is a chosen identifier; a unique constraint can establish other candidate identities.

The identity of a player need not be their display name. Names can change or collide. A stable player identifier can participate in ownership relationships while the name remains an ordinary attribute. Similarly, an item kind such as “iron sword” identifies a category, while an item identifier distinguishes one unique sword from another.

CREATE TABLE player (
    player_id BIGINT PRIMARY KEY,
    display_name VARCHAR(100) NOT NULL
);

CREATE TABLE item (
    item_id BIGINT PRIMARY KEY,
    owner_id BIGINT NOT NULL REFERENCES player(player_id),
    kind VARCHAR(80) NOT NULL
);

The SQL is a small ownership model, not a complete game schema. It establishes one owner field per item and requires that owner to reference an existing player. It does not establish whether the caller is authorized to transfer the item, whether the recipient has capacity, or whether a trade has been paid for. Those are transition rules that need appropriate transactional enforcement.

Functional dependencies and normalization

A functional dependency says that one set of attributes determines another. If player_id determines display_name, storing the current display name on every inventory row creates duplicated maintenance. Updating a player’s name now requires finding every copy. Failing to update one copy creates inconsistent answers to the same logical question.

Normalization organizes facts so dependencies are represented without unnecessary duplication. A player table holds player attributes; an item table refers to the player identifier. A join reconstructs the combined result. This can make updates easier to keep correct while increasing work for certain reads.

Normalization is not a requirement to split every possible attribute into its own table. The useful question is which fact changes independently and which combinations must be maintained together. Excessively fragmented schemas can make common transactions and queries complicated. A carefully chosen row can group attributes that share identity, lifetime, and access patterns.

Denormalization intentionally stores a redundant representation, often to reduce read work. A materialized count, embedded recent-item list, or copied display label can be appropriate when its freshness and maintenance rules are explicit. Historical snapshots also differ from accidental duplication: an order’s recorded shipping address may intentionally preserve the address at purchase time rather than track the customer’s current address.

Constraints are executable parts of the model

A NOT NULL constraint rejects missing required information. A CHECK constrains values or expressions under the database’s semantics. A UNIQUE constraint protects identity. A foreign key protects a reference relationship. These mechanisms reduce the set of states the engine will accept.

Null semantics require care. In SQL’s three-valued logic, an expression can be true, false, or unknown. A check such as price > 0 is not a substitute for NOT NULL if nulls are forbidden. A missing value may yield unknown rather than false. Exact uniqueness behavior involving nulls is product- and configuration-dependent, so applications should state the intended identity contract explicitly.

Cross-row invariants are more demanding. “At least one doctor remains on call” or “the sum of allocations never exceeds a regional quota” cannot generally be protected by independently checking each row’s nonnegative value. Concurrency control must protect the relationship being read and changed. Lessons 20 and 21 show why otherwise valid per-row updates can violate a shared rule.

Constraints also have physical costs. Unique enforcement often needs indexed lookups and coordination against competing transactions. Foreign-key checks can add reads and locking. Removing a constraint may improve a narrow benchmark while transferring correctness responsibility to application code and operations. That is a changed architecture, not a free speedup.

Relational algebra explains query transformations

Selection filters rows. Projection chooses attributes. A join combines related rows. Aggregation summarizes groups. These operations provide a vocabulary for understanding both query meaning and optimization.

SELECT p.player_id, p.display_name, i.item_id
FROM player AS p
JOIN item AS i ON i.owner_id = p.player_id
WHERE p.player_id = 17;

The query combines one player’s attributes with owned items. A logical description might select player 17, find matching item ownership, and project the requested attributes. The engine can choose different physical implementations while preserving the result. An index on owner_id changes the access path, not the logical ownership fact.

SQL commonly uses bag semantics, allowing repeated rows in intermediate and final results. Joining a player to forty items produces forty combined rows, not one. Joining two one-to-many relationships can multiply rows again. An aggregate written without accounting for that multiplicity can overcount revenue or inventory even when every source table is correct.

For an illustrative 100,000 players with forty items each, there are four million ownership rows. If a second join adds an average of five tags per item, the intermediate result can contain twenty million rows. The final result may still show one player, but execution and aggregate semantics must account for the larger intermediate relation.

Documents and key-value representations

A document groups related data under one object identity. It can simplify fetching a bounded aggregate and updating fields that share a transactional boundary. Large unbounded arrays, independent subobject updates, and relationships spanning many documents can complicate that model. The relevant limitations depend on the engine’s transaction and indexing capabilities, not on the word “document” alone.

A key-value model exposes lookup and mutation by key. Richer relationships and indexes can be built as additional key ranges or layers. This creates flexibility but also requires the layer to maintain secondary representations atomically where needed. A key-value interface does not imply weak consistency; a transactional key-value engine can provide strong multi-key guarantees.

An inventory can be represented as a player document, separate item rows, or keys such as (player_id, item_id). Each representation favors particular access patterns. An entire-player document may make a full inventory read straightforward but increase write contention on that aggregate. Separate rows allow more granular access while requiring joins or multiple lookups for some views. A composite key can cluster a player’s items while making reverse ownership queries require another access path.

Identity design affects physical behavior

Sequential identifiers can improve locality for append-oriented structures but concentrate new writes near one ordered edge. Random identifiers distribute insert locations while potentially increasing page churn and reducing locality. Time-ordered identifiers can provide ordering properties but still create a hot tail. Distributed uniqueness and ordering are separate requirements.

The logical model should avoid depending accidentally on a physical identifier property. An auto-increment value may have gaps after aborted work and should not be interpreted as an exact count of committed business events. A timestamp may not establish a global causal order. A random unique key does not imply that writes spread evenly if all operations update another shared row.

Identifiers also participate in security. Guess-resistant values can reduce casual enumeration, but they are not authorization. An authenticated caller supplying another player’s identifier still needs a server-side permission check. The distinction becomes especially important when database APIs expose identifiers directly to clients.

A worked ownership transition

Initially item 73 belongs to Alice. A trade intends to move it to Bob only if Alice still owns it and the operation has not already been completed. A guarded mutation can express the ownership precondition:

UPDATE item
SET owner_id = 22
WHERE item_id = 73 AND owner_id = 17;

If exactly one row changes, the ownership transition matched its expected starting state. If zero rows change, the transaction must interpret whether the item was absent, already moved, or otherwise ineligible. A complete trade also updates payment, operation identity, and trade history in an appropriate transaction. The guarded statement alone is not the whole protocol.

Now compare duplicated inventory arrays. Each array must be changed consistently with the authoritative item row. If the arrays are merely a cache, the application should say so and define refresh behavior. If they are authoritative, the transaction needs to encompass all representations or use a different ownership protocol. Calling the duplicate a cache without handling stale reads does not resolve the user-visible problem.

Model a bounded aggregate without hiding relationships

A player inventory looks like one object in a user interface, but that does not determine its storage boundary. Suppose a player owns forty ordinary items and two unique artifacts. The ordinary items may be represented as quantities per kind, while artifacts need individual identities and histories. A single “inventory” abstraction contains both fungible counts and unique ownership facts. Treating them identically can either waste storage or lose required identity.

A normalized model can distinguish item_kind, unique_item, and stack_quantity. A document model can make the same distinction inside one player document. The important issue is which operations must change the representations atomically. Trading a unique artifact requires moving one identity; spending ten units of wood requires a guarded quantity decrement. Moving an artifact between players crosses two player aggregates if the authoritative data is embedded in both.

The term aggregate is useful when it names a boundary over which invariants are enforced together. It is dangerous when it merely means “everything displayed on one screen.” One screen can combine many authoritative objects. One transaction can affect objects shown on different screens. Data modeling should follow identity, ownership, and state transitions before choosing a convenient response shape.

Functional dependencies expose specific update anomalies

Consider inventory(player_id, player_name, item_id, item_kind, kind_label). If player_id determines player_name, and item_kind determines kind_label, names and labels repeat across inventory entries. Updating one label now requires touching every relevant row. A partially completed update creates conflicting labels for one kind. This is an update anomaly.

Insertion creates another problem: a new item kind cannot be recorded without inventing an inventory row, because the combined table has no independent place for a kind with no current owners. Deletion creates a third: removing the last item of a kind could remove the only stored label. These anomalies follow from mixing facts with different lifetimes, not simply from the presence of repeated text.

Separating player, item_kind, and item preserves each fact independently. A join reconstructs labels for display. The logical change introduces access work, but it also reduces duplicated writes. A cache or materialized view can accelerate the read later, with an explicit derivation rule. This is a more controlled optimization than making every duplicated field independently authoritative.

Historical facts require a different decision. An order line can preserve the purchased description and price even if the current catalog changes. The dependency is then on the order-line identity and purchase event, rather than on the current product identifier alone. Normalization should respect the meaning of a fact over time. Blindly replacing every copied value with a join can corrupt historical interpretation.

Bags, sets, and a revenue overcount

Suppose an order has two lines worth 30 and 70 currency units. The order also has three shipping events. Joining lines to shipping events by order identifier creates six rows: each line appears once for each event. Summing line values over that join produces 3 × (30 + 70) = 300, although actual line revenue is 100. Every source row can be correct while the query’s multiplicity is wrong.

A safe formulation aggregates the lines to one row per order before joining the separate event summary, or uses an existence test when only the presence of an event matters. Adding DISTINCT to the final sum is not a general repair: two legitimate lines can have the same amount, and collapsing equal amounts would undercount them. Correctness depends on identifying the grain of each relation and the multiplicity of the join.

The grain is the fact represented by one row: one order, one order line, one event, or one ownership interval. Writing that sentence often reveals an incorrect key or missing relationship. An optimizer can rearrange equivalent operations, but it cannot infer that a six-row join was intended to represent a one-row business fact. Logical modeling remains the author’s responsibility.

Composite identities and tenant boundaries

A multitenant service can choose globally unique object identifiers or tenant-scoped identifiers. With tenant-scoped identifiers, (tenant_id, object_id) is the identity. Foreign keys and uniqueness rules should preserve that scope. A reference carrying only object_id can accidentally connect objects from different tenants if the identifier is reused.

For example, an order line identified by (tenant_id, order_id, line_number) can reference its parent using (tenant_id, order_id). This makes the tenant relationship explicit in the model. It still does not authorize a caller to choose arbitrary tenant values. Authorization must bind the query’s tenant scope to trusted identity, and caches and subscriptions must respect the same boundary.

Composite identity also influences physical access. An index beginning with tenant identifier can colocate tenant data and support tenant-local scans. A globally ordered event feed may need another access path. A very large tenant can become a hot range even when smaller tenants distribute well. Choosing a logical key and choosing its physical ordering are related decisions that should be documented separately.

Worked schema and transaction contract

The following SQL expresses part of a constructed relational model. It deliberately uses only two authoritative entity tables so the constraint boundaries remain visible:

CREATE TABLE player (
    player_id BIGINT PRIMARY KEY,
    display_name VARCHAR(120) NOT NULL
);

CREATE TABLE unique_item (
    item_id BIGINT PRIMARY KEY,
    owner_id BIGINT NOT NULL REFERENCES player(player_id),
    item_kind BIGINT NOT NULL,
    revision BIGINT NOT NULL CHECK (revision >= 0)
);

The primary key ensures one authoritative row per unique item. The foreign key ensures an owner exists. The revision can support a conditional transition that rejects outdated expectations. None of these constraints alone proves that a transfer is authorized or paid. The transaction must validate those rules under a concurrency strategy that preserves the invariant.

A transfer can require both expected owner and expected revision. After success, it increments revision in the same update. A zero-row result means the expected starting state did not match. This technique detects stale clients and some races; it does not replace a transaction for related balance changes. If two statements must preserve a shared conservation rule, guarding just one statement is insufficient.

Suppose 100,000 players each own forty unique items, giving four million rows. If the authoritative row and expected metadata average 64 bytes, payload is approximately 256 MB in decimal units before pages, indexes, allocation overhead, versions, and replication. An ownership index adds another representation. Capacity estimates should begin with the logical count and then add those physical multipliers explicitly rather than treating 256 MB as total required memory.

Denormalization needs a maintenance contract

Assume a player row caches item_count for a lobby screen. The authoritative inventory is the set of item rows. There are three possible contracts. The count can be updated in the same transaction as every ownership change; it can be maintained asynchronously from a durable change stream; or it can be recomputed periodically. These choices provide different freshness and failure behavior.

Synchronous maintenance makes reads cheap but adds contention on the player summary row. Asynchronous maintenance removes that row from the foreground transaction while requiring replay identity, ordering, and lag handling. Periodic recomputation can repair drift but allows longer stale periods and spends scan resources. Calling all three choices “denormalization” hides the decision that actually matters.

The cached count should not authorize a transfer if it can lag. An operation acting on exact ownership needs the authoritative fact or an equivalent protected summary. The UI may tolerate a briefly stale count while inventory allocation cannot. Distinguishing display convenience from decision authority lets the architecture optimize reads without weakening the underlying ownership rule.

Visualize authority and duplicated display facts

player
  player_id [primary key]
  display_name
       ^
       | owner_id [foreign key]
unique_item
  item_id [primary key]
  owner_id
  item_kind
  revision

normalized display: item.owner_id -> player.display_name
cached display:     item.cached_owner_name = copied display value

The first diagram marks authority: each item row records one owner, and the reference identifies a player. The last two lines compare deriving a label at read time with retaining a copied label. The copy can be useful, but it introduces an update obligation when a player changes their name or ownership changes. If the copy is a historical label, its intended time meaning must be explicit instead.

Assume Alice owns forty items and renames herself. A normalized representation changes one player row. A synchronous cached-name design may update forty item representations in addition to the player. An asynchronous design can change one authoritative row and enqueue propagation, but readers may temporarily see old labels. The tradeoff concerns write amplification and freshness, not whether duplication is inherently wrong.

Now consider changing ownership of one item from Alice to Bob. The authoritative update must validate the expected owner. If display caches are updated asynchronously, the item might briefly show Bob’s identifier and Alice’s cached name. The UI can avoid this inconsistent combination by deriving the name from a coherent identity mapping, versioning the cache, or accepting and clearly bounding stale display behavior. The invariant about ownership remains separate from the cosmetic label.

For a document alternative, storing all items beneath one player can make the screen fetch convenient, but a cross-player transfer must move data across documents while preserving unique identity. For a key-value alternative, placing item keys under owner prefixes makes inventory scans convenient, while moving ownership becomes a delete/insert plus any reverse-lookup maintenance. These are not reasons to reject either model. They reveal the transaction and query obligations that must be supported before the response shape is treated as a storage design.

A final modeling distinction is current state versus event history. One current owner row can be authoritative while a separate transfer ledger records how ownership changed. Reconstructing state from events is another valid architecture, but requires deterministic interpretation, ordering, and replay rules. Keeping a history table alone does not make every current-state query a replay operation.

Case connection and design conclusion

PostgreSQL’s constraint facilities illustrate how logical rules become engine-enforced boundaries. The course uses that implementation as a reference, while the ownership scenario is constructed. A different engine may enforce equivalent rules through another API. The architectural choice is which invariants are centralized, which representations are derived, and which relationships an operation must protect.

A good model makes invalid states difficult to represent and required reads easy to describe. It does not guarantee that every query will be cheap. The physical representation chapter follows the model into bytes, pages, and slots, where physical representation begins to determine memory use, I/O, and update behavior.

Sources and further reading

• PostgreSQL 18 constraints
• CMU database systems curriculum

Course index · Lesson plan