Database Design and Modeling

Project Checkpoint

---

This is the Part 5 project milestone. Earlier parts built the study tracker forward, from a walking skeleton in Part 1 to richer CRUD pages in Parts 3 and 4. The schema, the column names, the route shapes — all of those were specified by the chapter instructions. You did not design the project; you implemented what the chapters told you to implement.

This checkpoint asks you to take the analyst’s seat. Read the project the chapters built as if you had inherited it from someone else, and produce the design package the project would have had if it had been designed first. The migrations describe the entities, the routes describe the actions, the templates describe what users see — reading those carefully is the work of this checkpoint.

There are two distinct activities mixed together here, and they are worth separating in your head.

Archaeology. What was the original designer trying to accomplish? Why are these the entities, these the relationships, these the constraints? The code is the evidence; the design is what you reconstruct from it.

Design proper. A teaching project takes shortcuts. There is no users table, even though the application clearly assumes someone is studying. There are no audit timestamps on most tables. Constraints are looser than a real product would tolerate. Where would a real version need to go further, and why?

The most common failure mode of this checkpoint is paraphrase. “The decks table has an id and a name, so the design has a Deck entity with an id and a name.” That is translation, not design. Every sentence in your rationale should say something a stranger reading the migrations would not already know — either by reconstructing intent (archaeology) or by naming what a real version would still need (design proper).

The work has four steps. Steps 1, 2, and 3 each produce one document. Step 4 reviews the three together and submits them as one package. There is only one hand-in, at the end.

Step 1: Subject-Analysis Note (design/subject-analysis.md)

Apply the three-pass reading method from the chapter Reading a Case Description, but with the project itself as the case description.

First pass: candidate entities and roles. Open migrations/. Each CREATE TABLE points to a candidate entity; each REFERENCES points to a relationship.

Second pass: actions, connections, and rules. Open app/. Each route handler points to an action a user can take. Each INSERT, UPDATE, or DELETE points to a rule about who is allowed to change what. Each JOIN points to a relationship the application actually uses.

Third pass: open questions. This pass matters most. The teaching project deliberately took shortcuts; a real version would not. Look for what is missing or implied:

• columns that are absent but would be needed in a real version (a created_at on tags? a user_id anywhere?),
• constraints that are not enforced but probably should be (should two decks share a name? what stops it now?),
• features the routes hint at but do not implement (deck-edit exists for cards but not decks?),
• entities the implementation conflates (are tags really shared across all users, or did the project just not get around to user-scoping them?).

These are the questions the implementation either deferred or quietly assumed. They are the most useful part of the note, because they are where the gap between a teaching project and a real product is most visible.

The note should include candidate entities, candidate relationships, any candidate events, at least two business rules read from the route logic, at least three open questions, and at least three likely application queries (including at least one the project does not yet implement).

Write the note to design/subject-analysis.md (create a design/ folder at the root of the project). The note should be a clear, concise summary of the project as a domain, not a copy of the code.

Step 2: ER Diagram (design/er-diagram.md)

Turn the note into an ER diagram using the notation from the chapter Entity-Relationship Diagrams. Include all main entities, the relationships between them with cardinality marked, any many-to-many relationship as its own visible structure, and any relationship attribute the domain has.

Two judgment calls deserve thought.

Where the diagram should match the project, and where it should not. The diagram is a design, not an audit. If your subject analysis surfaced an open question, the diagram can show the version a real product would need. Annotate any element that diverges from the current schema, so a reader knows it is a deliberate proposal rather than a transcription error.

Attribute detail. Lean toward including the main attributes on each entity.

After drawing, read the diagram aloud against the note. If a relationship from the note has no line, or if a line is hard to read aloud in one sentence, revise.

Write the diagram to design/er-diagram.md.

Step 3: Design Rationale (design/design-rationale.md)

The rationale is the document where the analyst’s work is most visible, because it cannot be read directly off the code. The chapter on Documenting a Design walked through a worked rationale for the course-platform domain; yours follows the same shape, with one extra emphasis: every decision should be clearly attributed either to the existing code (as a reconstruction of likely intent) or to your own analysis (as a proposal for a real version).

Include:

• your choice of identifiers for the main entities, with one sentence of justification for each, noting where the project’s existing identifier is one a real version should keep and where it should change,
• one many-to-many relationship and the reason it is modeled as its own structure (or, if the project has none, what one would look like if added),
• one privacy or data-minimization decision a real version would need (a field that should not be stored, an access rule the system would enforce — the project itself probably has no such rule yet),
• at least two decisions a real version would need to make that the teaching project has silently skipped (e.g., introducing a users table, scoping tags per user, adding audit timestamps),
• at least one genuinely open question that has more than one defensible answer.

The current study tracker has decks, cards, tags, and card_tags. Any of these are open for reconsideration in a real version: whether card_tags needs relationship attributes, whether tags should be user-scoped, whether study sessions should be an entity, or whether the project should have a users table at all.

Two pages of clear prose is enough. A stranger reading the rationale and the diagram together should understand both what the existing project does and what a real version of it would still need to add.

Step 4: Review and Submit

You now should have three new files in the project. Read them together as one package and revise for consistency before submitting:

• every entity in the note appears in the diagram, and every diagram entity with a non-obvious decision is mentioned in the rationale,
• the open questions in the note are either resolved in the rationale or explicitly left open,
• every likely application query from the note can be answered from the diagram (use the method from chapter Testing a Design Against Its Questions),
• no anti-patterns from the chapter Design Patterns and Anti-Patterns have quietly appeared (comma-separated lists, repeating groups, god tables),
• every place where the design diverges from the existing implementation is clearly marked as a proposal, not a description — a reader should always be able to tell which sentences describe the project as it is and which describe what a real version would need.

Submit the three documents together with the project.

Loading Exercise...