Schemas and Constraints

Practicing Schema Implementation

---

In the previous chapters, we walked through the mapping and DDL steps on the course-platform example. That is a good starting point.

This chapter gives you two design packages to implement. Both domains should feel familiar because they were the practice cases in the previous part: the recipe app and the library.

How to Use This Chapter

For each case below:

1. revisit the subject-analysis notes from the previous part,
2. produce a table plan in the compact form shown in the chapter Planning the Schema from the Design Package,
3. write the DDL for every table, with appropriate data types, primary keys, foreign keys, referential actions, and at least one CHECK or UNIQUE constraint beyond the keys,
4. then read the commented worked solution and compare it with your own.

The worked solutions below show specific choices. Yours may differ in places, and that can still be correct. What matters is that every choice in your DDL is defensible against the notes from the design and the domain rules, and that the result is internally consistent.

Case 1: The Recipe App

Here is the recipe-app ER diagram from the last part in compact form:

• users owns many recipes,
• recipes uses many ingredients through recipe_ingredients, with a quantity on the relationship,
• users favorites many recipes through favorites.

Try the DDL yourself before reading on. Pay particular attention to:

• what data type to use for quantity,
• where to store the unit (grams, cups, pieces) — on ingredients or on the connection,
• what the primary key of recipe_ingredients should be,
• what referential action is appropriate when a recipe is deleted,
• what referential action is appropriate when an ingredient is deleted.

Loading Exercise...

A Commented Worked DDL

Here is one reasonable DDL for the recipe app, with commentary on the non-obvious choices.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE recipes (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ingredients (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE recipe_ingredients (
    recipe_id INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
    ingredient_id INTEGER NOT NULL REFERENCES ingredients(id) ON DELETE RESTRICT,
    quantity NUMERIC NOT NULL CHECK (quantity > 0),
    unit TEXT NOT NULL,
    PRIMARY KEY (recipe_id, ingredient_id)
);

CREATE TABLE favorites (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    recipe_id INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, recipe_id)
);

Commentary on the choices:

users.name and users.email UNIQUE. Neither column was named explicitly in the note. Both are added here because the application will need them: a name to display, and an email as a stable contact and login value. The UNIQUE on email follows the same pattern as the course-platform: surrogate id for relationships, natural value as a separate UNIQUE attribute.

ON DELETE CASCADE on recipes.user_id. When a user is deleted, their recipes go with them. This matches the ownership relationship: a recipe only exists in the context of its owning user. If the domain required recipes to survive their owners (for example, because recipes are shared community content), this choice would be different.

recipes.created_at. An audit-style timestamp is cheap to add now and hard to reconstruct later. The note did not require it, but it is the kind of small extension that a working application will almost always want.

quantity NUMERIC with CHECK (quantity > 0). Quantity could plausibly be INTEGER (“2 eggs”), but many real ingredients have fractional quantities (“0.5 cups of flour”). NUMERIC handles both. The CHECK prevents zero or negative quantities, which would not make sense in a recipe.

unit TEXT NOT NULL on recipe_ingredients. The unit (“cups”, “grams”, “tablespoons”) belongs to the specific recipe-ingredient pair. The same ingredient can appear in different units in different recipes. The note left “where do units belong?” as an open question; this DDL answers it by placing the unit on the connection. If your note put the unit on ingredients instead, your DDL will look different, and the justification in the note matters more than matching this reference exactly.

ON DELETE CASCADE on recipe_ingredients.recipe_id. When a recipe is deleted, its ingredient rows should also be removed. Keeping them would leave orphaned recipe-ingredient rows pointing to nothing.

ON DELETE RESTRICT on recipe_ingredients.ingredient_id. When someone tries to delete an ingredient, the delete is blocked if any recipe still uses it. This is the right choice because an ingredient’s removal could silently damage several unrelated recipes. The safer path is to make removing an ingredient a deliberate act that first requires the recipes to stop using it.

favorites with composite primary key. The table has no attributes other than the two foreign keys, so the pair itself is the identity. No surrogate id is needed, because nothing else in the schema refers to a favorite row.

Both ON DELETE CASCADE on favorites. Favorites depend fully on both the user and the recipe. If either one is deleted, the favorite row should disappear quietly.

No recipe_steps table. The note flagged preparation steps as a borderline case: an entity with its own identity, or an ordered list attached to the recipe. This DDL defers the question by leaving steps out altogether. A working application would either store the steps as text on recipes (a single instructions column) or add a recipe_steps(id, recipe_id, position, content) table when steps need to be reordered or rendered individually. Both choices are defensible; the note should be updated to reflect whichever is taken.

One smaller choice worth discussing is that recipes.description is nullable while recipes.title is not. The domain suggests every recipe has a title (the recipe has to be called something), but a description is optional. If you chose NOT NULL on description, compare that against the domain rules and decide which matches better.

Loading Exercise...

Case 2: The Library

Here is the library ER diagram from the previous part in compact form:

• books has many copies,
• copies has many loans over time,
• members takes many loans,
• a loan has a borrowed_at and possibly a returned_at.

The library case is particularly interesting for referential actions, because the correct answer depends on what the system considers worth remembering. Before reading on, try the DDL yourself. Pay particular attention to:

• whether a loan row should cascade-delete when a member is removed,
• whether a copy should cascade-delete when a book is removed from the catalogue,
• what CHECK constraint, if any, is appropriate on loans,
• what type to use for returned_at.

Loading Exercise...

A Commented Worked DDL

Here is one reasonable DDL for the library.

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    isbn TEXT UNIQUE
);

CREATE TABLE copies (
    id SERIAL PRIMARY KEY,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT
);

CREATE TABLE members (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE loans (
    id SERIAL PRIMARY KEY,
    copy_id INTEGER NOT NULL REFERENCES copies(id) ON DELETE RESTRICT,
    member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE RESTRICT,
    borrowed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    returned_at TIMESTAMP,
    CHECK (returned_at IS NULL OR returned_at >= borrowed_at)
);

Commentary on the choices:

books.title NOT NULL and books.isbn UNIQUE without NOT NULL. Every book in the catalogue needs a title, but not every book has an ISBN (older books, translations, in-house editions). The column is unique when present but may be missing for some rows. The UNIQUE constraint in PostgreSQL allows multiple NULL values, which matches this need.

members.name, members.email, and members.joined_at. None of these were in the note explicitly. name and email are added because the library will need to address members and contact them. joined_at is a small audit-style addition that is cheap now and hard to reconstruct later. The note should be updated to include them.

ON DELETE RESTRICT on copies.book_id. When someone tries to delete a book from the catalogue, the delete is blocked if any copy still exists. The cataloguer has to remove the copies first. This is the right choice because deleting a book should be an administrative act, not a quiet cascade that silently also removes every copy.

ON DELETE RESTRICT on loans.copy_id. Loans are historical records. If a copy is removed (damaged, lost, discarded), the loan history should not silently disappear. In a real library, the copy might be marked as withdrawn rather than deleted, and the loan history stays. The RESTRICT choice forces the library to decide explicitly what to do about the loans before the copy can be removed.

ON DELETE RESTRICT on loans.member_id. Same reasoning. The member’s loan history is meaningful even after the member leaves. In practice, many libraries anonymize old member records after a retention period rather than deleting them. RESTRICT means that deleting a member requires explicit handling of their loans first.

returned_at TIMESTAMP without NOT NULL. This is one of the most important design details in the whole schema. A loan exists before the copy is returned, so returned_at must be nullable, with NULL meaning “not yet returned.” Later queries will filter on returned_at IS NULL to find active loans.

CHECK (returned_at IS NULL OR returned_at >= borrowed_at). A return cannot be earlier than the borrow. The IS NULL OR ... guard allows the not-yet-returned case.

Notice what this DDL does not have. It does not include an is_overdue flag on loans. The subject-analysis note flagged this as an open question: should “overdue” be stored or computed? The worked DDL here answers it by computing overdue status from the dates in queries, rather than storing a flag that would need to be kept up to date as time passes. A query can ask:

SELECT id, copy_id, member_id, borrowed_at
FROM loans
WHERE returned_at IS NULL
  AND borrowed_at < CURRENT_TIMESTAMP - INTERVAL '28 days';

to find overdue loans at any moment, without needing an updater process.

If your DDL chose to store the flag, check whether the domain rules support that choice. Both are defensible, but they imply different operational stories.

Loading Exercise...

A Common Pitfall: Copying Constraints Across Domains

The recipe app and the library tell very different stories about deletion.

The recipe app uses CASCADE in most places because ownership is the dominant relationship: recipes belong to users, ingredient rows belong to recipes, favorites belong to both the user and the recipe. When the owner goes away, the owned rows go with it.

The library uses RESTRICT almost everywhere because history is the dominant concern: loans should not quietly disappear because a book was delisted or a member left. The schema forces administrative decisions rather than making them silently on delete.

Neither pattern is a universal rule, and applying one domain’s pattern to the other usually breaks something. Using the library’s RESTRICT pattern on recipes.user_id would mean that deleting a user account would be blocked for as long as the user owns any recipe. That is an awkward user-facing behavior: the account-deletion page would have to list every recipe the user owns and demand that each be removed first. In the other direction, using the recipe app’s CASCADE pattern on loans.member_id would mean that deleting a member quietly removes every loan record that ever involved them. A library that needs accurate borrowing history would have just lost it.

The safer habit is to make the choice for each foreign key based on the domain. A one-sentence justification per foreign key — “deleted with the parent,” “must be removed manually first,” “becomes null on parent removal” — is enough to force the thought.

Loading Exercise...

Check Your Understanding

1. Why does the recipe app use CASCADE on most foreign keys while the library uses RESTRICT?
2. Why is returned_at nullable in the library schema, and what does NULL mean there?
3. Why does the worked DDL for both cases extend the their subject-analysis notes rather than match the notes column for column?