Database Schema Design & ER Diagrams

Design relationships before building forms

A relational schema defines entities, identifiers and constraints. An entity-relationship diagram should show cardinality, not merely boxes with names. For a learning portal, a user can enrol in many courses and a course can have many users, so an enrolment table represents that relationship.

Minimal PostgreSQL schema

CREATE TABLE app_user (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);
CREATE TABLE course (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  published boolean NOT NULL DEFAULT false
);
CREATE TABLE enrolment (
  user_id bigint NOT NULL REFERENCES app_user(id),
  course_id bigint NOT NULL REFERENCES course(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, course_id)
);

The composite primary key prevents duplicate enrolments for the same user/course pair. Foreign keys prevent references to nonexistent records. Email uniqueness here is exact according to the chosen database collation/type behavior; define a consistent normalization policy rather than assuming every case variation is automatically identical.

Normalize dependencies

Store a course title in course, not copied into every enrolment. A user email belongs to the user, not every exam attempt. An order line may intentionally store a historical price snapshot because the price at purchase must not change when a catalogue price changes later. Normalization serves the meaning of the data, not a rule to remove every duplicate-looking value.

Constraints and transactions

Put essential uniqueness, nullability and range rules in the database where possible. An application-only “check then insert” can race with another request. A transaction groups related writes; define rollback and concurrency behavior for operations such as inventory reservation.

Queries and indexes

SELECT c.id, c.title
FROM enrolment e
JOIN course c ON c.id = e.course_id
WHERE e.user_id = $1
ORDER BY c.id;

$1 represents a bound parameter in a suitable PostgreSQL client. Add indexes based on actual access patterns and query plans. A primary key creates useful indexing, but not every reverse lookup is automatically covered optimally.

Practice

1. Draw user 1-to-many enrolment and course 1-to-many enrolment.
2. Attempt a duplicate enrolment and confirm the constraint rejects it.
3. Add lesson and progress entities, deciding whether progress is per lesson or per attempt.
4. Write a migration and a recovery plan before changing a production schema.