Tens l'encàrrec (12-01) i el contracte (12-02). Falta construir-lo, i construir-lo té un ordre: si escrius les consultes abans de tenir dades, no les pots comprovar; si crees els índexs abans de tenir les consultes, te'ls estàs inventant; i si comences pel CREATE TABLE sense haver dibuixat res, la tercera taula t'obligarà a refer les dues primeres.

Aquesta lliçó és la guia de construcció en set passos: el model complet, el DDL sencer, la tècnica per generar dades coherents, el mètode per escriure consultes sense equivocar-te i el criteri per decidir índexs i encapsulats. El que no et dona són les quinze consultes de l'enunciat fetes: això és 12-04, i arribar-hi sense haver-ho intentat és llençar el projecte.

1. Pas 1 — De l'enunciat al model
2. Pas 2 — El DDL: 01-esquema.sql
3. Pas 3 — Dades de prova coherents: 02-dades.sql
4. Pas 4 — Les consultes: mètode de treball
5. Pas 5 — Els índexs, després de les consultes
6. Pas 6 — Vistes, procediments i disparadors
7. Pas 7 — Seguretat i lliurament
8. Cronograma orientatiu
9. Errors habituals i consells
10. Exercicis
11. Conclusió

Dotze taules i setze relacions: catorze 1:N, una N:M resolta amb la taula pont obres_autors, una reflexiva a bibliotecaris i una 1 a 0..1 entre prestecs i multes. La forma és la de BotigaVerda; el contingut, no. Les columnes són al DDL del pas 2.

1. Per què exemplars és una taula i no un comptador. Argumentat a 12-01: l'exemplar té seu, estat i història propis. La conseqüència tècnica és més contundent que l'argument conceptual: sense aquesta taula, la FK de prestecs apuntaria a obres i seria impossible saber quin dels tres va tornar. No és estil: és que el model no pot representar el fet.

2. Per què prestecs desa data_prevista en lloc de calcular-la. Es podria deduir (data_prestec + termini(tipus del soci)), i no es fa: l'argument és idèntic al de linies_comanda.preu_unitari (01-06, 11-02), és una dada històrica. Si la Marta passa de general a senior, els seus préstecs antics es recalcularien a 30 dies i un que va arribar tard passaria a estar dins de termini. A més es mou amb les renovacions (RN-03), així que ni tan sols és funció de la data inicial. Regla: si el valor depèn d'una condició del passat que pot canviar, es desa.

3. Per què les multes són una taula i no una columna. Té vida pròpia —es genera, es paga, es pot condonar—, i això són tres columnes més, nul·les per al 83 % dels préstecs; permet comptar i sumar sense recórrer tots els préstecs; i demà podria haver-hi multes que no vinguin d'un retard (un llibre malmès), i n'hi hauria prou de fer nul·lable la FK. Relació 1 a 0..1, forçada amb UNIQUE (prestec_id).

4. Com es modela la cua de reserves. Amb data_reserva i res més. Les tres opcions:

5. Per què l'estat del préstec no s'emmagatzema i el de l'exemplar sí. Semblen simètrics i no ho són. El del préstec (actiu, vençut, retornat) és funció de dues dates i del rellotge: desar-lo obligaria a un procés nocturn que marqués els vençuts. El de l'exemplar (disponible, reparacio, extraviat, baixa) és un fet físic que algú decideix i no es dedueix de res. Per això exemplars.estat no inclou prestat: això sí que es dedueix, i tenir-ho seria desar dues vegades el mateix amb dues maneres de contradir-se.

L'ordre és el de 05-01: DROP de fills a pares, CREATE de pares a fills.

Vuit de les dotze taules no tenen cap sorpresa: són el patró exacte de categories, proveidors i devolucions a BotigaVerda, així que es resumeixen en aquesta taula —columnes, nul·lables i restriccions amb nom— i escriure'n el CREATE TABLE et durà cinc minuts:

I aquestes són les quatre que sí que tenen decisions dins del mateix CREATE TABLE:

És la peça tècnica del projecte. Un índex parcial (08-02) es construeix només sobre les files que compleixen una condició; quan a més és UNIQUE, la unicitat només s'exigeix entre aquestes files. Es llegeix literalment: "entre els préstecs sense retornar, l'exemplar_id és únic". Les seves dues proves:

Tres propietats el converteixen en la solució correcta i no en un truc. És declaratiu: ho fa complir el motor, no l'aplicació, així que sobreviu a scripts, a una altra aplicació i a una consola oberta a les tres de la matinada. És diminut: amb 300.000 préstecs històrics i 400 d'actius, l'índex té 400 entrades, no 300.000. I a més accelera, perquè tota consulta amb WHERE data_devolucio IS NULL el pot fer servir: una restricció que de propina és un índex útil.

I el seu límit, que cal dir a l'informe: impedeix dos préstecs actius, no dos préstecs solapats en el passat. Si algú registra avui un préstec de gener ja retornat que es trepitja amb un altre de gener, l'índex no ho veu. La solució completa és a 12-04.

Les dades de prova tenen dos objectius que es destorben: ser suficients perquè les consultes signifiquin alguna cosa i petites per llegir-les amb els ulls. El repartiment: a mà, amb id explícits, tot el que aparegui a les consultes —així "el préstec 29" significa sempre el mateix—; amb generate_series, el volum per mesurar rendiment.

El detall que decideix si això funciona: totes les files generades porten data_devolucio. Si hi deixessis nuls a l'atzar, l'índex únic parcial avortaria la càrrega tan bon punt dos préstecs del mateix exemplar quedessin oberts. Lluny de ser una molèstia, és la prova que la restricció funciona.

BotigaVerda tenia buits deliberats —clients sense comandes, productes sense vendre, comandes sense empleat— i les lliçons els necessitaven. Ara et toca posar-los a tu: sense ells, mitja consulta mal escrita retorna el mateix que una de ben escrita i no te n'assabentes.

Com a 01-06, comprova abans d'escriure ni una sola consulta. El joc de dades de referència té 3 seus, 6 matèries, 5 editorials, 10 autors, 8 bibliotecaris, 12 obres, 15 files d'obres_autors, 20 exemplars, 15 socis, 36 préstecs, 5 reserves i 6 multes. I els buits, en una sola consulta:

1. Comença pel FROM, no pel SELECT. Decideix primer de quina taula surt una fila del resultat: una per obra? El FROM és obres. Una per préstec? És prestecs. Tota la resta s'hi uneix.
2. Comprova el recompte després de cada JOIN. Si en unir exemplars amb prestecs passes de 20 files a 36, has canviat de granularitat: pot ser correcte, però ho has de saber, perquè a partir d'aquí COUNT(*) compta préstecs, no exemplars.
3. Decideix JOIN o LEFT JOIN preguntant pels zeros. Vull veure l'obra sense exemplars, la seu sense préstecs, el mes buit? Llavors LEFT JOIN — i recorda que COUNT(*) compta la fila fantasma i COUNT(columna_de_la_dreta) no (04-04). I valida el total per dos camins: el pivot de RC-15 ha de sumar el mateix que SELECT COUNT(*) FROM prestecs; si no quadra, no discuteixis amb la consulta, està malament (11-04).

"Digue'm on són els tres exemplars d'El jardí de les hores i qui els té." És la pregunta amb què començava el correu de l'Helena, i resumeix el projecte sencer.

Tres decisions que cal justificar. FROM exemplars, perquè vull una fila per exemplar, estigui prestat o no. La condició p.data_devolucio IS NULL va al ON, no al WHERE: allà desapareixerien els exemplars sense préstec actiu i el LEFT JOIN seria un JOIN disfressat (03-03). I estat continua dient disponible als tres, i està bé: és la condició física del volum; que sigui fora es veu a qui_el_te. És la separació del pas 1, feta columna.

L'Helena va demanar "els socis que no vénen des de fa temps". Abans d'escriure res cal definir-ho (11-04): aquí, soci en estat actiu el darrer préstec del qual és anterior a fa 90 dies o que no n'ha pres cap mai. Els de baixa queden fora expressament.

Quatre socis i tres lliçons. El LEFT JOIN és obligatori: amb un JOIN normal, els tres que no han demanat mai res —els més inactius de tots— desapareixerien. El COALESCE del HAVING és el que els deixa passar, perquè NULL < data dona UNKNOWN i HAVING descarta el que no és cert (04-03). I COUNT(p.id), no COUNT(*): amb COUNT(*) els tres sortirien amb 1 préstec en lloc de 0. És l'error més repetit del projecte sencer. I una lectura que no és de SQL: els tres sense préstecs no són el mateix problema —la Carla es va donar d'alta al febrer, la Irene fa un any que té el carnet sense fer servir i la Marta té quatre préstecs i tres mesos sense venir—; ficar-los a la mateixa xifra és el que 11-04 anomenava barrejar dues preguntes en un número.

L'ordre és innegociable: primer les quinze consultes, després els índexs. Un índex existeix per servir una consulta concreta; si no la pots anomenar, sobra i només frena les escriptures (08-02). El mètode és mecànic:

1. Llista el WHERE, el JOIN i l'ORDER BY de cada consulta. Aquesta llista és la teva llista de candidats, i només aquesta (11-05).
2. Afegeix-hi les claus foranes que naveges: PostgreSQL indexa la PK, no la FK (08-01). És la causa número u de recorreguts seqüencials en un esquema de dotze taules.
3. Descarta el que no aporta —una columna de tres valors com socis.tipus o una taula de sis files com materies no s'indexen: el planificador les ignorarà, i farà bé— i mesura amb EXPLAIN (08-05), no amb la intuïció.

Sense índex sobre prestecs(soci_id) i amb volum, el pla és un Seq Scan que llegeix la taula sencera; amb ell, un Index Scan que hi va directe. L'important no és que millori, és que ho comprovis i ho escriguis (RP-06). I amb les 36 files del joc de proves veuràs Seq Scan a tot arreu, perquè llegir 36 files és més barat que obrir un índex — cosa que és una lliçó en si mateixa: per parlar de rendiment cal tenir volum, i per a això hi ha el generate_series del pas 3. Els índexs de referència, amb el seu pla, són a 12-04.

La regla que evita el desastre: encapsula el que es repeteix i el que és delicat; no encapsulis per gust. Tres peces són suficients i una quarta ja seria sospitosa. El codi és a 12-04; aquí va el criteri, que és el que cal saber decidir.

Dos detalls de la vista importen més del que sembla: la seva columna s'anomena multa_estimada, no multa, perquè encara no existeix (RN-10) —anomenar-la multa seria la primera pedra d'un informe que suma diners que ningú no deu—; i fa servir CURRENT_DATE, així que canvia sola cada nit, que és exactament el que es volia i la raó de no desar "vençut" en una columna.

I aquí es para. La temptació d'afegir un disparador per al màxim de préstecs simultanis, un altre per validar renovacions i un altre per recalcular el bloqueig és enorme, i és un error: els disparadors són lògica invisible —qui llegeix l'INSERT no veu què passa— i depurar-los és incòmode. El criteri de 10-05: disparador per al que s'ha de complir passi el que passi i no es pugui declarar; procediment per a l'operació de negoci amb diversos passos; aplicació per a la resta.

Els tres rols de RS-01 a RS-03, amb el patró d'11-03: privilegis al grup, mai a l'usuari.

Dos falsos, que és el que es demanava. Aquesta comprovació de dues línies és la prova de RS-01 i RS-04, i va a l'informe.

Per sessions d'unes dues hores. Desviar-se molt a la sessió 3 sol ser senyal que el model té un problema, no que vagis lent:

• Crear les taules en l'ordre en què se t'acudeixen. La integritat referencial imposa l'ordre: pares abans que fills en crear, al revés en esborrar (05-01). Si el teu script només funciona la primera vegada, no és idempotent i RE-02 no es compleix.
• Posar la condició del LEFT JOIN al WHERE. L'error més car i més silenciós del projecte: converteix el LEFT JOIN en JOIN, desapareixen els zeros i el resultat continua semblant raonable. Condició sobre la taula de la dreta ⇒ va al ON.
• COUNT(*) després d'un LEFT JOIN. Compta la fila fantasma i els socis sense préstecs surten amb 1. Fes servir COUNT(columna_de_la_dreta).
• Afegir el valor prestat a exemplars.estat, o generar dades a l'atzar sense mirar les restriccions. El primer duplica informació que acabarà contradient prestecs; el segon (devolucions anteriors al préstec, dos actius del mateix exemplar, multes sense retard) avorta l'script a mitges i deixa la base a mig carregar.
• Consell: després de crear l'esquema, intenta violar-lo, i escriu el DDL amb els -- RI-nn al costat de cada restricció. Un INSERT que ha de fallar i falla val més que tres paràgrafs d'informe, i repassar els catorze requisits amb la rúbrica et durà un minut en lloc de mitja hora.
• Consell: desa un 99-comprovacions.sql amb els recomptes, els vuit buits, els INSERT que han de fallar i les dues consultes de privilegis. És la teva bateria de proves: executar-la després de cada canvi et dirà a l'instant si has trencat res.
• Consell: escriu l'informe a mesura que decideixes. Cada decisió difícil, el seu paràgraf, en el moment. Reconstruir-lo l'última nit és impossible i es nota.

Implementa la RN-04 completa: no es pot renovar si s'ha assolit el màxim, si el préstec està vençut o si l'obra té reserves en espera. (1) Quines de les tres condicions es poden declarar amb un CHECK i quines no, i per què? (2) Escriu un UPDATE que renovi el préstec 32 només si les tres ho permeten. (3) Què retorna amb les dades del projecte?

El préstec 29 (Lena Fuentes, exemplar ALV-0003) porta 36 dies de retard i es retorna avui, 2026-06-30. (1) Enumera totes les files que canvien, en quines taules i amb quins valors. (2) Per què les tres operacions han d'anar a la mateixa transacció, i què passaria si fallés la tercera després de les dues primeres? (3) Quin efecte té la devolució sobre l'índex únic parcial?

Un company proposa afegir a obres una columna num_exemplars INTEGER "per no haver de comptar cada vegada". (1) Dona tres arguments en contra. (2) Dona un escenari en què sí que seria defensable. (3) Si s'hagués de fer, com el mantindries i què et costaria?

Solució 1 — (1) Cap, i per motius diferents. El màxim de renovacions depèn del tipus del soci, que és en una altra taula, i un CHECK no pot consultar altres taules (05-01): l'únic declarable és el rang absolut BETWEEN 0 AND 2. Que el préstec estigui vençut depèn de CURRENT_DATE, i un CHECK amb funció no determinista està desaconsellat —una fila vàlida avui deixaria de ser-ho demà i una restauració de còpia fallaria sense motiu—. Les reserves són en una altra taula. Les tres són lògica d'operació. (2) Tot al WHERE, que és on es comprova sense llegir abans:

(3) Retorna UPDATE 0. El préstec 32 és de la Sofía Terán amb l'exemplar ALV-0001, d'El jardí de les hores — l'obra que té tres socis a la cua. La tercera condició el bloqueja, i fa bé: renovar-lo deixaria en Nuno Barros esperant un mes més per un llibre els tres exemplars del qual són fora. I UPDATE 0 no és un error: cal comprovar-ho a l'aplicació i traduir-ho a un missatge.

Solució 2 — (1) Canvien tres files en tres taules:

(2) Perquè les tres són una sola operació de negoci: un exemplar retornat que genera la seva multa i activa la seva reserva. Si fallés la tercera sense transacció, quedaria un préstec tancat amb la seva multa correcta i una cua que ningú no ha avisat: en Nuno continuaria esperant un llibre que ja és al taulell, i sense rastre de la fallada. És ACID en la seva forma més simple (09-01, 09-02): o les tres, o cap. En PL/pgSQL el bloc del procediment ja és una transacció implícita, així que una excepció al pas 3 desfà els dos primers. (3) En deixar d'estar actiu, el préstec 29 surt de l'índex únic parcial —la seva fila ja no compleix WHERE data_devolucio IS NULL— i l'exemplar ALV-0003 torna a poder-se prestar. La restricció es deixa anar sola: aquesta és l'elegància de l'índex parcial enfront d'una columna actiu que algú hauria de recordar-se de canviar.

Solució 3 — (1) Primer, és redundància pura: la dada ja és a exemplars i un COUNT(*) l'obté en microsegons amb l'índex d'exemplars(obra_id). Segon, s'ha de mantenir a cada alta, baixa i trasllat d'exemplar; el dia que algú insereixi des d'un script, la xifra queda malament per sempre i ningú no se n'assabenta, perquè un número plausible no salta a la vista. Tercer, no respon a la pregunta real: ningú no pregunta quants exemplars hi ha, pregunten quants n'hi ha disponibles ara, que depèn de prestecs i canvia cada minut.

(2) Seria defensable amb milions d'obres, en una pantalla de catàleg que mostra el recompte a cada resultat de cerca i es llegeix milers de vegades per segon, si l'agregat s'hagués mesurat i fos el coll d'ampolla. És a dir: quan hi hagi un EXPLAIN que ho justifiqui, no abans (08-04). I tot i així, la primera opció seria una vista materialitzada (10-01) refrescada cada nit, que aïlla la redundància en un objecte marcat com a derivat en lloc d'amagar-la en una columna que sembla una dada.

(3) Amb un disparador AFTER INSERT OR UPDATE OR DELETE sobre exemplars que sumi o resti. El cost: cada escriptura a exemplars escriu també a obres, cosa que crea contenció sobre la fila de l'obra —dues altes simultànies d'exemplars del mateix títol es serialitzen (09-05)— i afegeix lògica invisible; més una consulta de recàlcul des de zero, per corregir-ho quan (no si) es desincronitzi. Tot això, per estalviar un COUNT.

Ja saps construir-lo:

• El model: dotze taules i setze relacions, amb una N:M de clau primària composta i una de reflexiva. I les cinc decisions difícils argumentades: exemplars és taula perquè l'objecte té seu, estat i història propis; data_prevista es desa perquè és dada històrica i a més es mou amb les renovacions, igual que preu_unitari; les multes són taula perquè tenen cicle de vida; la cua es calcula des de data_reserva en lloc de desar una posició; i l'estat del préstec es deriva mentre el de l'exemplar s'emmagatzema, perquè un és funció del rellotge i l'altre un fet físic.
• El DDL, amb restriccions amb nom i anotades amb el seu RI-nn, i l'índex únic parcial WHERE data_devolucio IS NULL: declaratiu, diminut i útil també com a índex, amb el seu límit reconegut —no detecta solapaments històrics—. Les dades: a mà el que apareix a les consultes, generate_series per al volum (amb totes les files retornades, o RI-03 avorta la càrrega) i els casos límit sense els quals una consulta mal escrita passa per bona, amb els recomptes i els vuit buits verificats abans d'escriure la primera consulta.
• El mètode: començar pel FROM decidint què és una fila del resultat, comprovar el recompte després de cada JOIN, triar LEFT JOIN preguntant pels zeros i validar el total per dos camins. Els dos exemples resolts ensenyen els dos paranys: la condició del LEFT JOIN va al ON, i COUNT(*) compta la fila fantasma.
• Els índexs després de les consultes, no al revés; tres encapsulats i ni un més —la vista de vençuts amb el seu honest multa_estimada, el procediment atòmic de devolució i l'únic disparador necessari—; i tres rols de privilegi mínim verificats amb has_table_privilege.

Ja l'has construït; ara toca comparar-lo, que és on de debò s'aprèn. A la lliçó següent, Solucions comentades del projecte, hi ha el solucionari: les decisions de l'esquema justificades una a una amb les alternatives que també serien correctes —inclosa la restricció EXCLUDE i el debat entre estat derivat i emmagatzemat—; les 15 consultes resoltes amb el seu resultat, la seva decisió clau i l'error típic de cadascuna; els índexs de referència amb el pla que els aprofita; el codi de la vista, el procediment i el disparador; i el catàleg del que més falla en aquest projecte en concret.