Designing a Schema That Matches the Work

10.1.1 PLAIN — in simple words#

1. A schema is a description of how a database organises information: which kinds of record exist, which values they hold and which rules connect them. Here we use the word for the overall design, not just a drawing of boxes.
2. Start with work that somebody must complete. Mira wants to record an agreed sale, find its lines and answer what was agreed. Dev may also need to prepare an unfinished draft. These activities do not necessarily have the same acceptance rules.
3. “We need an orders table” is already a proposed solution. “We need to distinguish an unfinished quotation from an agreed order” is a requirement the solution must address.
4. A workflow is the sequence of actions and decisions around that requirement. It includes unsuccessful actions: an unknown product, a missing quotation, a repeated submission or a rejected change.
5. A useful design asks what must be known before each action, what is allowed to change, what must remain true afterwards and who is allowed to perform it. The last question is a permission requirement, not something a table name answers.
6. Do not collect a field merely because another application has one. A field creates interpretation, validation, maintenance and access responsibilities. Explain its purpose or leave it out of the first proposal.
7. Equally, do not omit a distinction because it complicates a screen. A convenient screen that combines “proposed price” and “agreed price” can destroy the very information the business needs later.

10.1.2 PLAIN — a picture in your head#

1. Imagine designing a kitchen before asking whether it serves tea, full meals or a hundred packed lunches. You might buy impressive equipment and still put the washing area in the wrong place.
2. Understanding the work comes before arranging the cupboards. In a database, cupboards are tables and the movement between workstations resembles the workflow.
3. Follow one request from arrival to completion. Notice where it waits, where someone makes a decision and where another person needs evidence of that decision.
4. Now follow a request that fails. Where does the unfinished work go? Does it remain visibly unfinished, disappear, or accidentally become a completed record?
5. Where the comparison breaks: a database can contain several representations of the same activity, and programs can act simultaneously. Physical kitchen order does not model transaction isolation or prove that a computer operation is atomic.
6. The picture is useful for discovering responsibilities, not for choosing a storage engine or deriving its guarantees. Those require their own evidence.

10.1.3 PLAIN — a worked example#

1. Write the small shop requirement in sentences before SQL. The following is a teaching proposal, not a report of a real interview with Mira or Dev.

2. Our existing agreed-line contract requires a present, nonnegative integer price in paise. An unfinished quotation can be a different kind of record, with an explicitly absent price.
3. Therefore the proposal does not remove the price requirement from order_lines. It introduces an isolated sql_draft_lines teaching workspace for later exercises.
4. Existing order O-1042 still contains two notebooks at 7,550 paise each and one pen at 2,000 paise. Its total remains 17,100 paise.
5. Draft D-SQL-01 is a different invented activity. Its first line proposes three notebooks at 7,550 paise; its second proposes one pen whose price has not yet been quoted. These are not extra agreed sales.
6. “Move a finished draft into agreed history” remains an unimplemented workflow in the foundations lab. We can describe its requirements without pretending the draft table already performs that transition.

10.1.4 PLAIN — what is really happening inside#

1. A user action reaches application code, which interprets it and sends operations to the engine. The engine enforces the rules it knows. It does not automatically know every promise written in a product document.
2. Design work maps each promise to an actual mechanism. A required value may become a not-null rule; a valid product reference may become a foreign key; authority to agree a sale needs a permission and workflow mechanism.
3. Some promises span several records. Publishing an order and its lines as one unit requires more than a collection of individually acceptable inserts. The earlier publication lab uses a transaction for its bounded operation; later chapters develop the larger concurrency problem.
4. Separating responsibilities prevents a common misunderstanding: “the form checks it” is not the same claim as “every writer is prevented from violating it”. An import tool or maintenance script may not use that form.
5. The reverse misunderstanding is also costly. A database rule that rejects missing prices everywhere may block a legitimate unfinished draft. The solution is to model the stage honestly, not to fill unknown prices with zero.
6. Keep a requirements-to-mechanisms record. A blank mechanism entry is a visible gap, not permission to assume that some other component must handle it.

10.1.5 TECHNICAL — the engineer’s version#

1. Separate conceptual, logical and physical decisions. The conceptual model identifies entities, events and relationships in the work. The logical model gives record grain, attributes and constraints. The physical implementation chooses actual tables, types, indexes and engine-specific behaviour.
2. These layers are a design aid, not a claim that a project must finish one permanently before touching the next. A storage limit or awkward query can reveal a conceptual ambiguity and send the design back for clarification.
3. For each operation, record preconditions, postconditions and failure disposition. For the toy publisher, the input must satisfy the agreed-line profile, referenced parents must exist and the owned transaction must either publish the chosen batch or roll back its new lines.
4. Do not confuse those postconditions with facts the publisher cannot establish. A structurally accepted price does not prove a buyer agreed to it. The lab contains no authenticated human approval process.
5. Draw the write paths as well as the read paths. Application edits, imports, scheduled jobs and direct administrative access may have different checks. A rule claimed as universal needs coverage of the relevant writers, not merely the preferred user interface.
6. Use an explicit scope statement. The foundations case models INR-only, whole-unit, positive-quantity agreed lines without discounts, taxes, returns, delivery charges or payment settlement. A future extension must define those meanings rather than smuggle them into negative quantities or overloaded fields.
7. Database definitions specify types and declared constraints; they do not implement an entire business process by implication. PostgreSQL’s table-definition reference and SQLite’s corresponding reference describe those mechanisms separately from our original shop policy. [S74] [S61]
8. A reviewable proposal can therefore say: “record structure implemented; agreed-price immutability not implemented; draft finalisation not implemented; authorization not implemented”. That is a more useful engineering statement than “the order module is complete”.

10.1.6 WORDS — remember these#

1. Schema design: the plan for organising records and their rules — a model of structures, domains, relationships and invariants implemented by a database and its surrounding system.

2. Workflow: the steps around completing a piece of work — a sequence of operations, decisions and failure paths with defined state transitions.

3. Precondition: something that must hold before an action — a predicate required at an operation’s entry boundary.

4. Postcondition: something promised after an action — a predicate the completed operation is intended to establish or preserve.

5. Write path: a route through which information can change — an application, import, job or administrative mechanism capable of mutating stored state.

10.2.1 PLAIN — in simple words#

1. Finish this sentence for every table: “one row means one …”. Then test it against every column you propose to put there.
2. One order line can contain a quantity of two. It is still one line, not two rows waiting to be unfolded. One order can contain several lines without becoming several orders.
3. A field belongs with the fact it describes. An order’s currency applies to the order in this exercise; the agreed unit price belongs to the identified line; today’s catalogue price describes a product at the current catalogue state.
4. Choosing a boundary means deciding which things are represented separately. We separate orders from lines because one order can have several independently identified line entries.
5. An identity boundary is equally important. Line number 1 is meaningful within O-1042, not across every order in the shop. The complete identifier is the pair.
6. Do not make a one-row sentence so vague that it covers everything. “One row means some data about a sale” cannot tell you whether repeating an order total on every line is safe to sum.
7. A table can be internally tidy but still describe the wrong grain. The error appears when someone asks a question the model cannot answer without guessing.

10.2.2 PLAIN — a picture in your head#

1. Imagine an order envelope containing several numbered slips. The envelope identifies the order; each slip identifies one line within it.
2. The total written on the envelope describes the entire envelope. Copying that total onto each slip does not make it a separate contribution from each slip.
3. Two slips may name the same product and still be different lines. Perhaps they are separately agreed entries. Their difference is recorded by line identity, not inferred from the product name.
4. Product cards live in another tray because products can exist before any order and can appear on many orders. Customer profile cards are another kind of thing again.
5. Where the comparison breaks: the computer does not need literal nested storage for an order and its lines. Related SQL rows may live in separate managed structures and be combined by a query.
6. The envelopes also do not settle whether an empty order is allowed. Our teaching schema permits an order without lines; any stronger completion rule must be stated and implemented separately.

10.2.3 PLAIN — a worked example#

1. Compare three ways of counting the unchanged fixture.

2. Suppose a report copies 17,100 onto both lines of O-1042 and 11,550 onto both lines of O-1043. Adding that repeated column gives 57,300 paise, twice the correct combined total.
3. Nothing went wrong with addition. The report treated an order-grain value as a line-grain contribution.
4. Now propose a key (order_id, product_id). It would reject a second P-NOTE line on the same order even when the workflow treats it as a legitimate separate entry. Our earlier O-REPEAT exercise deliberately permits that case.
5. The existing key (order_id, line_no) identifies the line without inventing a “one product only once per order” policy.
6. Keep the distinction between a record’s identity and a display choice. A screen may combine identical products for convenience, but that display should not silently replace the historical line records.
7. A useful review question is: “After this transformation, what does one output row mean?” Ask it after joining, grouping, exporting and flattening, not only when creating the first table.

10.2.4 PLAIN — what is really happening inside#

1. A key lets a program address an intended record by values. When an update uses only part of a composite key, it may target several records rather than the intended one.
2. A line update using only line_no = 1 could touch the first line of both canonical orders. Adding order_id = 'O-1042' narrows the target to the full line identity.
3. The engine can enforce uniqueness of the complete pair. It cannot decide whether the chosen pair describes the business’s intended thing. That is a modelling decision checked through examples.
4. Relationships describe references between identified records. A foreign key from a line to an order says that a required parent exists; it does not count how many lines every parent must have. [S59]
5. Keeping boundaries explicit also helps changes. Renaming a product’s current display name should not require editing every order identifier. A product identity and its current label serve different purposes.
6. Do not split records merely to maximise the number of tables. Split when the meanings, multiplicities, lifecycles or enforcement responsibilities justify it. Chapter 13 develops the formal reasoning behind placing related facts.

10.2.5 TECHNICAL — the engineer’s version#

1. Write a grain contract with four elements: represented thing, key, allowed multiplicity and temporal interpretation. For order_lines: one identified agreed line within one order; key (order_id, line_no); repeated products permitted; price interpreted as the value agreed for that line.
2. The contract separates a functional association from a business rule. Each line key identifies its stored product and quantity. It does not follow that each (order_id, product_id) pair identifies at most one line.
3. Document relationship direction. A non-null line reference constrains the child to an existing parent. It does not establish a parent’s participation minimum or prove that the order has been completed.
4. Review optionality by state rather than convenience. orders.customer_id is nullable in the toy model. Its absence means no linked profile in this record; it does not prove the buyer was anonymous, refused identification or never had a profile.
5. Distinguish transport shape from storage shape. The agreed-line transport repeats currency so each incoming record can be validated independently. The relational model places INR currency on the parent order and requires publication to preserve that contract.
6. A flattened export may intentionally repeat parent attributes. Document which fields are dimensions or annotations and which can be added at the export’s grain. Otherwise the 57,300-paise error can reappear in somebody else’s spreadsheet.
7. The new schema_snapshot() helper reports stored definitions using SQLite’s metadata interfaces. It can reveal the declared key and references; it cannot infer an unwritten row-grain sentence from business activity. [S75]
8. The review test for a boundary is not “does this look normal?”. It is “can this representation distinguish the valid cases we need, reject the invalid cases it promises to reject, and answer the required questions without inventing missing facts?”.

10.2.6 WORDS — remember these#

1. Identity boundary: the scope inside which a label identifies something — the domain over which a key or composite key is interpreted and uniqueness is enforced.

2. Grain contract: a precise statement of what one row represents — documentation tying a record’s unit, key, multiplicity and temporal meaning together.

3. Participation minimum: the least number of required related records — a cardinality condition such as “every completed order has at least one line”.

4. Flattened export: related information copied into a single rectangular result — a representation that may repeat parent attributes at a finer output grain.

5. Temporal interpretation: which point or period a value describes — the time-related meaning that distinguishes a current attribute from a historical observation or agreement.

10.3.1 PLAIN — in simple words#

1. Two fields can hold the same number today and still mean different things. Today’s notebook price and the notebook price agreed on O-1042 both start at 7,550 paise, but they answer different questions.
2. Tomorrow’s catalogue can change without changing yesterday’s agreement. Replacing an agreed historical price with a current catalogue lookup would answer a new question while appearing to show the old one.
3. A draft is different again. It represents unfinished work whose proposed values may still change. A missing quotation in a draft is not a free item and not an agreed sale.
4. A value is mutable when the intended workflow allows it to change. Calling another value historical describes its intended meaning, but does not automatically prevent a program from overwriting it.
5. Therefore ask two separate questions: “what should this value mean?” and “what mechanism protects that meaning?” A good name answers only the first part.
6. Correction does not mean pretending the earlier record never existed. The required correction process depends on what evidence, timing and authority must remain available. The foundations lab does not implement that full process.
7. Keeping meanings apart makes the application easier to explain: “current price”, “proposed price” and “agreed price” can all be shown honestly without forcing one field to stand for three different claims.

10.3.2 PLAIN — a picture in your head#

1. Think of a shop’s chalkboard, a pencil-written quotation and a previously issued order document.
2. Erasing the chalkboard changes what the shop is offering now. Editing the pencil quotation changes unfinished work. Neither act should silently rewrite the amount shown on an earlier agreed line.
3. If the old order contains a mistake, the shop needs an appropriate correction process. Borrowing the chalkboard eraser is not a complete policy for historical evidence.
4. Where the comparison breaks: digital records are not physically protected by their appearance. A table called history can still allow ordinary updates unless the system actually restricts them.
5. Nor does an append-only-looking screen prove that backups, administrators or other write paths cannot change the data. The model must state which protection exists and against which kinds of change.
6. The analogy distinguishes meanings. It does not establish a legal retention rule, an audit guarantee or an implementation of immutability.

10.3.3 PLAIN — a worked example#

1. Reuse the separate current-price experiment from Chapter 9. Change only P-NOTE’s current catalogue price from 7,550 to 8,250 paise in a fresh practice database.
2. The three historical notebook units still use 7,550 paise in their agreed lines. The three pen units still use 2,000 paise.
3. Historical total: 3 × 7,550 + 3 × 2,000 = 28,650 paise. O-1042 remains 17,100 and O-1043 remains 11,550.
4. Hypothetical value at today’s catalogue prices: 3 × 8,250 + 3 × 2,000 = 30,750 paise. The difference is 3 × 700 = 2,100 paise.
5. Both calculations are internally consistent. They answer different questions. The hypothetical 30,750 is not newly discovered historical revenue.
6. Now inspect the separate draft D-SQL-01. Three proposed notebooks at 7,550 contribute 22,650 paise, but the pen quotation is absent. We cannot claim a complete quoted total by treating that absence as zero.
7. The lab’s three meanings are shown in the figure. The draft values and its identifier are explicitly new; the historical fixture is not rewritten.

Figure 10 — Current catalogue, editable draft and agreed order lines answer different questions. The draft is new teaching data; the historical combined total remains 28,650 paise.

10.3.4 PLAIN — what is really happening inside#

1. The product row and the agreed line store different attributes. Changing the product row does not automatically rewrite the line’s copied agreed price because they are separate stored values.
2. A query that multiplies quantities by the product’s current price nevertheless produces a repriced result. The historical values can remain intact while the report chooses the wrong source for its question.
3. Protection therefore has at least two concerns: prevent unauthorized or inappropriate writes, and ensure reads use the intended meaning.
4. The present teaching schema checks whether an agreed price is a present integer within its bounds. It does not check whether every later update is an authorized historical correction.
5. A new test deliberately changes one agreed price to 1 paise in an isolated disposable copy. The structure accepts it, and O-1042’s computed total becomes 2,002 paise. That is evidence of a missing workflow protection, not a legitimate correction to the book’s canonical order.
6. Keeping that negative example visible is useful. It prevents the phrase “we stored a snapshot price” from expanding into the unsupported claim “the system guarantees immutable agreements”.

10.3.5 TECHNICAL — the engineer’s version#

1. A historical snapshot attribute can intentionally duplicate a value that also appears in a current entity record. The duplication is not necessarily redundant in meaning: catalogue_price_minor and unit_price_minor have different temporal semantics.
2. The snapshot’s preservation policy must identify allowed mutations, correction provenance, authorizing actors and read behaviour. This schema implements none of those broader controls merely by using a separate column.
3. The isolated draft table accepts nullable quantity and price under bounded checks, while the agreed-line table remains not-null. This is a separate lifecycle representation, not a backwards-compatible relaxation of the original accepted-record contract.
4. A future finalisation operation would need to validate the complete draft, establish the agreed identifiers and values, coordinate publication, prevent duplicate effects under its chosen retry policy and handle failures. That design is deferred; a successful draft edit is not finalisation.
5. The new lab’s price_meanings_demo() explicitly returns both agreed totals and a labelled hypothetical current-catalogue total. The arithmetic uses bounded integer paise, not binary floating-point money.
6. The same principle applies outside prices. A customer’s current address is not automatically the address used for an earlier delivery. An employee’s current role is not automatically the role held when a past approval occurred. Those are design illustrations, not extra implemented shop features.
7. Decide deliberately which historical claims are needed. Snapshotting every attribute forever is not the default answer; each retained field needs a purpose and an appropriate lifecycle. Later chapters discuss retention, access and operations in their own scope.
8. Mark the protection level accurately in documentation: separate meanings stored; selected query results tested; historical update prevention absent; complete evidence and permission workflow absent. This record lets later implementation work close a real gap instead of discovering that an earlier promise was fictional.

10.3.6 WORDS — remember these#

1. Current attribute: what a record says about the present state — a value whose intended temporal interpretation follows the entity’s current representation.

2. Historical snapshot attribute: a value copied to describe an earlier event or agreement — a stored attribute with event-specific temporal semantics, not automatic immutability.

3. Draft record: unfinished work that remains open to revision — a lifecycle-specific representation with explicit incompleteness rules and no implied finalisation.

4. Repricing: calculating using a different price basis — recomputation with current or alternative prices rather than the stored agreed values.

5. Mutation policy: the rules for changing a record — a specification of permitted transitions, authorization, evidence and failure handling.

10.4.1 PLAIN — in simple words#

1. A storage type says what kind of value an engine can store. A business domain says what those values are allowed to mean in this particular role.
2. An integer might be a count, an amount in paise or an identifier generated by a program. Being an integer does not make it interchangeable with every other integer.
3. Our agreed quantity is a positive whole-unit count. Our agreed price is a nonnegative number of paise. Zero is allowed for the price under this toy contract, but not for the quantity.
4. Missing is another decision. A nullable draft price means the quotation is not present. A not-null agreed price means the accepted line must supply one. Neither decision comes from the word “integer” alone.
5. Bounds are part of the implemented contract. The relational lab limits quantity to 10,000 and unit price to 100,000,000 paise per line. These are teaching adapter limits, not universal laws about shops.
6. A type conversion can lose a distinction before the database sees it. A program may turn a Boolean true into the integer one. If the input contract rejects Booleans as quantities, validate that at the boundary where the distinction still exists.
7. Think of a domain as a complete acceptance description: representation, unit, allowed range, missingness, identity rules and interpretation. A few of those pieces can be enforced in SQL; others need additional application checks or evidence.

10.4.2 PLAIN — a picture in your head#

1. Imagine receiving labelled parcels. The outer box size resembles the storage type: it determines what physically fits.
2. A label saying “whole notebooks” adds meaning. The box may physically fit half a broken notebook, but that is not an acceptable value for the agreed count in this exercise.
3. A price parcel labelled “paise” is different from one labelled “rupees”, even if both contain the handwritten number 75.
4. The receiving desk must inspect the label and contents together. Checking only that the parcel fits through the doorway misses the unit error.
5. Where the comparison breaks: some database engines convert values as they accept them, and a program can adapt values before sending them. There is no universal untouched parcel that every layer inspects identically.
6. The analogy therefore reminds us to test the full path from input representation to stored value. It does not imply that every valid business concept deserves a custom database type.

10.4.3 PLAIN — a worked example#

1. Compare the domain decisions below. They deliberately preserve the existing accepted-line profile while allowing a separate incomplete draft.

2. For O-1042 line 1, quantity 2 and price 7,550 produce 15,100 paise. Quantity true is rejected by the original input validator even though a programming language may treat true as numerically related to one.
3. In a separate SQLite STRICT demonstration, a bound Boolean true reaches integer storage as 1. The text value "2" can be losslessly converted to integer 2, while the real value 2.5 is rejected for the integer column. These observed cases illustrate the documented conversion boundary. [S60] [S86]
4. Suppose someone sends the integer 75 intending INR75.00, but the field’s unit is paise. The stored value 75 is within the allowed range. A range check cannot discover the sender’s intention.
5. Our test accepts that plausible-but-wrong-unit value in a separately named example. Its lesson is not to prohibit 75 paise universally; it is to establish units before converting and validate the source contract.
6. The original source validator and destination adapter have different responsibilities. A record can pass a broad source profile and still exceed the destination’s chosen bounds. Publication must check that second boundary explicitly.

10.4.4 PLAIN — what is really happening inside#

1. The application receives values in some runtime representation, such as a Python string, integer or Boolean. Validation happens against that representation and the incoming record’s contract.
2. The database driver then binds supported values to a statement. The engine applies its storage rules and constraints. A later reader receives the stored result, not necessarily the original representation.
3. That sequence explains why a database-only check cannot recover a distinction already erased by the driver or earlier conversion.
4. It also explains why two layers of checking need not be pointless repetition. The input layer can reject an unexpected runtime type; the storage layer can protect a declared range when another writer bypasses the usual input code.
5. Keep errors specific enough to diagnose the boundary. “Unsupported Boolean quantity”, “quantity above adapter limit” and “referenced product missing” are different failures with different remedies.
6. Changing a domain can change more than storage. Allowing fractional quantities would affect arithmetic, formatting, validation and the meaning of a unit. Such a change deserves a versioned contract, not an unnoticed switch from integer to real.

10.4.5 TECHNICAL — the engineer’s version#

1. PostgreSQL has a concrete CREATE DOMAIN feature for reusable constraints over a base type. That product feature is one implementation tool; the broader modelling concept of a domain is not limited to it. [S68]
2. The following PostgreSQL 17 fragment is illustrative and was not executed in the SQLite lab. It gives the numeric range a reusable name while leaving presence to the using column.

CREATE DOMAIN nonnegative_minor AS bigint
    CHECK (VALUE >= 0 AND VALUE <= 100000000);

CREATE TABLE quoted_amount_example (
    example_id text PRIMARY KEY,
    unit_price_minor nonnegative_minor NOT NULL
);

3. The name does not encode the currency by itself, nor establish a buyer’s agreement. A complete contract would still state that the amount is in paise for INR under the chosen exercise.
4. Do not teach domain-level not-null as a universal guarantee that no expression can ever produce a null value of that type. PostgreSQL documents important caveats; using column-level presence requirements keeps this introductory example explicit. [S68]
5. SQLite STRICT tables do not provide the same CREATE DOMAIN facility. The executed lab uses supported storage type names, row checks, not-null declarations, references and pre-binding validation. [S60]
6. Analyse arithmetic bounds at the operation’s grain. A line quantity up to 10,000 multiplied by a price up to 100,000,000 is at most 1,000,000,000,000 paise. That product fits within a signed 64-bit integer, but an unbounded sum over arbitrarily many lines needs its own analysis.
7. Domain documentation should distinguish stored values from derived values. A line total is quantity times its agreed unit price here. A formatted amount string is a display result. Neither should be confused with the original price field.
8. The tested Python boundary uses exact type checks where Booleans must be excluded. This is a deliberate contract choice, not a claim that every application must reject all lossless textual number inputs. Different interfaces can have different documented profiles.

10.4.6 WORDS — remember these#

1. Business domain: the values that make sense for a particular purpose — a semantic acceptance set including units, bounds, missingness and interpretation.

2. Storage type: the kind of value an engine stores in a field — an implementation-level representation with defined conversion and operation rules.

3. Lossless coercion: changing representation without losing the represented value — an allowed conversion such as a supported textual integer into integer storage.

4. Pre-binding validation: checking input before the driver sends it — validation while original runtime distinctions remain available to application code.

5. Arithmetic bound: the largest or smallest result an operation may produce — a range derived from operand limits and the operation’s cardinality.

10.5.1 PLAIN — in simple words#

1. A name is a short label, not a complete explanation. amount leaves too many questions open: amount of what, in which unit, at which time and under which calculation?
2. unit_price_minor is better in this model because it distinguishes a unit price from a line total. Its documentation must still state paise, INR, agreement time and allowed values.
3. A data dictionary is a small reference describing tables and fields. It should answer the questions a careful new reader would otherwise have to ask the original developer.
4. Write down missing-value meaning. The nullable customer link says “no linked profile in this record”, not a guessed reason such as “customer refused”.
5. Write down the grain and key. A future analyst should not have to inspect a screen or guess from the table’s plural name before knowing what a row counts.
6. Keep examples next to definitions. One valid value and one misleading-but-plausible value often explain a field better than a long abstract description.
7. Documentation can be wrong or stale. Compare it with the stored definition and tests whenever the model changes. The dictionary is part of the work, not evidence that the work has already been verified.

10.5.2 PLAIN — a picture in your head#

1. Think of a building plan with a legend. A symbol is useful because the legend explains its meaning consistently everywhere it appears.
2. Without a legend, one circle might mean a socket, a light or a drain. Guessing from appearances is dangerous when people make real changes.
3. Field names are the symbols. The data dictionary is the legend explaining units, roles and exceptions.
4. Where the comparison breaks: the database can change while an old document stays in a folder. Unlike a printed plan issued with one building, software and its documentation need an explicit update process.
5. The dictionary also cannot replace enforcement. Writing “quantity must be positive” in a comment does not make an otherwise unconstrained column reject minus three.
6. A practical review reads three things together: the intended definition, the actual database definition and a small set of observed acceptance tests.

10.5.3 PLAIN — a worked example#

1. Here is a compact entry for the existing agreed-line price. It documents the current teaching scope rather than adding a new requirement silently.

2. Now document orders.customer_id: optional reference to a stored profile. O-1042 has NULL; O-1043 has C-001 under the earlier synthetic annotation.
3. Do not expand C-001 into an invented verified identity. The reference demonstrates linkage and optionality; it does not provide authentication evidence.
4. The new lab exposes a partial DATA_DICTIONARY for its core order tables and compares key facts against actual metadata. It is intentionally partial, not a complete enterprise catalogue.
5. Names such as catalogue_price_minor and unit_price_minor make a difference visible. The definitions explain why the values may diverge after the catalogue price changes.
6. A query returning an alias called total_minor should document whether it means one line, one order or the entire selected set. Reusing the same alias at different grains is possible, but consumers need the result contract.

10.5.4 PLAIN — what is really happening inside#

1. Engines maintain system information about their own tables and columns. Programs can query that information instead of relying entirely on a separate document.
2. SQLite’s metadata interfaces can report column names, not-null settings, primary-key positions and declared foreign keys. They cannot explain why Mira chose INR or what evidence established a particular agreement. [S75]
3. PostgreSQL provides an information schema with a standard-oriented view of many database objects. Product-specific catalogues supply additional details. These are ways to inspect implementation, not automatic business documentation. [S71]
4. Some engines also store comments attached to objects. A comment can explain purpose, but it remains descriptive text rather than an enforced rule. [S70]
5. Keep sensitive values out of examples and comments. This book uses invented records, and the reader package needs no credentials or live customer data.
6. A good documentation update records what changed and why. Merely regenerating a column list cannot reveal that a field quietly changed from paise to rupees while retaining the same integer type.

10.5.5 TECHNICAL — the engineer’s version#

1. Distinguish three uses of “schema”: the overall data design discussed in this chapter, a particular structured-record contract, and PostgreSQL’s named namespace containing objects. Context must make the intended use clear.
2. PostgreSQL permits qualified names such as sales.orders, where sales is a namespace. Name resolution and search-path configuration have operational and security implications; no new PostgreSQL namespace is created by this SQLite exercise. [S69]
3. Prefer a consistent, documented identifier convention. The lab uses lower-case words with underscores and avoids relying on quoted mixed-case identifiers. This is a project convention, not a universal SQL requirement. PostgreSQL’s lexical rules explain how quoted and unquoted identifiers are treated in that engine. [S72]
4. The actual SQLite inspection used by the lab includes:

PRAGMA table_info(order_lines);
PRAGMA foreign_key_list(order_lines);
PRAGMA foreign_keys;

5. table_info describes ordinary column metadata, not every possible hidden or generated-column detail; broader inspection can require other metadata interfaces. Read the documented scope before treating a metadata query as a complete schema export. [S75]
6. A field dictionary should include ownership of meaning, expected sources, domain, unit, null policy, derivation if any, constraints, allowed mutation and consumer interpretation. This is our review checklist, not a claim that one industry standard mandates this exact list.
7. Keep a distinction between machine-checkable and human-reviewable statements. A test can compare a declared bound with a constant. A human workflow review is still needed to decide whether the bound or meaning is suitable for the intended work.
8. For publication, generated chapter metadata, glossary and examples must be derived from the same manuscript version. Otherwise a book can teach one field meaning in print and a different one in its browser reference. The same continuity discipline used for a schema applies to its teaching material.

10.5.6 WORDS — remember these#

1. Data dictionary: the reference explaining what fields mean — maintained metadata describing grain, domains, units, source, lifecycle and interpretation.

2. Namespace: a named container used to distinguish object names — in PostgreSQL, a schema that can contain tables and other database objects.

3. Qualified name: a name that includes its containing scope — an identifier such as sales.orders rather than an unqualified table name.

4. System metadata: information the engine stores about its own objects — structural descriptions exposed through catalogues or metadata interfaces.

5. Result contract: the meaning promised by a query’s output — a specification of columns, units, grain, missingness and ordering where relevant.

10.6.1 PLAIN — in simple words#

1. A model review asks whether the records match the intended work. It is not a contest to see who knows the most database vocabulary.
2. Show small examples. Ask the person responsible for the workflow what each example means, what action should be allowed and what evidence should remain.
3. Include awkward cases early: the same product twice on one order, no customer link, an unfinished quotation, an order with no lines and a changed current price.
4. Ask about things the database would accept even though the business would reject them. A positive price entered in the wrong unit is a useful example because it passes an ordinary range test.
5. Record decisions as decisions. “The product owner approved this rule” requires actual approval; a developer’s proposal or a fictional conversation in a textbook is not a substitute.
6. A counterexample is a specific case that exposes a claim as too broad. Finding one during design is valuable because it points directly to the missing distinction or mechanism.
7. Finish the review with open questions and responsible next steps. A clear list of unresolved items is better than a vague claim that everyone liked the diagram.

10.6.2 PLAIN — a picture in your head#

1. Imagine trying a key in the actual locks before making a thousand copies. Looking at the key’s neat shape cannot prove it opens the right doors.
2. Valid examples are the doors that should open. Invalid examples are the doors that must stay closed.
3. A model can fail in either direction: reject legitimate work or admit a harmful ambiguity. Testing only that one happy-path order fits examines only one door.
4. Where the comparison breaks: an information model has far more possible inputs and sequences than a small ring of locks. A finite test set is evidence about selected cases, not a proof of every future behaviour.
5. Nor can a test establish an unspoken business preference. Someone with authority over the workflow must decide whether a case is valid before the implementation’s response can be judged against that requirement.
6. Use the picture to remember both sides of acceptance, while keeping the limits of testing explicit.

10.6.3 PLAIN — a worked example#

1. Run this review on the existing model. The outcomes below describe the bounded lab and the stated teaching policy.

2. The review does not “fix” these by adding constraints blindly. Some entries reveal intended flexibility, some missing mechanisms and some unavailable evidence.
3. A minimum-one-line rule might apply only when an order is finalised. Applying it indiscriminately to every intermediate operation could make a legitimate construction sequence impossible.
4. Historical price protection needs an allowed-correction policy as well as prevention of casual overwrites. “Never update anything” is not automatically a complete business solution.
5. The output is a bounded proposal: keep the canonical agreed-line structure, maintain the optional profile annotation, use a separate draft workspace, and explicitly defer complete finalisation and authorization.
6. This is an author-created design exercise. It records no real shop interview, owner acceptance or production-readiness decision.

10.6.4 PLAIN — what is really happening inside#

1. Review turns vague language into predicates and examples. “Correct order” becomes a list of concrete structural, workflow and evidence requirements.
2. Tests then observe the implemented subset. One test inserts a duplicate full line key and expects rejection. Another inserts a different line number for the same product and expects acceptance.
3. A negative demonstration can intentionally show missing protection. The isolated historical-price update is valuable precisely because it succeeds where an overly broad claim would predict failure.
4. Keep those tests separate from an accepted workflow. The demonstration’s database is disposable; its changed total must never replace the canonical example or be presented as an authorized correction.
5. When a requirement changes, update the proposal, enforcement and examples together. Otherwise tests can keep passing against an obsolete definition of success.
6. The review ends with a boundary: the current schema and selected examples are described and tested, but future concurrency, permissions, finalisation and operational recovery work remains to be designed and verified.

10.6.5 TECHNICAL — the engineer’s version#

1. Use a traceability matrix connecting requirement, assumption, representation, enforcement, test and remaining limitation. Each column answers a different question; a source citation or a test name should not be pasted across all columns as if it were universal evidence.
2. Classify failures. A model defect cannot represent a valid case; an enforcement gap permits a case the rule rejects; an evidence gap leaves the system unable to know whether the real-world condition held. These categories can coexist but should not be conflated.
3. The new lab checks the stored seven-table model, canonical totals, current-versus-agreed price separation, accepted repeated-product lines and the deliberately absent protections. It does not execute a schema migration or modify the earlier lab’s definitions.
4. Review sample sequences as well as isolated records. “Create draft, edit quantity, leave price absent, cancel draft” differs from “agree order, later change current catalogue, reread agreed total”. The representation must keep their meanings distinguishable.
5. Include range boundaries and type boundaries in tests. For the current adapter, quantity 10,000 and price 100,000,000 are accepted limits; values above those limits are rejected. Zero price is valid, zero quantity is not.
6. Document ambiguous failures honestly. A guarded update that finds no matching row may indicate absence or a changed expected value. Do not report one specific explanation unless the available evidence establishes it.
7. A test suite’s size is not a coverage measure by itself. The significant question is which classes of records, transitions and failures were exercised and which were omitted.
8. Carry unresolved items into the next milestone. Chapter 11 develops declared constraints and their exact limits; Chapter 12 develops basic queries and bounded mutations; later chapters address larger transactions, concurrency, security and lifecycle operations.

10.6.6 WORDS — remember these#

1. Counterexample: a concrete case that disproves an overbroad statement — an input or sequence for which a claimed invariant or interpretation does not hold.

2. Traceability matrix: a record showing how a requirement is addressed — a mapping among assumptions, representation, enforcement, tests and remaining gaps.

3. Model defect: a representation that cannot express the intended work correctly — a structural or semantic mismatch with valid domain cases.

4. Enforcement gap: a declared rule without sufficient protection — a condition the implementation can violate despite the intended policy.

5. Evidence gap: something the records cannot establish — missing information or verification needed to support a claim about the world.