Schemas and Constraints

Project Checkpoint, Step 1: Designing Card Reviews

---

The chapters so far have focused on schema work in the abstract: planning, DDL, constraints, verification, writes, migrations, and safe evolution. In this chapter, you take a small new feature for the study tracker, work it through subject analysis, extend the ER diagram, write the migration, and verify the result.

The Feature: Card Reviews

Up to this point, the project has supported organizing decks of cards and tagging them. What it cannot do is record how well the user actually remembered each card. Without that, the project can show what is in a deck, but not what the user knows.

The new feature is a small step toward closing that gap. After the user reads a card’s answer, the application should let them rate, on a scale of 1 to 5, how well they remembered the answer. Each rating is stored with the moment it happened. Over time, the project accumulates a record of how the user has performed on each card, which later chapters and parts can use for summary views (“how often has this card been reviewed?”, “what is the average rating per deck?”).

Here is the case description in the form we used in the last part:

The study tracker should let the user record how well they remembered a card after seeing the answer. Each review is a 1-to-5 rating where 1 means “did not remember at all” and 5 means “remembered easily.” A user can review the same card many times, and each review is recorded with its timestamp. The application should be able to show how many reviews each card has received, and the deck listing should show review activity at a glance.

A short paragraph, easy to read, but with several quiet design decisions. The rest of this chapter unpacks them.

Subject Analysis on a Feature Addition

In the part on Database Design and Modeling, we used subject analysis for green-field domains: starting from a case description with no existing structure. Here the situation is different. The project already has decks, cards, tags, and card_tags. Subject analysis on a feature addition is not about discovering everything from scratch; it is about discovering what is new and how it connects to what already exists.

The three-pass reading method still applies, but each pass focuses on the additions.

First pass: candidate entities. Reading the case description for nouns that might be new:

• review (the act of rating a card after seeing it) — this is the central new candidate,
• rating (the 1-to-5 value) — this is more naturally an attribute than its own entity,
• user (mentioned in the case) — the project does not yet have a users table, and this chapter does not add one (more on that below).

So the candidate entity list adds exactly one item: review.

Second pass: actions, connections, and rules.

• a review belongs to a card — one-to-many relationship from card to review,
• the user makes a review at a specific moment — the review carries a timestamp,
• the rating is 1-to-5 — a domain rule that should be enforced,
• a single card can have many reviews over time — the project should keep the history, not just the latest,
• an implicit rule is that a review never exists without a card — the card_id is required.

Third pass: open questions.

• Should the project keep all review history forever, or eventually drop old reviews? (For now: keep all.)
• Is “1 to 5” the right scale, or would “easy/medium/hard” work better? (For now: 1 to 5; numeric ratings make later summary queries simpler.)
• What happens to a card’s reviews when the card is deleted? (Cascade is the natural choice — a review of a deleted card has no meaning.)
• Should reviews be attributed to specific users? (See the next section.)

That last question is the most important one, and it deserves direct attention.

A Limitation: No Users

A real review feature would track who made each review. Two students using the same project should see their own ratings, not each other’s. This requires a users table and a user_id foreign key on reviews.

The study tracker project does not have a users table. The earlier parts of the course built decks, cards, and tags as a single-user-implicit application: the project assumes one person is using it, and the schema simply does not record identity.

This chapter accepts that limitation. The reviews table will have card_id, rating, and reviewed_at, but no user_id. Reviews are unattributed.

Loading Exercise...

This is honest about what the project is and what it is not. Adding a users table would mean designing authentication, sessions, and permission checks, which are substantial features in their own right.

The Web Software Development course (CS-C3170) teaches you more about web development, including how to work with users and authentication.

For the project’s purposes here, single-user-implicit is enough. The new feature works for a single user studying their own decks; what it cannot do is distinguish two users reviewing the same card. We record this as a known limitation and move on.

The Compact Subject-Analysis Note

Pulling the three passes together:

• candidate entities (new): review
• candidate relationship: review belongs to card (one-to-many from card to review)
• business rules:
  • a rating is between 1 and 5 inclusive,
  • every review belongs to exactly one card,
  • a card may have many reviews over time,
  • when a card is deleted, its reviews go with it
• open questions (resolved or recorded):
  • keep review history forever (for now: yes),
  • rating scale 1-to-5 numeric (for now: yes),
  • cascade on card delete (for now: yes),
  • attribution to specific users (for now: no, accepted as limitation)
• likely application queries:
  • how many reviews has each card received?
  • what is the most recent review for a given card?
  • what is the average rating per deck?
  • which cards have not been reviewed yet?

Notice that the note is short. It is short because the feature is small, and it is small because subject analysis on an addition only has to cover the new pieces. The existing decks, cards, and tags are not re-analyzed.

Loading Exercise...

Extending the ER Diagram

The existing project has, conceptually, decks containing cards, with tags attached to cards through a linking table. Adding reviews produces this extension:

Figure 1 — The study tracker ER diagram with the new REVIEWS entity. Only the new entity is shown with attributes; the existing entities are unchanged.

The diagram shows only the structural change. The existing entities (DECKS, CARDS, TAGS, CARD_TAGS) are still there, unchanged. The new entity (REVIEWS) has its attributes shown so the migration can be read off the diagram directly.

Read aloud:

• a card receives many reviews, and each review belongs to exactly one card.

That is the whole change at the conceptual level. One new entity, one new relationship, no modifications to anything that already exists.

What Kind of Change This Is

The chapter on Evolving a Schema Safely contrasted additive changes (safe) with changes that need more care — removing or renaming a column, tightening, splitting. The reviews feature is purely additive. It introduces a new table, a new foreign key on that new table, and nothing else. The existing tables are untouched.

That makes the migration straightforward. There is no backfill to plan, no application code that depends on the old shape of an existing table, and no transition strategy to choose between. The change goes into a single new migration file and is applied like any other.

This also means the rule from the chapter Migrations and Seed Data — never edit a migration file that has already been applied — applies cleanly. The new migration is 006_reviews.sql, the next number after the five migrations that we have added to the project so far. The earlier files (001_schema.sql through 005_card_tags.sql) stay exactly as they are.

Writing the Migration

Here is the migration file:

-- 006_reviews.sql

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    card_id INTEGER NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    reviewed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Walking through it:

The CREATE TABLE matches the ER diagram:

• id SERIAL PRIMARY KEY is a surrogate key.
• card_id INTEGER NOT NULL REFERENCES cards(id) ON DELETE CASCADE is the foreign key that points to the card being reviewed. It uses the cascade choice that the subject analysis settled on.
• rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5) enforces the domain rule directly in the schema.
• reviewed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP is an audit-style timestamp.

Loading Exercise...

Seeding the Reviews Table

Let’s next add some seed data for reviews. This is not strictly necessary, but it makes the next chapter’s work on review-related queries more interesting. With no reviews, the queries will all return zero or null; with some reviews, the queries have more varied results that better demonstrate their behavior.

The seed file is 007_reviews_seed.sql:

-- 007_reviews_seed.sql

INSERT INTO reviews (card_id, rating, reviewed_at)
SELECT
    c.id,
    1 + (c.id * 7) % 5,
    CURRENT_TIMESTAMP - (c.id || ' hours')::interval
FROM cards AS c;

INSERT INTO reviews (card_id, rating, reviewed_at)
SELECT
    c.id,
    1 + (c.id * 3) % 5,
    CURRENT_TIMESTAMP - ((c.id + 24) || ' hours')::interval
FROM cards AS c
WHERE c.id % 2 = 0;

The two INSERT statements seed reviews against the cards that already exist in the project. They use SELECT from cards so the seed adapts to whatever cards happen to be in the project, rather than assuming specific card identifiers. The modulo arithmetic produces deterministic but varied ratings, so the seeded data has a usable spread without being random.

A small detail worth noticing: the seed deliberately gives some cards more than one review (the second INSERT adds extra reviews for even-numbered cards). This mimics what real review history looks like and makes the next chapter’s “how many reviews per card” feature visibly produce different numbers per card.

There’s quite a bit of trickery here — no need to understand the details of how the ratings are generated or how the timestamps are calculated. The important thing is that the seed produces some reviews with varied ratings and timestamps, so the next chapter’s queries have interesting data to work with.

Loading Exercise...

Verifying the Migration

The chapter Verifying the Schema introduced a verification methodology: run the migration, then exercise the new schema with both valid and invalid inserts to confirm the constraints fire as intended. The same applies here.

After running python3 -m app.migrate (or restarting the project with docker compose up --build), open psql and run a few checks.

First, confirm the table and seed data exist:

SELECT * FROM reviews LIMIT 1;
-- expected: at least one review per card, plus extras for cards with even ids

SELECT c.id, r.id
FROM cards AS c
LEFT JOIN reviews AS r ON r.card_id = c.id
ORDER BY c.id;
-- expected: at least 1 per card, with even-id cards showing 2

Next, confirm the constraints reject invalid data:

INSERT INTO reviews (card_id, rating) VALUES (1, 0);
-- expected: ERROR: new row for relation "reviews" violates check constraint

INSERT INTO reviews (card_id, rating) VALUES (1, 6);
-- expected: ERROR: new row for relation "reviews" violates check constraint

INSERT INTO reviews (card_id, rating) VALUES (99999, 3);
-- expected: ERROR: insert or update on table "reviews" violates foreign key constraint

Finally, confirm a valid insert works and the timestamp default fills in:

INSERT INTO reviews (card_id, rating) VALUES (1, 4);
SELECT id, card_id, rating, reviewed_at FROM reviews ORDER BY id DESC LIMIT 1;
-- expected: one row with rating = 4 and reviewed_at set to roughly now

If all of these behave as expected, the schema works. If any do not, the discrepancy is something to fix before moving onward (unless it’s about the id’s not matching). Schema bugs are much cheaper to fix here than after the application code has been written against them.

Check Your Understanding

1. Why is the reviews feature an additive change, and what does that simplify?
2. Why does the migration use SELECT from cards to seed reviews instead of hard-coded card identifiers?
3. Why does the chapter explicitly record “no users table” as a known limitation rather than leaving it unmentioned?

Submission

The deliverable for this chapter is the project zip built on top of the earlier design-package checkpoint, now extended with the updated design documents plus migrations/006_reviews.sql and migrations/007_reviews_seed.sql. Before submitting, confirm that the project still reproduces from a clean state and that the verification queries above behave as expected.

Loading Exercise...