O bază de date bună nu începe cu un șir de comenzi SQL. Începe cu întrebări despre realitate: ce obiecte există, cum se identifică, ce relații au și ce situații trebuie să fie imposibile. Abia apoi transformăm regulile în tabele, chei, constrângeri și interogări.
Vom folosi drept exemplu o aplicație educațională simplificată în care cursanții se înscriu la ediții de curs. Scopul nu este să copiem arhitectura unei platforme reale, ci să exersăm o metodă repetabilă.
1. Scrie regulile înaintea tabelelor
Pentru exemplul nostru alegem următoarele reguli:
- un cursant este identificat unic prin adresa de e-mail;
- un curs poate avea mai multe ediții;
- o ediție aparține unui singur curs;
- un cursant se poate înscrie o singură dată la aceeași ediție;
- data de final a ediției nu poate fi înaintea datei de început;
- starea înscrierii aparține unei liste explicite de valori.
Aceste propoziții sunt contractul modelului. Dacă o regulă importantă rămâne doar într-un comentariu sau într-o pagină, baza de date poate accepta stări pe care aplicația nu știe să le gestioneze.
2. Separă entitățile și relațiile
Modelul are patru entități: learner, course, course_edition și enrollment. Ultima reprezintă relația dintre cursant și ediție și poate păstra propriile atribute, precum data și starea înscrierii.
CREATE TABLE learner (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
full_name text NOT NULL
);
CREATE TABLE course (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL
);
CREATE TABLE course_edition (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
course_id bigint NOT NULL REFERENCES course(id),
starts_on date NOT NULL,
ends_on date NOT NULL,
CONSTRAINT valid_edition_dates CHECK (ends_on >= starts_on)
);
CREATE TABLE enrollment (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
learner_id bigint NOT NULL REFERENCES learner(id),
edition_id bigint NOT NULL REFERENCES course_edition(id),
status text NOT NULL CHECK (status IN ('pending', 'active', 'completed')),
enrolled_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT one_enrollment_per_edition UNIQUE (learner_id, edition_id)
);Cheile străine păstrează relațiile valide. Constrângerea UNIQUE împiedică dublarea aceleiași înscrieri, iar CHECK respinge datele calendaristice sau stările nepermise. Aceste reguli funcționează indiferent dacă datele vin din interfața web, dintr-un script sau dintr-o integrare.
3. Alege cheia după rol, nu după aparență
Cheia primară identifică stabil un rând. Un identificator numeric generat este ușor de referit și nu depinde de schimbarea unui nume sau a unei adrese de e-mail. O cheie naturală poate primi separat o constrângere UNIQUE, așa cum am făcut pentru e-mail.
Nu toate valorile distincte sunt identificatori buni. Numele unei persoane nu este unic, iar titlul unui curs se poate modifica sau repeta. Alegerea trebuie să reflecte identitatea și durata de viață a înregistrării.
4. Folosește NULL numai pentru „necunoscut” sau „neaplicabil”
NULL nu înseamnă șir gol, zero sau fals. El exprimă absența unei valori. Dacă fiecare ediție trebuie să aibă o dată de început, coloana trebuie să fie NOT NULL. Dacă o dată de finalizare există numai după absolvire, ea poate fi nulă până la acel eveniment.
Întrebarea utilă pentru fiecare coloană este: „În ce situație legitimă această valoare lipsește?” Dacă nu există un răspuns clar, NOT NULL este de obicei contractul mai sigur.
5. Protejează operațiile compuse prin tranzacții
Unele operații au mai mulți pași care trebuie să reușească împreună. De exemplu, activarea unei înscrieri și înregistrarea evenimentului de audit nu trebuie să lase sistemul la jumătate dacă al doilea pas eșuează.
BEGIN;
UPDATE enrollment
SET status = 'active'
WHERE id = 42 AND status = 'pending';
INSERT INTO enrollment_event (enrollment_id, event_type)
VALUES (42, 'activated');
COMMIT;O tranzacție grupează pașii într-o unitate: modificările devin vizibile împreună la COMMIT, iar în caz de eroare pot fi anulate prin ROLLBACK. Tranzacția nu corectează automat o regulă de business greșită; ea garantează atomicitatea pașilor pe care i-ai definit.
6. Scrie întâi interogarea corectă
Pentru a lista înscrierile active împreună cu persoana și cursul:
SELECT
l.full_name,
c.title,
ce.starts_on,
ce.ends_on
FROM enrollment AS e
JOIN learner AS l ON l.id = e.learner_id
JOIN course_edition AS ce ON ce.id = e.edition_id
JOIN course AS c ON c.id = ce.course_id
WHERE e.status = 'active'
ORDER BY ce.starts_on, l.full_name;Testează rezultatul cu date mici care includ cazuri-limită: două ediții ale aceluiași curs, un cursant înscris în mai multe ediții și o ediție fără înscrieri. Corectitudinea vine înaintea optimizării.
7. Adaugă indexuri pentru interogări reale
Un index poate reduce numărul de rânduri examinate, dar ocupă spațiu și adaugă lucru la inserări și actualizări. PostgreSQL creează automat indexuri pentru cheile primare și constrângerile unice, însă nu înseamnă că orice coloană folosită într-un filtru trebuie indexată.
Pentru o aplicație care caută frecvent înscrierile unei ediții după stare, un candidat ar putea fi:
CREATE INDEX enrollment_edition_status_idx
ON enrollment (edition_id, status);Ordinea coloanelor trebuie aleasă după interogările reale. Verifică decizia cu EXPLAIN sau EXPLAIN ANALYZE, pe un volum reprezentativ, și citește planul de execuție. Nu presupune că indexul este folosit doar pentru că există.
8. Verifică modelul ca pe un contract
Un set minim de verificări pentru exemplul nostru include:
- o a doua înscriere a aceluiași cursant la aceeași ediție este respinsă;
- o ediție cu finalul înaintea începutului este respinsă;
- o înscriere nu poate indica un cursant sau o ediție inexistentă;
- ștergerea unei entități referite are politica explicită potrivită domeniului;
- tranzacția nu lasă modificări parțiale la o eroare simulată;
- interogările importante întorc rezultatul corect și au un plan acceptabil.
Într-un sistem cu date comerciale sau financiare, regulile devin mai stricte: păstrarea istoricului, trasabilitatea și corecțiile aditive trebuie proiectate explicit. Un model convenabil pentru ecran nu este automat un model sigur pentru audit.
Exercițiu practic
Extinde schema cu o tabelă lesson și una lesson_progress. Definește înainte de SQL:
- dacă ordinea lecțiilor este unică în cadrul unei ediții sau al cursului;
- dacă progresul poate exista înaintea înscrierii;
- ce înseamnă finalizarea și ce date trebuie păstrate;
- ce ștergeri trebuie blocate pentru a păstra istoricul.
Apoi implementează schema, încearcă intenționat date invalide și explică ce constrângere le respinge. Dacă vrei să consolidezi mai întâi logica necesară pentru astfel de proiecte, vezi programul Python Foundations și modul de lucru descris în Metoda Carpathica.