Skip to content
AITroveRead. Build. Understand.
Reading list
Make this comfortable

Relational Records and Database Constraints

Last updated: 4 Oct 20266 min read
tutorial
IntermediateBy AITrove Editorial

A relational model names the entities and relationships that must remain true after every request. A case has a stable ID; an inspection belongs to one case; a reviewer assignment connects one reviewer to one case. Primary keys prevent duplicate identity, foreign keys prevent orphaned inspections, and check constraints can reject values that should never enter storage. Application validation still provides clear user messages and permission checks, but a database constraint protects the invariant when another process writes to the same table. Design the schema from real operations rather than placing an entire case in one unvalidated text field.

Working case

Case 47 has two inspection entries and reviewer 29 is assigned to it. A write for a nonexistent case cannot create an orphan inspection because the foreign key rejects it. A duplicate assignment pair cannot be inserted twice. The server checks that reviewer 29 may write before opening the transaction; the database cannot infer that policy merely from the foreign key. If an inspection must never be edited after approval, that rule requires a separate state transition policy. The schema below encodes identity and relationship, not every business permission.

Implementation

sql
CREATE TABLE cases (
  case_id bigint PRIMARY KEY,
  title text NOT NULL
);
CREATE TABLE inspections (
  inspection_id bigint PRIMARY KEY,
  case_id bigint NOT NULL REFERENCES cases(case_id),
  note text NOT NULL CHECK (char_length(note) BETWEEN 1 AND 160)
);
CREATE TABLE reviewer_assignments (
  reviewer_id bigint NOT NULL,
  case_id bigint NOT NULL REFERENCES cases(case_id),
  PRIMARY KEY (reviewer_id, case_id)
);

Cost and boundaries

Each index and foreign key check adds work to writes and storage proportional to row count. Reads for a case's inspections need an index on case_id when the table grows. Normalized rows reduce repeated case data but require joins to assemble a page. Measure query plans at realistic sizes rather than assuming a small development table predicts production behavior. A constraint error should be translated into a safe API response; never expose raw database exception text to the browser.

Common Mistakes

  • Do not rely only on a form field to preserve a database invariant.
  • Do not use a foreign key as a reviewer permission check.
  • Do not store every inspection as an unstructured blob inside one case row.

Connected lessons

Data Persistence; Transactions and Concurrent Writes; Indexes and Query Plans for Case Feeds; Expand-and-Contract Schema Migrations; Form submission: validate on the server and return field errors; Authorization: check permission for this record on every request; HTTP caching: validate a changed representation with an ETag.

Failure trace

The API checks that a case exists before inserting an inspection, but another request deletes the case between those operations. The new inspection points to nothing. Express durable relationships and uniqueness in database constraints, then translate constraint failures into an API response. Application checks can improve error messages, but they cannot replace the database boundary under concurrency.

Verification

  • Attempt to insert an inspection for a missing case and confirm the database rejects it.
  • Create two records with the same business key concurrently and confirm one wins without a duplicate.
  • Delete or archive a parent record and verify the chosen retention rule for its children.

Decision note

Normalize records when it protects a real relationship and update path. A read-heavy projection can be added later; duplicated authoritative values require a reconciliation rule from day one.

Apply and check

Build Project: paginated inspection feed with safe writes; then check the boundary with Web Development: data and API contracts quiz.

web-tech
web-development
Storage details