Tables, Rows and Relationships

9.1.1 PLAIN — in simple words#

1. A table groups records with the same intended kind of meaning. Before choosing its columns, finish the sentence “one row in this table means one …”.
2. In a product table, one row may describe one product identity. In an order table, one row may describe one identified order. In an order-line table, one row describes one identified line within an order.
3. Those are different grains. An order containing two lines is still one order, and a line with quantity two is still one line.
4. A table’s rows have values in named columns. The names help organise the claims, but their full meaning still comes from the contract developed in Part A.
5. A stored row does not have a dependable “position number” unless the model explicitly defines one. A query’s displayed order should be requested explicitly rather than inferred from yesterday’s screen. [S03]
6. The relational idea treats a relation as a set of tuples. Ordinary SQL tables and query results need an important qualification: duplicate-looking rows can occur unless constraints or query operations prevent them. [S56] [S57]
7. Therefore a useful simple definition is “a collection of same-shaped claims under declared rules”, followed by an explicit discussion of keys, duplicates and ordering.

9.1.2 PLAIN — a picture in your head#

1. Imagine three trays of cards. One tray contains product cards, another order cards and another order-line cards.
2. Mixing all three kinds of card in one tray would make questions harder. “How many cards?” would no longer tell you how many products or orders existed.
3. The row-grain sentence is the label on the tray: “one card for one order line”. It tells the reader how to interpret a count.
4. A key is a reliable way to identify a card under the model’s scope. It is not the card’s current position in the tray.
5. Where the comparison breaks: SQL can produce repeated result rows, and a query can manufacture a new grain by joining or grouping. The result is not necessarily a simple subset of the original cards.
6. The analogy also does not grant the cards truth. A tidy tray can contain incomplete or inaccurate records. The table structure helps state and enforce some rules; it cannot verify the entire world.

9.1.3 PLAIN — a worked example#

1. The four canonical lines are shown below. Their identity is the pair (order_id, line_no).

2. Count the rows: four order lines. Count distinct order identifiers: two orders. Sum quantities: six units. Sum line totals: 28,650 paise.
3. These are four different questions. A screen showing four rows does not entitle us to label the result “four orders”.
4. If a query returns only product_id, the visible result includes P-NOTE twice and P-PEN twice. That repetition does not mean the underlying line identities are duplicates.
5. Asking for distinct product identifiers produces two values. It answers “which represented products?” rather than “how many lines?” or “how many units?”.
6. Sorting by order identifier and line number makes the display reproducible for this fixture. Without an ordering clause, the application must not depend on incidental row-return order. [S03]

9.1.4 PLAIN — what is really happening inside#

1. A table definition describes its columns and constraints. A primary key supplies one chosen way to distinguish its rows by values.
2. The engine may store records in a layout that changes as data is inserted, updated, indexed or maintained. A query result is computed from that managed data, not read as a guaranteed spreadsheet row order. [S56]
3. A projection chooses output columns. It can hide the very key that distinguished two source rows, making them look identical in the result.
4. DISTINCT removes duplicate result rows according to the selected values. It does not reconstruct omitted source identities or repair a poorly chosen row grain. [S57]
5. A filter keeps rows satisfying a condition. An aggregate combines rows into a different result, such as one total per order. Each operation should have an explicit meaning for one output row.
6. The application should document that output meaning where it matters. “One row per order” is a checkable requirement; “make a sales report” is too broad to identify many mistakes.

9.1.5 TECHNICAL — the engineer’s version#

1. In the relational model, a relation has a heading of attributes and a body of tuples. Mathematical set semantics exclude duplicate tuples. SQL’s default multiset behaviour and NULL semantics require additional care rather than a claim that every SQL result is literally such a set. [S56] [S57]
2. The implementation uses primary keys on entity tables and the composite key (order_id, line_no) on order lines. The key is a uniqueness and presence requirement, not a physical pointer to a screen row.
3. An unordered query can return any order consistent with its semantics. Use ORDER BY when sequence is part of the required result. An index or a previously observed order is not a substitute. [S03]
4. A compact query for the canonical order totals is:

SELECT order_id,
       SUM(quantity * unit_price_minor) AS total_minor
FROM order_lines
GROUP BY order_id
ORDER BY order_id;

5. The result grain is one row per represented order identifier. An order with no lines does not appear in this query, because the input is the line table. Whether such an order should appear with an empty or zero total is a separate reporting decision.
6. SQL aggregation operates on stored values. The toy schema places explicit per-line bounds on quantity and price so individual products fit comfortably in SQLite integer storage. Arbitrarily large accumulated totals still need separate range analysis.
7. Avoid using DISTINCT as a general “remove wrong totals” button. Two legitimate lines can have equal amounts. Removing repeated amount values could discard valid contributions rather than eliminate erroneous join multiplicity.
8. The four-line fixture is deliberately small enough to count by hand. That makes the intended meaning independently inspectable before a more complex query obscures it.

9.1.6 WORDS — remember these#

1. Table: a collection of records with a declared shape — a managed SQL structure whose columns and constraints give rows a defined interpretation.

2. Row grain: what one row stands for — the unit of representation against which counts and aggregates must be interpreted.

3. Tuple: one same-shaped collection of attribute values — a row-like element of a relation in the relational model.

4. Projection, in a query: choosing which values to display — an operation that selects output columns and can hide source identity.

5. Multiset: a collection that can contain repeated values — the bag-style semantics relevant to many ordinary SQL results.

6. DISTINCT: removing repeated output rows — a SQL operation over selected result values, not automatic repair of a data model.

9.2.1 PLAIN — in simple words#

1. A column has a storage type, but that is not its complete meaning. An integer can count notebooks, paise, seconds or order-line positions.
2. A domain describes the values that make sense for a particular role. A quantity may need to be a positive whole number. A currency may need to be the literal INR in this particular exercise.
3. The domain can be narrower than the type. The integer type can hold a negative value even when the shop’s agreed sale quantity must be positive.
4. It can also be more specific than a range. A product identifier must name an appropriate product, not merely be a nonempty string.
5. A database constraint can enforce some of these rules. Other rules involve application workflow or external evidence and cannot be inferred from a column declaration alone.
6. Naming units in fields such as unit_price_minor helps, but a name does not stop a program from supplying rupees where paise were expected. The importer and schema must reinforce the agreement.
7. Clear columns let the simple explanation and the engineering implementation describe the same thing instead of relying on hidden assumptions.

9.2.2 PLAIN — a picture in your head#

1. Imagine drawers labelled quantity, price and identifier. All three may contain digits written on cards, but the drawers have different acceptance rules.
2. A price drawer may permit zero for a free item under this toy contract. A sale-quantity drawer does not permit zero because a line here represents a positive number of units.
3. An identifier drawer preserves the text 00017 rather than performing arithmetic on it. Converting it to seventeen would change the agreed label.
4. Where the comparison breaks: database engines do not all apply identical type rules. Some convert incoming values when a conversion is possible; some reject values under different conditions. The drawer labels alone do not reveal the engine’s coercion semantics.
5. A value can therefore arrive as one runtime type and be stored as another. The application should validate any distinction that would be lost during binding or conversion.
6. The goal is not the strictest imaginable rejection policy. It is a deliberate policy whose actual behaviour matches the contract and is covered by tests.

9.2.3 PLAIN — a worked example#

1. Consider the quantity column for an agreed line. The following values have different outcomes at the input boundary.

2. These are choices in the preserved domain contract. Another application may choose different coercions, but it should make them explicit.
3. The new SQLite destination adds classroom capacity bounds: quantity at most 10,000 and agreed unit price at most 100,000,000 paise. Both canonical orders remain far inside these bounds.
4. Those upper bounds are not retroactive claims about the original business contract. They are additional storage-adapter rules for this executable destination and are reported as such.
5. A line with quantity 10,001 can satisfy the older positive-integer rule but fail this destination’s capacity rule. That is a meaningful stage-specific rejection, not evidence that the JSON parser failed.
6. An unknown product identifier can satisfy every local field rule and still fail the relationship check. The next section explains why the database needs that additional rule.

9.2.4 PLAIN — what is really happening inside#

1. The importer first validates Python values under the line contract. The database driver binds those values to a statement. The engine then applies its own typing and constraint semantics.
2. SQLite normally has flexible typing. Its STRICT table option, available since SQLite 3.37.0, narrows allowed column type names and enforces stricter storage-type behaviour. It can still perform certain lossless conversions. [S21] [S60]
3. Therefore STRICT does not mean “the original Python object had exactly the type my business validator wanted”. Some source distinctions no longer exist after values cross the driver boundary.
4. A NOT NULL declaration rejects a missing database value. A CHECK condition can reject values outside an allowed range. In SQLite and PostgreSQL, a CHECK expression yielding NULL does not by itself reject the row, so presence must be declared separately when required. [S02] [S61]
5. A foreign key checks a relationship to another table. It cannot be replaced by a local range check on an identifier.
6. These layers are complementary. Input validation gives useful early diagnostics; database constraints also protect writes made through other code paths within the enforced configuration.
7. The implementation tests both the intended application route and direct constraint violations, without claiming that the engine can reconstruct distinctions already erased by conversion.

9.2.5 TECHNICAL — the engineer’s version#

1. A conceptual domain combines representation, allowed values and meaning. It need not correspond to a database feature named DOMAIN in every engine. SQLite’s teaching schema expresses relevant rules with types, NOT NULL, CHECK, primary keys and foreign keys.
2. The destination’s numeric limits are deliberate: 1 <= quantity <= 10000 and 0 <= unit_price_minor <= 100000000. Each line amount is therefore at most 1,000,000,000,000 paise. These are lab bounds, not price or capacity recommendations.
3. Multiplication for one bounded line fits in a signed 64-bit integer. Summing indefinitely many such lines can still overflow. A per-row bound is not a proof about every future aggregate; Chapter 3’s numeric-range reasoning remains necessary.
4. The schema uses TEXT NOT NULL PRIMARY KEY for named identifiers. This avoids relying on older SQLite behaviours that permit NULL in some ordinary primary-key declarations. STRICT adds further type enforcement, but explicit presence remains readable documentation. [S61] [S60]
5. CHECK(quantity > 0) and NOT NULL do different work. The CHECK does not establish presence when its expression evaluates to NULL. Declare both when both are required. [S02] [S61]
6. A Python Boolean bound directly into SQLite can become integer 0 or 1. The database then sees the stored value, not a preserved Boolean-origin label. The API’s explicit type(value) is int rule must therefore run before binding. [S20] [S22]
7. Do not describe the SQL as universally portable. STRICT, pragma configuration, autocommit conventions and particular constraint behaviour are SQLite-specific. PostgreSQL is referenced separately to explain concepts and differences, not to claim that this exact script was executed there.
8. Every rejection should identify its layer: input profile, domain value, destination capacity, relational integrity or transaction outcome. This makes the system’s explanation useful instead of reducing every failure to “bad data”.

9.2.6 WORDS — remember these#

1. Column: a named role within each row — an attribute position with declared type and associated meaning.

2. Domain: the values and meaning permitted for a role — a business-level contract potentially narrower than a storage type.

3. Coercion: converting a value into another representation or type — an engine or application behaviour that must be deliberate when it changes interpretation.

4. NOT NULL: a requirement that a database value be present — a constraint rejecting SQL NULL for a column.

5. CHECK constraint: a declared predicate on acceptable values — an engine-enforced condition whose NULL semantics must also be understood.

6. Destination capacity rule: an extra bound imposed by this storage adapter — a limitation distinct from parsing and the earlier domain contract.

9.3.1 PLAIN — in simple words#

1. A relationship describes how records are allowed to refer to one another.
2. One order can contain many lines. Each line in our model belongs to one order. This is a one-to-many relationship from orders to lines.
3. A line therefore carries an order_id that refers to an existing order. A foreign key lets the engine check that reference.
4. A one-to-one relationship needs an additional limit. For example, a customer profile may have at most one separate preference record in our optional teaching extension.
5. “At most one” and “exactly one” are different requirements. A unique reference can prevent a second preference record without forcing a preference record to exist for every customer.
6. Likewise, requiring every line to name an existing order does not force every order to have at least one line. The foreign key points from the child to the parent, not the other way around.
7. A diagram should show these minimum and maximum counts clearly enough that someone can test the claims it makes.

9.3.2 PLAIN — a picture in your head#

1. Picture one envelope for an order, with several line cards bearing its order number. Many cards can name the same envelope.
2. The line cards are the children and the order envelope is the parent in this particular relationship. These words describe a reference direction, not people or ownership rights.
3. A foreign-key check asks whether the named envelope exists. It does not automatically count how many cards each envelope contains.
4. Now picture a separate preferences card attached to a customer card. A rule permits at most one preferences card for that customer. Uniqueness supplies the upper limit.
5. Where the comparison breaks: there are no literal strings attaching stored rows. The engine checks values under constraints. Deleting a parent has a defined database response, not whatever happens when someone tears an envelope.
6. Different relationships can need different deletion rules. The lab chooses rejection for deleting referenced parents rather than silently cascading away order history.

9.3.3 PLAIN — a worked example#

1. The two canonical order rows are O-1042 and O-1043. The four canonical lines refer to them in pairs.

2. Try to insert a line for O-MISSING when no such order exists. With the declared foreign key enabled, the destination rejects the line rather than creating a hidden parent order.
3. Try to insert O-1042 line 1 again. The line’s composite primary key rejects that repeated identity. The foreign key alone would not reject it, because O-1042 does exist.
4. Now create the invented customer C-001 and one preference record referring to C-001. The preference record uses customer_id as both its primary key and its foreign key.
5. Inserting a second preference record for C-001 fails the primary-key uniqueness rule. Inserting one for C-MISSING fails the foreign-key rule. A customer with no preference row remains permitted.
6. These three outcomes prove different bounded properties: no duplicate preference identity, no dangling reference and optional absence. They do not prove that every real customer has exactly one preference record.

9.3.4 PLAIN — what is really happening inside#

1. A primary key identifies a parent row. A foreign key declares that a child value must match an allowed parent key, except where an optional NULL is permitted.
2. In our line table, both parts of the line key are required. The order identifier references the order table; the product identifier separately references the product table.
3. Several child rows can use the same parent key unless another constraint forbids it. This is how the order’s two lines can both refer to O-1042.
4. To enforce “at most one preference row per customer”, the referenced customer value in the preference table must also be unique. Making it the primary key is one simple way to do that.
5. A parent can exist with no children under these declarations. Enforcing a minimum child count often requires workflow logic or additional mechanisms, not simply another line drawn in a diagram.
6. In SQLite, foreign-key enforcement must be enabled on each connection that uses it and checked before starting the transaction. Attempting to change the setting in the middle of a transaction does not supply the intended protection. [S59]
7. Our connection helper explicitly enables and verifies it. A test then attempts an invalid reference, so the declaration and the observed behaviour are both checked.

9.3.5 TECHNICAL — the engineer’s version#

1. Cardinality here states a minimum and maximum number of related rows. “Zero or one”, “exactly one” and “zero or many” are different contracts and should be written separately.
2. The line-to-order declaration is conceptually:

order_id TEXT NOT NULL REFERENCES orders(order_id)

3. Under an enforced foreign key to a unique parent key, this gives each line exactly one matching order. It does not require every order to have a line. That reverse minimum is not implied by the declaration. [S02] [S59]
4. An optional one-to-one extension can use:

CREATE TABLE customer_preferences (
    customer_id TEXT NOT NULL PRIMARY KEY
        REFERENCES customers(customer_id) ON DELETE RESTRICT,
    receipt_preference TEXT NOT NULL
        CHECK(receipt_preference IN ('paper', 'none'))
) STRICT;

5. The table permits zero or one preference row per customer, and each preference row must reference a customer. The preference is only an educational delivery preference; it is not a legal consent record or an implemented receipt service.
6. Deletion actions such as RESTRICT, CASCADE and SET NULL express different data changes. They must follow the domain’s retention and lifecycle decisions. The lab uses RESTRICT for the important parent references and does not claim that deleting a row constitutes comprehensive erasure. [S59]
7. A foreign key checks the referenced values, not an arbitrary association inferred from similar names. Matching customer display names is not a relational integrity mechanism.
8. Indexing child references can matter for performance, but an index and a foreign-key constraint have different purposes. One concerns access paths; the other concerns permitted relationships. Detailed index design follows in Part C.

9.3.6 WORDS — remember these#

1. Foreign key: a declared reference to another allowed key — a referential-integrity constraint linking child values to parent values.

2. Parent row: the row being referenced — a record whose key is the target of a particular foreign-key relationship.

3. Child row: a row carrying a reference — a record constrained to refer to an allowed parent under the declared semantics.

4. Cardinality: how many related rows are permitted or required — the minimum and maximum counts on each side of a relationship.

5. One-to-many: one parent can have several children — a relationship whose child references need not be unique across the child table.

6. One-to-one: no more than one related row at each relevant side — a relationship whose optionality must still be specified separately.

9.4.1 PLAIN — in simple words#

1. Sometimes many records on one side can relate to many records on another. A product can have several tags, and the same tag can describe several products.
2. A separate linking table can store one row for each permitted association. In our tag example, one row means one product/tag pairing.
3. Order lines also connect orders and products, but their meaning is richer than a bare pairing. A line has a line number, quantity and agreed price.
4. Crucially, the same product may appear on more than one line of the same order. That was already permitted by the line contract. A unique (order_id, product_id) pair would accidentally forbid it.
5. Relationships must therefore be modelled from the actual row meaning, not by mechanically turning every many-to-many diagram into the same key.
6. Joining through a linking table can multiply result rows. That can be correct for listing associations and wrong for a total that is supposed to count each order line only once.
7. The cure is to understand the new result grain, not to add DISTINCT and hope that the arithmetic repairs itself.

9.4.2 PLAIN — a picture in your head#

1. Imagine a wall listing products and another wall listing descriptive tags. Instead of writing a changing comma-separated tag list on every product card, you keep a card for each product/tag connection.
2. A connection card carries two identifiers. It says that this particular product has this particular tag.
3. For order lines, the connection card carries more information: where it belongs within the order, how many units were agreed and at what price.
4. Where the comparison breaks: a line is not always just one relationship that may occur once. Repeating a product on different identified lines can be legitimate, and collapsing those lines would lose information.
5. The wall picture also hides query multiplication. When one line connects to two tags, listing line/tag pairs produces two rows from that one line. That is a new grain, not a second sale.
6. Always ask what one output row represents after the connection has been followed.

9.4.3 PLAIN — a worked example#

1. Add three synthetic tag associations to the product catalogue. They do not alter any price or quantity.

2. Joining the four canonical order lines to these associations gives six line/tag rows: each notebook line matches two tags, while each pen line matches one.
3. There are still four order lines and two orders. The joined result contains six representations of line/tag associations.
4. The notebook line amounts are 15,100 and 7,550 paise, totalling 22,650. They appear twice after this join, so their displayed contributions sum to 45,300.
5. The pen line amounts total 6,000 paise and appear once. Summing every joined row therefore gives 45,300 + 6,000 = 51,300 paise, not the correct 28,650.
6. The query did not necessarily malfunction. It summed a different collection of rows than the report intended.
7. In a separate order O-REPEAT, two different lines can both identify P-NOTE at an agreed price of 7,550 paise and quantity one. The key (order_id, line_no) preserves both. A key consisting only of order and product would reject the second legitimate line under the existing contract.

9.4.4 PLAIN — what is really happening inside#

1. A join combines rows that satisfy its matching condition. If one row has two matches, the joined result normally contains two corresponding combinations. [S58]
2. The tag-link table has a composite primary key (product_id, tag_id). This prevents the same product/tag association from being declared twice under our chosen set-like association rule.
3. That key does not belong automatically on the line table. Its grain is “one identified agreed line”, not “one distinct order/product pair”.
4. An aggregate performed after a join operates on the multiplied rows. It cannot know that the report author intended to preserve each original line’s contribution only once.
5. When the question is only whether an association exists, a semantically appropriate existence test can avoid multiplying the measured rows. Another option is to aggregate at the correct grain before combining results, with the remaining relationship carefully checked.
6. Which approach is correct depends on the question. “Sales by each tag” may intentionally allocate or repeat attribution, while “total value of these orders” must not silently count a line twice.
7. Reports should state an allocation rule when one fact contributes to several categories. A label such as “sales by category” does not decide whether overlapping categories should sum to the grand total.

9.4.5 TECHNICAL — the engineer’s version#

1. A junction table, also called an associative table, represents relationships as rows. Its key must match the intended association identity rather than follow a naming convention automatically.
2. The executable tag extension uses:

CREATE TABLE product_tags (
    product_id TEXT NOT NULL
        REFERENCES products(product_id) ON DELETE RESTRICT,
    tag_id TEXT NOT NULL
        REFERENCES tags(tag_id) ON DELETE RESTRICT,
    PRIMARY KEY (product_id, tag_id)
) STRICT;

3. Both foreign keys are required, and their pair is unique. Each product can have zero or many tags; each tag can describe zero or many products. No rule here requires either side to participate.
4. The order-line relation instead uses (order_id, line_no). product_id is a non-unique foreign key. A regression test inserts the same product on two distinct lines in an isolated order and confirms that both survive.
5. The tag-join test calculates the intentional fan-out result, 51,300 paise, and compares it with the base-line total, 28,650. Its purpose is to expose the grain error, not present the larger number as a valid sales total.
6. To ask for orders’ lines whose product has the paper tag without changing line grain, one possible form is:

SELECT l.order_id, l.line_no, l.quantity, l.unit_price_minor
FROM order_lines AS l
WHERE EXISTS (
    SELECT 1 FROM product_tags AS pt
    WHERE pt.product_id = l.product_id AND pt.tag_id = 'paper'
)
ORDER BY l.order_id, l.line_no;

7. The existence test is about whether a matching row is present, not how many matches it has. Joins and existence tests are tools for different result semantics; neither is a universal performance winner. [S58]
8. SQL’s join behaviour is documented; the product tags, counts and totals are original synthetic calculations. Their simplicity lets the reader verify the reasoning without relying on an unexplained benchmark or an unseen production dataset.

9.4.6 WORDS — remember these#

1. Many-to-many: several records on either side can relate — an association that needs an explicit representation and participation rules.

2. Junction table: a table of links — an associative relation whose rows represent a defined pairing or richer relationship.

3. Composite key: an identity made from several values together — a unique column combination whose scope is not supplied by any one component alone.

4. Join: combining rows through a stated condition — an operation whose result can contain multiple matches for one source row.

5. Fan-out: one source row producing several matched result rows — multiplicity introduced by a one-to-many match in a query.

6. Existence test: asking whether at least one match is present — a condition that need not reproduce every matching row in the output.

9.5.1 PLAIN — in simple words#

1. A relationship can be optional without the whole row being meaningless. A walk-in order may exist even when the shop has not linked it to a customer profile.
2. Our new teaching annotation makes O-1042 such an order. Its customer_id is NULL, meaning “no linked profile in this model”. The order’s quantities and agreed prices remain known.
3. O-1043 links to the invented customer C-001. That link is a separate recorded association, not proof of a verified real-world identity.
4. A fake customer row called UNKNOWN is not automatically a better model. It can mistakenly group many unrelated people under one identity or suggest that the system has a profile when it does not.
5. NULL does not explain every reason a link is absent. If the distinction between not requested, not supplied and deliberately unlinked matters, the model needs an explicit, carefully defined way to record it.
6. A report must decide whether to keep orders without profiles. An inner join can omit them; a left join can preserve the order side and show absent customer values. [S58]
7. Missing optional relationships should be visible as optionality, not silently converted into lost orders or invented people.

9.5.2 PLAIN — a picture in your head#

1. Imagine an order envelope with an optional customer-reference pocket. Some envelopes contain a profile card number; others do not.
2. An empty reference pocket does not mean that the order envelope is empty or that no human bought anything. It means the chosen link is absent.
3. A report listing every order should keep envelopes with empty reference pockets. A report specifically listing linked customer profiles can deliberately omit them.
4. Where the comparison breaks: SQL NULL has defined comparison semantics. It is not the text “blank”, the number zero or a universal wildcard that matches every customer. [S17] [S24]
5. The picture also does not justify collecting more identifying information merely to fill the pocket. Whether a link is needed belongs to the workflow and data-minimisation decision, not to a desire for visually complete tables.
6. The exercise uses one invented profile because it needs a visible example of optionality, not because every small sale requires a customer identity system.

9.5.3 PLAIN — a worked example#

1. These are the newly introduced relationship annotations.

2. The customer table contains one synthetic row: C-001, display label “Study customer”. There is no UNKNOWN customer row.
3. An inner join on the customer identifier returns only O-1043. This can be correct for “orders with linked profiles”, but is incomplete for “all orders”.
4. A left join starting from orders preserves both order rows. O-1042 has NULL in the joined customer columns because no matching profile was required or found.

SELECT o.order_id, c.customer_id, c.display_name
FROM orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

5. The first output row is O-1042 with absent customer values; the second is O-1043 with C-001 and the study label.
6. Add WHERE c.customer_id IS NOT NULL and the result again contains only O-1043. The left join does not override a later condition that intentionally removes the unmatched row.
7. Counts also differ: counting all preserved order rows gives two, while counting the non-null matched customer identifiers gives one. Neither count should be labelled ambiguously as “customers served” without defining what is being counted.

9.5.4 PLAIN — what is really happening inside#

1. A nullable foreign-key column can represent no reference. For our single-column SQLite reference, NULL does not require a matching parent row. A supplied non-null key still must match. [S59]
2. During an inner join, only matching row combinations are returned. A left join also keeps unmatched rows from its left input and supplies NULL values for the missing right-side contribution. [S58]
3. Conditions applied afterwards operate on that result. A test requiring a right-side value can remove the very unmatched rows the left join preserved.
4. The query’s intended population therefore matters at every stage. “All orders” is a different starting promise from “orders with recorded customer profiles”.
5. The database does not treat C-001’s display name as the order reference. Renaming the display label need not break the relationship because the stable key is separate.
6. Deleting a referenced customer is rejected by this lab’s chosen rule. Automatically removing the linked orders would be a very different lifecycle policy, and this chapter does not implement it.
7. Likewise, setting a link to NULL would remove that association in this table, not prove that every copy or derivative identifier had been erased from the whole system.

9.5.5 TECHNICAL — the engineer’s version#

1. Specify optionality as a domain statement: each order has zero or one linked profile in the teaching model. A profile can be linked from zero or many orders. The foreign-key column is nullable; the parent key is unique and required.
2. The SQLite foreign-key rule permits a NULL child key. Composite references require additional care because partially null key combinations can affect matching semantics; this introductory customer reference is intentionally single-column. [S59]
3. Equality against NULL does not behave like equality against an ordinary scalar. Use the documented IS NULL or IS NOT NULL predicates when testing for absence. [S17] [S24]
4. A left join is not a blanket guarantee that all left rows survive every later operation. Subsequent filtering, grouping and projection can change the result’s population and grain. [S58]
5. COUNT(*) counts result rows, while COUNT(c.customer_id) counts non-null expressions. After this left join the values are two and one respectively. This reuses Chapter 3’s distinction in a relational setting. [S19]
6. The engine can check that C-001 exists. It does not establish that C-001 corresponds to a verified person, that two profiles describe different people or that a user is allowed to view the profile. Those are separate identity and access concerns.
7. Do not fill NULL with a shared fabricated entity solely to simplify joins. A sentinel entity is justified only when it has a real, explicitly defined meaning in the domain rather than hiding an absence.
8. This model intentionally avoids names, phone numbers and addresses as order keys. Its one display label is invented. The schema is not a customer-identity product or a complete privacy-lifecycle implementation.

9.5.6 WORDS — remember these#

1. Optional relationship: a permitted link that need not be present — a reference with a minimum participation count of zero.

2. Nullable foreign key: a reference column that can contain SQL NULL — an optional association under the engine’s stated matching semantics.

3. Inner join: returning matching row combinations — a join that omits rows without a match in the joined relation.

4. Left join: preserving the left input when no right match exists — an outer join that supplies NULL right-side values for unmatched left rows.

5. Sentinel entity: a special record used to stand for a designated case — a modelling choice that must not fabricate identity or conceal unknown meaning.

6. Result population: the records or combinations included in an answer — the scope after joins and filters, which must match the report’s intended question.

9.6.1 PLAIN — in simple words#

1. We now assemble a small destination for the canonical lines. Four core tables are enough for this teaching step: products, customers, orders and order lines.
2. The product table describes catalogue identities. The customer table contains the one invented profile used to teach optionality. The order table identifies orders and their optional profile links. The line table records quantities and agreed prices within orders.
3. Three small extension tables illustrate optional one-to-one preferences and product tags. They are not necessary to compute order totals.
4. The agreed price remains on the line. A later change to a catalogue price must not silently rewrite what an earlier order agreed.
5. The currency is stored once on each order in this destination, because the destination supports only INR orders. The incoming line still carries currency for validation; storing it at the parent is an explicit modelling decision, not an accidental dropped field.
6. The raw input and import report remain separate evidence. Reconstructing a normalised row is not the same as reproducing every original byte, header or formatting choice from the source file.
7. The model is intentionally incomplete. It does not implement payment, tax, permissions, fulfilment, refunds, the Chapter 6 event store or a final order-lifecycle service.

9.6.2 PLAIN — a picture in your head#

1. Picture a small records desk with an order envelope, its line cards, a catalogue index and an optional pointer to a customer profile.
2. The order envelope says which order it is and which currency applies. Each line card says which product, how many units and the agreed price for that line.
3. The catalogue index can change its current display price without reaching into every old envelope and changing history.
4. Where the comparison breaks: normalisation is not simply “put each fact in one physical place”. Repeated values can be meaningful snapshots, and retained source evidence may intentionally duplicate representations for a different purpose.
5. The decision is about which fact is authoritative for which question. “Current catalogue price” and “agreed price on this old line” are different facts even when their numeric values happen to match.
6. This desk is a model of data responsibilities, not a finished shop. The actual business still needs rules governing who can create, confirm, change and retire records.

9.6.3 PLAIN — a worked example#

1. Begin with the two product identities P-NOTE and P-PEN, the invented customer C-001, and the two order identities O-1042 and O-1043. Do not create order lines yet.
2. Parse the untidy CSV from Chapter 7. The report yields four candidates, one duplicate and three rejected new rows. The operator-facing explanation still states that the file was only partially acceptable.
3. For this demonstration, explicitly publish the four candidates as a group. The destination checks product and order references, applies its additional numeric bounds and inserts all four lines within one transaction.
4. Query the destination: O-1042 totals 17,100 paise; O-1043 totals 11,550. The four lines contain six units. These outputs match the earlier hand calculation.
5. Change the current catalogue notebook price from 7,550 to 8,250 paise in a separate catalogue-update exercise. The two historical order totals remain unchanged because they use agreed line prices.
6. Try a separate publication containing one valid line followed by a line referencing P-MISSING. The second line fails its foreign-key check, and the first insertion is rolled back. The destination receives no partial set from that failed publication.
7. Try to publish the same four canonical lines again. Their existing primary keys cause rejection; the lab does not treat that as a new sale or advertise a production retry protocol.
8. These tests connect parsing, relationships and transaction boundaries. None requires invented customer data, a hosted service or a real shop database.

Figure 9.1 — Core shop relationships: each line names one existing order and one existing product; an order optionally names one customer. Parent rows may have zero children unless another rule establishes a minimum.

9.6.4 PLAIN — what is really happening inside#

1. The database is created in a fresh in-memory connection for the main relational exercise. The connection helper enables foreign keys before any transaction and checks that enforcement is active.
2. Core tables are created with SQLite’s STRICT option. Named identifiers are explicitly required, line identity is composite, and quantity and price bounds are declared.
3. The fixture loader inserts known parent rows. The publication function does not silently invent missing products or orders merely to make a foreign-key failure disappear.
4. Before binding, the function checks the source objects and the destination bounds. It starts one explicit transaction for the selected candidate set, inserts each line and commits only after all succeed.
5. A failure rolls back that transaction. The caller receives a failure rather than a count of how many earlier inserts temporarily succeeded.
6. Queries derive totals from the agreed line values. Current product labels or catalogue prices serve different purposes and do not replace those historical values.
7. The extension tables are populated only with the labelled synthetic preferences and tag associations. They demonstrate relationship semantics without being presented as an integrated marketing, receipt or analytics product.
8. The lab closes every connection and removes its own temporary test directory. It neither discovers nor modifies the user’s existing files or databases.

9.6.5 TECHNICAL — the engineer’s version#

1. The core line table used by the lab is:

CREATE TABLE order_lines (
    order_id TEXT NOT NULL
        REFERENCES orders(order_id) ON DELETE RESTRICT,
    line_no INTEGER NOT NULL CHECK(line_no > 0),
    product_id TEXT NOT NULL
        REFERENCES products(product_id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL
        CHECK(quantity BETWEEN 1 AND 10000),
    unit_price_minor INTEGER NOT NULL
        CHECK(unit_price_minor BETWEEN 0 AND 100000000),
    PRIMARY KEY (order_id, line_no)
) STRICT;

2. The order table has a required, unique order key, an INR-only currency constraint and a nullable customer reference. The product and customer tables have required unique identifiers. SQL file contents and a full fixture are included in data_bridge_lab.py rather than leaving the reader with disconnected pseudocode.
3. The incoming record_type and schema_version describe the message contract. They are not repeated on every line row in this destination. The adapter version, source bytes and import report are separate concerns; the lab does not claim a persistent audit trail simply because those values exist in memory.
4. The publication function receives an explicitly selected candidate set. Its all-or-nothing guarantee is for that set in one database transaction, not for all eight original source rows, which already have distinct rejection and duplicate outcomes.
5. Important incompleteness: the core schema permits an order with no lines. It also does not connect the separate order-state machine from Chapter 6 to a confirmed-order minimum-line rule. Building that integrated workflow requires additional application and database design.
6. Relationship checks, uniqueness checks, source typing and destination bounds are all exercised. A tag fan-out example deliberately demonstrates a wrong aggregate, and an optional-customer query deliberately contrasts inner and left joins.
7. The correct fixture total is calculated independently in Python and in SQL. Agreement provides evidence for these fixture paths, not proof that every future query is correct. Tests must include adversarial and alternative-grain inputs rather than only reproducing one expected number.
8. The schema is SQLite-specific teaching code. A PostgreSQL implementation, durable import ledger, authorization boundary, migration chain and production recovery design are not included or implied.
9. The next chapter, Designing a Schema That Matches the Work, develops the workflow questions this small model deliberately leaves open. A diagram and a successful insert are the beginning of a model review, not its conclusion.

9.6.6 WORDS — remember these#

1. Agreed line price: the price attached to a particular historical agreement — a transaction-specific fact distinct from a product’s current catalogue price.

2. Catalogue price: the current listed price in the product model — a mutable reference value that must not silently replace historical agreement values.

3. Referential integrity: references remaining valid under declared rules — the property enforced by active foreign-key constraints within their semantics.

4. Publication unit: the selected set of records accepted together — the transaction scope for this destination, distinct from an entire source file.

5. Schema boundary: what the declared database model does and does not enforce — the limit beyond which application workflow or other mechanisms remain necessary.

6. Regression test: a repeatable check preserving an intended behaviour — evidence that a known property still holds for the tested inputs after a change.