Relational Modeling and Constraints

MODULE 11 · LESSON 11.1

Translate product rules into tables, keys, relationships and constraints.

Practice-firstBeginner-friendlyProduction-aware

From mental model to working change

Good work on Relational Modeling and Constraints leaves evidence: a visible behavior, a stable contract or a repeatable operational check. This lesson defines an application trust boundary, where an explicit contract is safer than framework convention or an undocumented assumption.

Here, that decision supports a specific checkpoint: Design and query the CourseFlow relational database. A reviewable result should include a repeatable request, automated test, query result and failure response rather than a claim that the feature simply works.

Relational Modeling and Constraints workflowA four-step visual showing primary keys, foreign keys, normalization, constraints.Relational Modeling and Constraints workflow1Primary Keys2Foreign Keys3Normalization4Constraints

Relational Modeling and Constraints workflow

  1. 1Primary Keys
  2. 2Foreign Keys
  3. 3Normalization
  4. 4Constraints
Relational Modeling and Constraints workflow: a practical sequence used in this lesson.

A practical model for relational modeling and constraints

Translate product rules into tables, keys, relationships and constraints. The useful unit of understanding is the boundary: who owns the decision, which input crosses it, what result is visible and how a failure is reported.

  • Primary Keys: Connect this concept to the module checkpoint and identify the evidence a reviewer should expect.
  • Foreign Keys: Explain the concept without framework jargon, then point to it in the working example.
  • Normalization: Decide what belongs in code, configuration, data or documentation and explain why.
  • Constraints: Name its input, observable result and most likely failure in this lesson.

Follow the data through the example

The sample is intentionally narrow. Its job is to expose primary keys without hiding the decision behind unrelated setup.

SQL
CREATE TABLE enrollments (
  user_id bigint REFERENCES users(id),
  course_id bigint REFERENCES courses(id),
  enrolled_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, course_id)
);
Review it as someone else's change

Explain what the sample proves, what it does not prove, and which test would increase your confidence in foreign keys.

Ship a reviewable increment

  1. 1
    Primary Keys

    Describe the behavior in one sentence, then choose the smallest input that can prove it.

  2. 2
    Foreign Keys

    Add this responsibility at the narrowest sensible boundary; do not pull an unrelated layer into the change.

  3. 3
    Normalization

    Run the focused example and save the output, trace, query or screenshot that confirms the result.

  4. 4
    Constraints

    Break one assumption on purpose, make recovery clear and record the trade-off you accepted.

Risks to catch during review

  • Treating primary keys as vocabulary instead of defining the behavior it must produce.
  • Testing the expected path while ignoring an empty, invalid, repeated or unauthorized case around foreign keys.
  • Allowing normalization to cross a boundary without an explicit contract or useful error.
  • Changing several layers before capturing the first piece of evidence, which makes the original cause harder to see.

A repeatable investigation sequence

  1. Reduce the problem to the smallest failing Relational Modeling and Constraints case.
  2. Capture the actual input and output at the primary keys boundary.
  3. Read the first relevant error, request, trace or query rather than the loudest downstream symptom.
  4. Test one explanation for the failure in foreign keys; avoid changing two variables together.
  5. Keep a regression check that would expose the same defect if it returned.

Security decision

Validate external input, authorize the requested action, use parameterized data access, and keep credentials out of responses, source control and logs.

Performance decision

Bound queries and collections, inspect the actual request or query plan, and optimize only the slow boundary confirmed by evidence.

PRACTICE

Build something you can inspect

Create an ER model for users, courses, lessons, enrollments and progress.

Stretch challenge

Ask another person to run the exercise from your README. Fix the first place where their result differs from yours.

Definition of done

  • The behavior around primary keys works with realistic input.
  • A failure involving foreign keys is handled clearly and without leaking sensitive detail.
  • The implementation remains keyboard-usable when it produces an interface.
  • Your evidence directly supports the claim made in the exercise.
  • The README records the important trade-off without pretending the solution is universal.

Check your reasoning

Which data rule should be a database constraint rather than application convention?

Answer by naming the expected primary keys behavior, the layer responsible for it and the evidence that would confirm your explanation.

Where would you investigate the first failure?

Start where foreign keys crosses a boundary. Compare the actual input and output there before following downstream symptoms.

What would make this work reviewable?

Show the focused change, repeatable steps, the result of your check and one honest trade-off connected to normalization.

What to carry into the next lesson

  • Translate product rules into tables, keys, relationships and constraints.
  • Keep primary keys visible at the boundary where it can be tested.
  • Use evidence from foreign keys before widening the implementation.

References and related reading

Progress is stored only in this browser.

Share this page

Share this page with the people who will use it next.

X Facebook LinkedIn WhatsApp Email

Discussion

No comments yet. Add the first useful question or observation.