Database Design and Modeling

Documenting a Design

---

By this point in this part, you have practiced each of the design steps separately. This chapter brings them together. A complete design package usually has three parts: a subject-analysis note, an ER diagram, and a design rationale. Each part has its own audience and its own purpose.

The Three Parts of a Design Package

A compact design package usually contains:

1. a subject-analysis note that records what was found in the domain, including open questions,
2. an ER diagram that shows the conclusions from that analysis as a visual model,
3. a design rationale that explains the non-obvious choices in the model.

Each document has a different audience. The subject-analysis note is mostly for the designer: it is the reading notes that the design rests on. The ER diagram is for anyone who needs a quick map of the domain. The design rationale is for reviewers and future maintainers who need to know not only what the design is but also why it is that way.

Loading Exercise...

A Worked Design Package: Course Platform

The rest of this chapter walks through one complete design package for the course-platform domain, so that you have a reference example to compare your own work against.

Part 1: Subject-Analysis Note

Here is a subject-analysis note for the course-platform domain, expanded from the version in the chapter Reading a Case Description. Later chapters of this part — entities and attributes, relationships, keys, and the design rationale below — all suggested small refinements and additions, and those have been folded in here.

Purpose of the system. A course platform where a department runs courses that are offered in specific terms. Students enroll in instances, submit work for exercises, and receive grades from teachers.

Main user actions. A student enrolls in a course instance, submits work for an exercise, and views submission history. A teacher creates exercises, grades submissions, and reviews course activity.

Candidate entities. user, course, course instance, enrollment, exercise, submission.

Candidate relationships. A course is offered as many course instances. A course instance contains many exercises. A user makes many submissions. An exercise receives many submissions. A user enrolls in many course instances, and a course instance has many enrolled users.

Candidate events. Submission (has a timestamp). Grading (sets a score and updates a status).

Business rules.

• One course may be offered many times.
• One exercise belongs to one course instance.
• A student may submit more than once per exercise.
• Only the most recent submission counts for grading.
• Teachers grade submissions; students do not.

Open questions.

• Can the grading score be updated after it has been set once?
• Do we keep the history of earlier submissions, or only the most recent?
• Can a student enroll after a course instance has started?
• Do we record which teacher graded which submission?

Likely application queries.

• Exercises in a given course instance.
• Students enrolled in a given course instance.
• Submissions that still need grading.
• Most recent submission per student per exercise.
• Students enrolled in a course instance but who have submitted nothing.
• Average score for a given exercise.

Notice the overall shape. The note is categorized, the open questions are explicit, and the likely queries are concrete enough to test the later design against. It is not long, and it is not polished, but it is complete.

Part 2: ER Diagram

Here is the ER diagram for the same design, matching the note above.

Figure 1 — A complete ER diagram for the course-platform domain, with the main attributes included on each entity.

This diagram goes a little further than the lightweight diagrams from the chapter Entity-Relationship Diagrams because it also shows the main attributes on each entity. That is a reasonable trade-off for a final design package: the extra detail is worth the slight loss in visual simplicity, because the diagram now carries enough information to guide database design and modeling.

Notice that some decisions from the subject-analysis note have now become concrete:

• submission.score exists (supports the “average score” query),
• submission.status exists (supports the “still needs grading” query),
• users.role exists (to distinguish students and teachers without splitting the entity),
• enrollments has a composite key and includes joined_at as a relationship attribute.

Each of these decisions will be justified in the design rationale.

Loading Exercise...

Part 3: Design Rationale

Here is a design rationale for the same design. The rationale should explain the non-obvious decisions, not repeat what the diagram already shows.

Identifiers. Each main entity uses a surrogate id as its primary key. The meaningful natural values — courses.code, users.email — are kept as separate attributes and will be enforced as UNIQUE in the schema. This keeps relationships stable even if a course code or email changes later. enrollments uses the composite key (user_id, course_instance_id) because what uniquely identifies an enrollment is the pair; no surrogate id is needed for it.

Teachers and students in one table. The design keeps teachers and students in the same users table with a role attribute, rather than splitting them into separate entities. This reflects the subject-analysis finding that teachers and students have the same shape of core data (name, email). If the system later needs teacher-specific attributes that do not apply to students, a separate teacher_profile entity can be added alongside without reworking the users table.

Enrollment as its own structure. Enrollment is modeled as its own entity, with a composite key, rather than as a simple column on one side. This supports both the many-to-many shape and the joined_at attribute, which depends on the specific pair of user and course instance.

Submissions as events, not state. A submission is modeled as an event with its own row, rather than as a current-state attribute on the enrollment. This supports the domain rule that a student may submit more than once per exercise. The “only the most recent counts for grading” rule is handled in the application or through a query, not by throwing away earlier rows. This makes the submission history available for later features such as review pages or plagiarism checks.

Privacy note. The design does not store demographic information such as date of birth or home address. The course-platform domain has no clear use for these fields. If the system later needs such information, a targeted extension (for example, a student_profile table with a retention policy) will be added.

Open question retained. The design does not yet decide whether to record which teacher graded which submission. If the “who graded this?” question becomes important, a graded_by_user_id foreign key can be added to submissions. No data would have to be migrated to support that change, because the column would be nullable for existing rows.

Notice the overall shape. The rationale is focused on decisions that could reasonably have been made differently. It does not repeat what the diagram already says. It connects back to the subject-analysis note and to earlier parts of the course — the data-minimization argument from the chapter on Data Ethics and Privacy in Part 1 (not storing demographic information), and the relationship-attribute pattern from the chapter Relationships, Cardinality, and Optionality (many-to-many shape with joined_at attribute). It also acknowledges one open question rather than pretending every question has been resolved (“who graded this?”).

Loading Exercise...

What Makes a Design Package Good

Reading through this example, a few qualities stand out:

• the three documents are consistent with one another: every entity in the note appears in the diagram, and every entity in the diagram is explained or mentioned in the rationale where relevant,
• the open questions from the note are either resolved in the diagram or explicitly acknowledged in the rationale,
• the rationale connects decisions to domain needs and to earlier course material rather than asserting them,
• no document is longer than it needs to be.

These qualities come from revision, not from the first draft. The worked example above would not come out cleanly on the first try. The three smaller checkpoints in this part are there precisely so that each piece can be revised before the final assembly.

Documentation Formats

The file formats for the design package are not fixed. Useful options include:

• design/subject-analysis.md for the note,
• design/er-diagram.md (Mermaid) or another agreed diagram format for the diagram,
• design/design-rationale.md for the rationale.

What matters is that the three documents stay together, stay in version control, and get updated together when the design changes. A diagram that has drifted out of sync with the rationale is worse than no rationale at all, because a reader will trust the document that confirms their assumption without checking the other.

Use the Package as a Guide

The design package is the handoff to the next part. Everything the next part does — writing CREATE TABLE statements, choosing data types, adding constraints, writing migrations — should be traceable to a decision in the package.

If a table appears in the next part that the design package does not justify, the package is incomplete. If the package describes a decision that decisions in the next part silently contradicts, one of them is wrong. These cross-checks are the main reason the package exists.

Loading Exercise...

Check Your Understanding

1. What are the three main parts of a design package, and who is each part for?
2. Why is a rationale that mostly restates the diagram usually a sign that it should be shorter?
3. Why does keeping the three documents in version control matter more than the exact file format?

Design Exercise

Build a compact, traceable design package from one case description.

Keep the three parts aligned: your note proposes candidate entities and one open question, your ER summary turns that into explicit relationships, and your rationale explains one non-obvious choice behind the model.

Loading Exercise...