Build a PostgreSQL Data Layer for a Learning Product

Model the business relationships

Use learners, courses and enrollments as the starting scenario. A learner has an identity; a course describes a learning offering; an enrollment connects a learner to a course and stores participation facts. Keeping those responsibilities distinct makes it easier to update a course description without rewriting every learner record.

Write the application’s essential questions first: which courses are available, which enrollments belong to a learner and which participants belong to a particular session? Do not model every hypothetical future feature. Add structure when a real rule or access pattern requires it.

Express integrity in the database

Choose stable primary keys and foreign keys for relationships. Define uniqueness only where it reflects the business rule. A learner might legitimately repeat a course in a later cohort, so a blanket learner-course uniqueness constraint could be wrong. If the rule is one enrollment per learner per cohort, represent the cohort explicitly.

-- Illustrative relationship, not a complete production schema
CREATE TABLE enrollments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  learner_id bigint NOT NULL REFERENCES learners(id),
  cohort_id bigint NOT NULL REFERENCES cohorts(id),
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (learner_id, cohort_id)
);

The example assumes learners and cohorts already exist. Add a suitable status constraint or reference table based on the actual lifecycle. Application validation improves error messages, while database constraints protect integrity when concurrent or alternate write paths reach the same data.

Protect multi-write invariants

If confirming a registration changes both enrollment and capacity, describe the invariant before writing the transaction. The transaction should prevent a completed state in which those records disagree. Concurrency still needs attention: two requests can both observe apparent availability unless the chosen locking, isolation or conditional-write strategy protects the rule.

Keep transactions short. Do not hold database locks while waiting for a model or sending an email. Commit the relevant database state and coordinate external work through a durable job or event. An external message cannot generally be rolled back by a database transaction.

Design indexes from actual queries

Capture the query that lists a learner’s active enrollments and inspect its execution plan on representative data. A candidate index should follow a demonstrated filter or ordering need. Avoid indexing every displayed field. Each index also costs storage and write work, and a small development dataset may hide the production access pattern.

Count queries per page as well as measuring the slowest query. A catalog that loads each instructor separately can create an N+1 pattern even when individual calls are quick. Use deliberate joins or bounded batches and inspect whether the result duplicates parent rows or changes pagination semantics.

Plan migrations and deletion behaviour

For a changing field, prefer a staged migration when old and new application versions may coexist. Add the new representation, populate it with a resumable backfill, switch reads and remove the old structure only after the transition is verified. A rollback plan must consider data compatibility, not just application code.

Decide what deletion means for learners, enrollments and historical reports. Cascading deletion may be correct for some dependent records and wrong for others. Document retention and anonymization choices according to the product’s actual commitments and applicable requirements; do not let a default foreign-key action make the policy accidentally.

Verify with realistic failure cases

Operational handover

Record the schema’s important business rules, the reason for each critical index, migration procedures and backup ownership. Test a restore in an isolated environment and measure the recovery process. Include uploaded files and configuration dependencies in the recovery plan; a database alone may not restore the user’s access to learning materials.

• Entity ownership and relationship facts are clear.
• Constraints protect real invariants under concurrency.
• Query plans and query counts have been inspected.
• Migrations have a staged compatibility path.
• Recovery has been practiced with the application.