Database Design and Modeling

Reading a Case Description

---

Database design tasks typically begin with forming an understanding of the domain. The starting point is typically a short case description, written in ordinary language, far less precise than an actual schema. Turning it into a useful starting point is a skill in its own right.

This skill is called subject analysis, and it is what the rest of this part is built on top of. In this chapter, we walk through one worked example from start to finish. In the next chapter, you will do the same work on two fresh cases.

Subject analysis is the concrete technique behind step 1 of the design loop from The Database Design Process. Once the reading is done, the design loop takes over: the note from this chapter feeds into the entity and relationship chapters that follow.

Part 1 already practiced a related skill from the opposite direction. In Exploring Database Structure, you read an existing schema and inferred what questions it could answer. Subject analysis reverses that move: now you begin from the questions and the prose, and you infer what the future structure will probably need.

What Subject Analysis Means

Subject analysis is the early discovery step where we look for the important things, roles, events, rules, and questions in the domain. At this stage, the goal is not to finalize table names. The goal is to understand what the domain is actually about.

A useful subject-analysis note usually contains:

• candidate entities (the main things the system remembers),
• candidate relationships (how those things are connected),
• candidate events (things that happen at a point in time),
• business rules (statements that constrain what is allowed),
• open questions (ambiguities that need clarification later),
• likely application queries (concrete questions the system should answer).

That is a lot of categories, but they will become familiar with practice. The important point is that the note does more than list nouns. It also records relationships, constraints, and uncertainty.

Loading Exercise...

A Three-Pass Reading Method

A useful workflow is to read the case description three times, each time with a different goal:

1. first pass: underline candidate things and roles,
2. second pass: underline actions, connections, and rules,
3. third pass: list open questions and likely application queries.

This keeps the early design work concrete without turning it too quickly into table syntax.

The three passes matter because the eye tends to find what it is looking for. Trying to capture everything at once usually results in a messy list where entities, events, and rules are mixed together. Separating the passes keeps the note cleaner.

Loading Exercise...

Case Description: Small Course Platform

Consider this case description for a small course platform:

A department runs several courses. Each course may be offered multiple times, once each term; each offering — an instance of the course — has exercises that students work on. Students enroll in an instance at the start of the term. When a student submits work for an exercise, the system records the submission and the time it was made. Teachers later grade the submissions and give a score. A student may resubmit an exercise, and in that case only the most recent submission counts for grading.

This is a typical short description: friendly to read, but full of quiet design decisions.

First Pass: Things and Roles

The first pass looks for nouns that might become entities, and for roles that refer to people playing a part in the domain.

Going through the description sentence by sentence:

• “A department runs several courses” — department, course
• “Each course may be offered multiple times, once each term; each offering — an instance of the course — has exercises” — course instance, exercise
• “Students enroll in an instance” — student (a role), enrollment (an idea that may or may not be its own entity)
• “When a student submits work for an exercise, the system records the submission” — submission
• “Teachers later grade the submissions” — teacher (a role)

After one pass, the candidate list already looks like this:

• department, course, course instance, exercise, submission
• roles: student, teacher

Notice that we have not committed to anything yet. department might not need to be its own entity if the system never needs to distinguish one department from another. enrollment and submission might be events rather than stable things. These are exactly the kinds of questions later passes will sharpen.

Second Pass: Actions, Connections, and Rules

The second pass looks for verbs that describe what happens and sentences that describe what is or is not allowed.

Going through the description again:

• “A department runs several courses” — connection: department -> many courses
• “Each course may be offered multiple times, once each term; each offering — an instance of the course” — connection: course -> many course instances
• “Each offering — an instance of the course — has exercises” — connection: course instance -> many exercises
• “Students enroll in a course instance” — connection: student <-> course instance (probably many-to-many)
• “When a student submits work for an exercise, the system records the submission and the time” — action: submitting work; submission has a timestamp
• “Teachers later grade the submissions and give a score” — action: grading; submission has a score
• “A student may resubmit” — rule: multiple submissions per student per exercise are allowed
• “Only the most recent submission counts for grading” — rule: grading uses the latest submission

The second pass already tells us more than the first. Some items are clearly connections between entities, some are events with attributes, and some are rules that will eventually shape constraints or application logic.

Third Pass: Questions and Open Items

The third pass asks what the case description did not fully settle. This is often the most valuable pass because it keeps the design from quietly assuming things that may not be true.

From this description, reasonable open questions include:

• Can a course exist before any course instance has been created?
• Does “grade the submission” mean the score is set once, or can it be updated?
• Does the system need to remember old submissions after a resubmission, or only the latest?
• Are teachers and students conceptually the same, i.e., are both users?
• Can a student enroll in a course instance after it has started?
• Do we need to record who or what graded which submission?

Capturing these questions matters because designs often fail due to hidden assumptions. Writing them down makes it possible to answer them later with the right person, instead of guessing now and discovering the mismatch only after the schema is in use.

Likely application queries also belong in this pass:

• Which exercises are in a given course instance?
• Which students are enrolled in a given course instance?
• Which submissions still need grading?
• What is the most recent submission from a given student for a given exercise?
• Which students have enrolled in a course instance but never submitted any work?
• What is the average score for a given exercise?

Those questions help later. When the draft model is ready, we will check whether each of these questions can actually be answered from the draft structure. This is focused on in the chapter Testing a Design Against Its Questions.

Loading Exercise...

A Compact Worked Note

Putting the three passes together gives a first subject-analysis note for this case:

• candidate entities: user, course, course instance, exercise, submission
• candidate relationships: enrollment
• candidate events: submission (has a timestamp), grading (sets a score)
• 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
• open questions:
  • is the grading score updatable?
  • do we keep the history of earlier submissions?
  • are teachers and students in the same users table?
  • 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 still needing 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

This is still not a schema. It is a first map of the domain, organized so that later modeling steps have something to work from. The same structure — entities, relationship concepts, events, rules, open questions, likely application queries — will be used in every later note in this part.

Capturing Ambiguity Is Important

One of the hardest habits to develop is resisting the urge to resolve every question right away. When the case description is ambiguous, writing “we do not yet know whether X or Y” is a better note than guessing.

The worked note above has five open questions. Each of them will eventually have to be answered, but none of them has to be answered now. What matters is that they are visible, so that they can be discussed before any assumption gets quietly baked into a table.

A note that hides its uncertainty tends to produce designs that look confident but are built on sand. A note that makes its uncertainty visible tends to produce designs that survive later scrutiny.

Loading Exercise...

Check Your Understanding

1. Why is subject analysis useful before drawing an ER diagram?
2. What is the point of reading the case description in three passes rather than one?
3. Why is it valuable to record open questions in the note instead of guessing answers?

Design Exercise

Up to this point, you have seen a fully worked example of subject analysis. Next, you will do the same kind of reading yourself on a new case. Focus on separating entities, relationships, events, rules, and open questions, and keep your note conceptual rather than SQL-oriented. The goal is not a perfect final model, but a clear first-pass analysis that makes assumptions visible.

Loading Exercise...