Progetta uno schema di database normalizzato

1. Scrivi i requisiti come frasi semplici: «un cliente può avere più indirizzi».
2. Aggiungi le query che eseguirai più spesso: guidano la scelta degli indici.
3. Incolla il diagramma Mermaid in un visualizzatore compatibile per rivederlo.
4. Esegui il DDL su un database di prova prima di scrivere le migrazioni definitive.

Applicazione: gestione di una scuola di lingue · Requisiti: studenti, corsi, gruppi classe, iscrizioni e pagamenti mensili · Motore: PostgreSQL 16.

4. DDL (estratto, PostgreSQL 16)

CREATE TABLE students (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  email       text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now(),
  deleted_at  timestamptz                -- cancellazione logica
);

CREATE TABLE enrollments (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id      bigint NOT NULL REFERENCES students (id) ON DELETE RESTRICT,
  class_group_id  bigint NOT NULL REFERENCES class_groups (id) ON DELETE RESTRICT,
  enrolled_on     date NOT NULL DEFAULT current_date,
  UNIQUE (student_id, class_group_id)
);

CREATE TABLE monthly_payments (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  enrollment_id  bigint NOT NULL REFERENCES enrollments (id) ON DELETE RESTRICT,
  period         date NOT NULL CHECK (extract(day FROM period) = 1),
  amount_cents   integer NOT NULL CHECK (amount_cents >= 0),
  paid_at        timestamptz,
  UNIQUE (enrollment_id, period)
);

-- Indice parziale per "studenti con pagamenti scaduti"
CREATE INDEX idx_payments_unpaid ON monthly_payments (period) WHERE paid_at IS NULL;

ON DELETE RESTRICT impedisce di perdere lo storico dei pagamenti se qualcuno elimina uno studente: per questo si usa la cancellazione logica. Gli importi sono in centesimi per evitare errori di arrotondamento.

Ambiguità

• Un pagamento copre una sola iscrizione o tutte quelle dello studente nel mese? [DA COMPLETARE]…