Fins ara hem mirat les taules d'una en una. Però el poder real d'una base de dades relacional no és a les taules: és a les relacions entre elles. Aquesta lliçó respon la pregunta que tot principiant es fa en veure l'esquema de BotigaVerda: "per què nou taules i no una de sola amb tot?". Veuràs què és una clau primària i per què el curs fa servir id numèrics, com una clau forana impedeix físicament que existeixi una comanda d'un client inexistent, què passa quan intentes esborrar un registre del qual depenen d'altres, com es representen les cardinalitats 1:1, 1:N i N:M, i com la normalització descompon una taula monolítica en un conjunt de taules sanes. És la lliçó més conceptual del mòdul i també la que més rendiment et donarà quan arribis als JOIN.

1. El model relacional en 10 minuts
2. Clau primària: natural davant de subrogada
3. Claus candidates i claus úniques
4. Clau forana i integritat referencial
5. Què passa en esborrar o modificar un pare: ON DELETE i ON UPDATE
6. Cardinalitats: 1:1, 1:N i N:M
7. Normalització pràctica: 1FN, 2FN i 3FN
8. Quan desnormalitzar a propòsit
9. Errors habituals i consells
10. Exercicis
11. Conclusió

El model relacional el va proposar Edgar F. Codd el 1970 i descansa en tres idees sorprenentment simples.

Nota important: "relació" no significa "relació entre taules". En la terminologia de Codd, una relació és una taula. Les connexions entre taules s'anomenen associacions o, a la pràctica, s'implementen mitjançant claus foranes. La coincidència de noms confon molta gent.

1. Una relació és un conjunt, així que no hi ha ordre ni duplicats conceptuals. Si dues files fossin idèntiques en tot, serien la mateixa tupla.
2. Les dades es relacionen pel seu valor, no per punters. A BotigaVerda, comandes.client_id = 7 assenyala el client 7 perquè el valor coincideix, no perquè hi hagi una adreça de memòria desada. Aquesta idea, que avui sembla òbvia, era revolucionària davant dels sistemes jeràrquics i de xarxa dels anys 60.
3. L'estructura és independent de l'accés. Pots reorganitzar índexs i emmagatzematge sense canviar ni una consulta.

De la propietat 2 en surt directament tot el que veuràs al mòdul 3: un JOIN no és més que aparellar files els valors de les quals coincideixen.

Una clau primària (PK) és la columna —o combinació de columnes— que identifica de manera única cada fila d'una taula. Les seves tres propietats:

• Única: no es pot repetir.
• No nul·la: mai no pot ser NULL.
• Estable: idealment no hauria de canviar mai.

Hi ha dues filosofies per triar-la:

Les raons:

1. Uniformitat. Saps que qualsevol taula s'identifica per id, i que qualsevol clau forana es diu <taula_singular>_id. Zero sorpreses.
2. Estabilitat. L'email d'un client pot canviar; el seu id no. Si l'email fos la PK i canviés, caldria actualitzar en cascada totes les taules que el referencien.
3. Eficiència. Un INTEGER ocupa 4 bytes; un email, 30 o 40. Tots els índexs i totes les claus foranes se'n beneficien.
4. Llegibilitat didàctica. WHERE client_id = 7 és infinitament més còmode en un curs que WHERE client_email = '[email protected]'.

Important: fer servir id subrogat no eximeix de declarar la clau natural com a UNIQUE. A BotigaVerda, clients.email és UNIQUE encara que la PK sigui id: sense aquest UNIQUE podries registrar dues vegades el mateix client i la base de dades no protestaria.

Res no obliga que la PK sigui una sola columna. Es podria formar amb diverses:

Això significaria "un producte només pot aparèixer una vegada a cada comanda". És una decisió legítima, però BotigaVerda fa servir un id propi a linies_comanda per uniformitat i perquè permet que un mateix producte aparegui en dues línies de la mateixa comanda amb preus o descomptes diferents.

• Una clau candidata és qualsevol conjunt de columnes que identifica unívocament una fila. Una taula en pot tenir diverses.
• La clau primària és la candidata que tries com a identificador oficial.
• La resta de candidates es declaren com a claus úniques (UNIQUE).

A clients tenim dues candidates:

Diferència clau entre PRIMARY KEY i UNIQUE:

Aquest detall dels NULL a UNIQUE sorprèn: PostgreSQL considera que dos nuls no són iguals entre si, així que una columna UNIQUE pot tenir moltes files amb NULL. Si necessites el contrari, PostgreSQL 15 va introduir UNIQUE NULLS NOT DISTINCT.

Una clau forana (FK) és una columna que conté valors que han d'existir a la clau primària d'una altra taula. És el mecanisme que connecta les taules i, sobretot, el que garanteix que no hi hagi dades òrfenes.

A BotigaVerda:

Es llegeix: "client_id ha de correspondre a un id existent a clients, i és obligatori; empleat_id també ha d'existir a empleats, però pot quedar buit".

Això s'anomena integritat referencial: la base de dades garanteix que les referències apunten a alguna cosa real. I no és una recomanació, és una barrera física.

BotigaVerda té 15 clients. Si intentes registrar una comanda del client 999:

Llegeix-lo amb atenció, perquè el veuràs moltes vegades:

• violates foreign key constraint → has trencat una FK.
• El nom comandes_client_id_fkey segueix el patró <taula>_<columna>_fkey i et diu exactament quina.
• El DETAIL et dona el valor culpable (999) i la taula on hauria d'existir.

Sense aquesta restricció tindries una comanda fantasma: apareixeria al total de vendes, però en intentar mostrar el nom del client no hi hauria res. Aquest tipus d'inconsistència és demolidor en un sistema real, i és impossible d'introduir aquí.

La clienta 1 (Lucía Martínez Soler) té comandes. Si intentes esborrar-la:

PostgreSQL s'hi nega: esborrar aquesta clienta deixaria comandes apuntant al buit. Aquest comportament per defecte s'anomena RESTRICT (tècnicament NO ACTION, que és equivalent tret de les transaccions diferides), i és exactament el que vols la majoria de les vegades.

En declarar una FK pots triar què ha de passar quan la fila referenciada s'esborra (ON DELETE) o canvia la seva clau (ON UPDATE).

Aplicat a BotigaVerda, l'elecció no és arbitrària: cada relació demana una acció diferent segons el significat de negoci.

Regla mental per decidir: pregunta't "la fila filla té sentit per si sola si el pare desapareix?". Si no en té, CASCADE. Si en té però perd una relació opcional, SET NULL. Si el pare no hauria de poder desaparèixer estant referenciat, RESTRICT.

Compte amb CASCADE. És còmode i perillós: un sol DELETE es pot propagar per mitja base de dades en silenci. Fes-lo servir únicament quan la relació sigui de composició real (la part no viu sense el tot), com linies_comanda respecte a comandes.

ON UPDATE funciona igual, però es dispara quan canvia la clau primària del pare. Amb claus subrogades gairebé mai no es fa servir, perquè un id autoincremental no canvia mai. És precisament un dels avantatges de les claus subrogades davant de les naturals.

La sintaxi completa per declarar aquestes restriccions (CONSTRAINT ... FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE) s'estudia a la lliçó 05-01. Aquí el que importa és entendre el criteri de decisió.

La cardinalitat descriu quantes files d'una taula es poden associar amb quantes d'una altra.

És la més freqüent. Una categoria té molts productes; cada producte pertany a una sola categoria.

Com s'implementa: la FK va sempre al costat "molts". productes.categoria_id apunta a categories.id. Mai al revés: si posessis una columna producte_id a categories, només hi cabria un producte per categoria.

Relacions 1:N a BotigaVerda:

Una comanda conté molts productes, i un producte apareix en moltes comandes. Una relació N:M no es pot implementar directament: no hi ha on posar la FK. La solució és una taula pont (o taula intermèdia, o d'associació).

La N:M entre comandes i productes es descompon en dues relacions 1:N que convergeixen a linies_comanda.

I aquí hi ha el detall que distingeix una taula pont ben dissenyada: linies_comanda no és només un enllaç, té dades pròpies.

Aquest preu_unitari és un exemple perfecte de desnormalització deliberada (secció 8): duplica informació que també és a productes.preu, però és imprescindible perquè són coses diferents: un és el preu actual i l'altre el preu històric facturat.

Cada fila d'A es correspon amb com a màxim una de B. S'implementa posant una FK en una de les dues taules i declarant-la a més UNIQUE.

BotigaVerda no té relacions 1:1, perquè gairebé mai no calen: si la correspondència és exacta, el natural és fondre totes dues taules en una. Els casos legítims són tres:

• Separar columnes grans poc consultades (una taula productes_fitxa_tecnica amb textos enormes).
• Aïllar dades sensibles amb permisos diferents (una taula empleats_dades_bancaries).
• Especialització: una taula usuaris general i taules usuaris_admin / usuaris_client amb camps exclusius.

Una taula es pot referenciar a si mateixa. BotigaVerda té dos casos:

Totes dues columnes admeten NULL, i aquest nul té un significat precís: cap_id IS NULL identifica la direcció general (l'empleada 1, Rosa Alcázar Vives) i referit_per_id IS NULL identifica els clients que van arribar pel seu compte. Consultar aquestes relacions requereix SELF JOIN, que s'estudia a la lliçó 03-06.

La normalització és el procés d'organitzar les columnes en taules per eliminar redundància i evitar anomalies. Sona acadèmic, però s'entén millor veient què passa quan no es fa.

Imagina't que BotigaVerda desés totes les seves comandes en una única taula:

Els problemes són immediats:

Regla: cada cel·la conté un únic valor atòmic; no hi ha grups repetits.

La columna productes viola la 1FN descaradament: conté tres valors en una cel·la. Per arreglar-ho cal treure els productes a files pròpies.

Senyals que alguna cosa viola la 1FN:

• Llistes separades per comes en una cel·la.
• Columnes numerades: producte_1, producte_2, producte_3.
• Un camp que de vegades conté una dada i de vegades diverses.

Després d'aplicar la 1FN, cada producte de cada comanda és una fila, cosa que ja ens dona el germen de linies_comanda.

Regla: estar en 1FN i que cap atribut no clau no depengui només d'una part d'una clau primària composta.

Després de la 1FN, la clau de la nostra taula seria (comanda_id, producte_nom). Però fixa-t'hi:

• data depèn només de comanda_id, no del producte.
• client_nom, client_email i client_ciutat depenen només de comanda_id.
• categoria depèn només de producte_nom, no de la comanda.

Són dependències parcials, i provoquen que aquestes dades es repeteixin una vegada per cada línia de la comanda. La solució és dividir:

• El que depèn de la comanda → taula comandes.
• El que depèn del producte → taula productes.
• El que depèn de la combinació (quantitat, preu de venda, descompte) → taula linies_comanda.

Aquí tens, deduïda, la taula pont de la secció 6.2.

Regla: estar en 2FN i que cap atribut no clau no depengui d'un altre atribut no clau (res de dependències transitives).

A la taula comandes resultant continuaríem tenint client_nom, client_email i client_ciutat. Aquests depenen de client_email (o del client en general), no de comanda_id. És una dependència transitiva: comanda_id → client → email.

La solució: extreure una taula clients i deixar a comandes només la referència client_id. Idènticament, categoria a productes depèn de la categoria, no del producte: s'extreu categories i queda productes.categoria_id.

Aplicant aquestes tres regles al cas de BotigaVerda arribes, gairebé mecànicament, a l'esquema de nou taules del curs. Aquesta és la resposta a la pregunta inicial: l'esquema no està partit per caprici, sinó perquè cada taula agrupa exactament les dades que depenen d'una mateixa cosa.

Resum memoritzable de les tres formes normals:

La regla mnemotècnica clàssica: "cada atribut no clau ha de dependre de la clau, de tota la clau i de res més que la clau". La primera part és 1FN/2FN, "de tota la clau" és 2FN i "de res més que la clau" és 3FN.

Hi ha formes normals superiors (BCNF, 4FN, 5FN) que resolen casos més estranys. A la pràctica professional, arribar a 3FN cobreix el 95 % dels dissenys.

La normalització optimitza la integritat i l'escriptura. De vegades es paga un preu en velocitat de lectura, perquè reconstruir una factura obliga a combinar cinc taules. Desnormalitzar és introduir redundància conscientment a canvi de rendiment o de correcció històrica.

Casos legítims, amb exemples de BotigaVerda:

I la regla d'or:

Normalitza primer. Desnormalitza després, amb mesures a la mà, i documenta per què.

Desnormalitzar sense mesurar és la causa número u de bases de dades incoherents. Cada dada duplicada és una dada que pot quedar desincronitzada, i necessitaràs un mecanisme explícit (disparador, procés batch, lògica d'aplicació) per mantenir-la al dia. Abans de desnormalitzar, prova amb un índex (mòdul 8) o una vista materialitzada (mòdul 10): solen resoldre el problema sense cost d'integritat.

• Confondre "relació" amb "relació entre taules". En el model de Codd, una relació és una taula.
• Posar la FK al costat equivocat d'una 1:N. Sempre va al costat "molts". Si la poses a l'"un", limites la relació a un únic fill.
• Intentar fer una N:M sense taula pont. Desar "3,7,12" en una columna productes_ids viola la 1FN, impedeix les FK i fa les consultes impossibles.
• Fer servir CASCADE per comoditat. Un DELETE es pot propagar molt més lluny del que et penses. Reserva CASCADE per a relacions de composició real.
• Esborrar productes venuts. Destrueix l'històric i salta la FK. Fes servir actiu = FALSE (esborrat lògic); per això existeix la columna.
• Oblidar el UNIQUE de la clau natural. Amb id subrogat com a PK, res no impedeix duplicar l'email d'un client si no el declares UNIQUE.
• Sobrenormalitzar. Partir una taula en set per purisme acadèmic complica cada consulta sense aportar integritat real.
• Desnormalitzar "per si de cas". Sense un mesurament que ho justifiqui, només estàs creant incoherències futures.
• Consell: indexa les teves claus foranes. PostgreSQL crea índex automàticament per a la PK, però no per a les FK. Sense aquest índex, els JOIN i els esborrats en cascada poden ser molt lents (mòdul 8).
• Consell: dibuixa el diagrama abans d'escriure DDL. Deu minuts d'esquema en paper eviten setmanes de migracions.
• Consell: anomena les FK amb el patró <taula_singular>_id. client_id, producte_id, comanda_id. La consistència fa que les consultes s'escriguin gairebé soles.

Per a cada parell de taules de BotigaVerda, indica la cardinalitat (1:1, 1:N o N:M), on va la clau forana i quina acció ON DELETE triaries, justificant-la:

1. proveidors i productes
2. clients i ressenyes
3. comandes i devolucions
4. clients i productes (a través de ressenyes)
5. empleats amb si mateixa

Aquesta taula viola les tres formes normals. Identifica quina regla trenca en cada cas i descompon-la en taules normalitzades fins a 3FN, indicant claus primàries i foranes.

Prediu què respon PostgreSQL a cadascuna d'aquestes operacions sobre la base de BotigaVerda ja carregada, i explica per què:

Solució 1

Solució 2

Violacions:

Descomposició en 3FN:

I així queda:

Fixa't que la llista "5, 4" de la primera fila es converteix en dues ressenyes diferents: la 1FN ens ha obligat a descobrir que allà hi havia realment dos fets, no un.

Solució 3

a) Falla:

BotigaVerda té 20 productes, així que el 99 no existeix. La integritat referencial impedeix crear una ressenya òrfena.

b) Falla:

El producte 1 (Oli d'oliva verge extra) apareix en diverses línies de comanda, i aquesta FK està declarada RESTRICT precisament per protegir l'històric de facturació. Per retirar-lo del catàleg es fa UPDATE productes SET actiu = FALSE WHERE id = 1;.

c) Funciona, i esborra més del que sembla:

S'elimina la comanda 20 i, en cascada, les seves dues línies de comanda (linies_comanda.comanda_id està declarada ON DELETE CASCADE). És l'exemple perfecte de per què CASCADE s'ha de fer servir amb cura: una sola sentència ha esborrat tres files en dues taules. Si la comanda tingués devolucions associades, també desapareixerien.

d) Funciona:

L'empleat 4 (Óscar Peris Blasco, comercial) passa a no tenir cap assignat. La columna cap_id admet NULL per disseny, així que no es viola cap restricció. Ara hi hauria dos empleats amb cap_id IS NULL: la directora general (que ho és per naturalesa) i aquest comercial (que ho és per un canvi organitzatiu). És un bon recordatori que NULL pot significar coses diferents en files diferents, i de per què convé documentar-ne la semàntica.

Aquesta lliçó explica el perquè de l'esquema que carregaràs a continuació:

• El model relacional organitza les dades en relacions (taules) de tuples (files) amb atributs (columnes) sobre dominis (tipus), i connecta la informació per valor, no per punters: d'aquí neixen els JOIN.
• La clau primària identifica cada fila de manera única, no nul·la i estable. BotigaVerda fa servir id subrogat a les nou taules per uniformitat, estabilitat i eficiència, sense renunciar a declarar UNIQUE les claus naturals com clients.email.
• La clau forana garanteix la integritat referencial: PostgreSQL rebutja amb violates foreign key constraint tant inserir una referència inexistent com esborrar un pare referenciat.
• Les accions ON DELETE (RESTRICT, CASCADE, SET NULL) es trien segons el significat de negoci: CASCADE per a linies_comanda, SET NULL per a comandes.empleat_id, RESTRICT per a productes venuts.
• Les cardinalitats 1:N (FK al costat "molts"), N:M (taula pont com linies_comanda, amb dades pròpies) i les relacions reflexives d'empleats.cap_id i clients.referit_per_id.
• La normalització fins a 3FN, deduïda a partir d'una taula de comandes monolítica, explica per què l'esquema té nou taules; i la desnormalització deliberada justifica que linies_comanda.preu_unitari conservi el preu històric.

A la lliçó següent, La base de dades del curs: BotigaVerda, tot això es fa tangible: veuràs el diagrama entitat-relació complet, la descripció taula per taula amb les seves columnes i tipus, i l'script SQL a punt per copiar que crea les nou taules i carrega les dades que faràs servir durant els onze mòduls restants. En acabar-la tindràs la base de dades funcionant al teu equip, i a partir del mòdul 2 començaràs a consultar-la de debò.