Tornem a mirar el plànol complet, tal com vam prometre en tancar el mòdul 7. Durant set mòduls has construït BiblioRed a base de peces: una taula aquí, un JOIN allà, un índex quan alguna cosa anava lenta. Cada peça s'explicava per separat perquè calia aprendre-la per separat. En un projecte real ningú no et lliura les peces ordenades: et lliuren una conversa amb un client i has d'arribar tu sol des d'allà fins a un sistema en producció.

Això és el que fa aquesta lliçó. L'ajuntament de Vallmar, satisfet amb la xarxa de biblioteques, acaba d'encarregar un segon sistema: VallBici, el servei municipal de bicicletes compartides. No és una ampliació de BiblioRed; és un domini nou, amb les seves pròpies regles, els seus propis problemes de concurrència i els seus propis informes. BiblioRed apareixerà de tant en tant com a terme de comparació —«això ja ho vam resoldre així, aquí canvia perquè…»—, però la feina és nova de cap a peus.

El recorregut és el d'un projecte real i en el seu ordre real: l'encàrrec, els requisits escrits, el model conceptual, l'esquema físic, la comprovació de normalització, la càrrega de dades, les transaccions crítiques, els informes, el rendiment i l'operació. A cada punt on cal decidir alguna cosa, la decisió va acompanyada de la seva justificació i de les alternatives descartades. Aquest és el contingut de la lliçó: no el SQL final —que es copia en deu minuts— sinó el raonament que hi porta.

I acaba on ha d'acabar un cas honest: enumerant les tres coses que aquest esquema resol malament, que són exactament el material de la lliçó 08-02.

1. L'encàrrec: la conversa inicial
2. Document de requisits VallBici v1.0
3. Model conceptual: entitats, relacions i tres decisions difícils
4. El diagrama ER
5. Esquema físic: el CREATE TABLE comentat
6. Les restriccions que codifiquen les regles de negoci
7. Comprovació de normalització i dues desnormalitzacions deliberades
8. Càrrega de dades i volum realista
9. Les dues transaccions crítiques: desbloquejar i ancorar
10. Consultes d'explotació: els informes de l'ajuntament
11. Rendiment: les dues consultes que es degraden
12. Operació: rols, dades personals i còpies
13. Què resol malament aquest esquema
14. Errors Habituals i Consells
15. Exercicis
16. Conclusió

La primera reunió amb l'àrea de Mobilitat de l'ajuntament produeix això, transcrit gairebé literalment:

«Tenim 60 estacions repartides pels cinc districtes de la ciutat i 900 bicicletes, unes de mecàniques i unes altres d'elèctriques. Cada estació té un nombre fix d'ancoratges; una bici ocupa un ancoratge quan està aparcada. Hi ha 24.000 persones abonades. Una persona desbloqueja una bici en una estació, fa el seu trajecte i l'ancora en una altra. Hi ha tres tipus d'abonament —anual, mensual i turístic de tres dies— i si et passes del temps inclòs es cobra un recàrrec. Les bicis passen revisions, s'avarien i es retiren al taller. L'aplicació del mòbil ha de mostrar la disponibilitat en temps real, recordar la sessió, deixar buscar estacions per nom o per adreça i guardar el rastre GPS de cada trajecte. I volem informes mensuals d'ús per districte i franja horària.»

Apliquem la tècnica de 04-01: subratllar els substantius (candidats a entitat), els verbs (candidats a relació) i les frases amb "no pot", "sempre", "cada" (candidates a restricció). Però abans cal fer una cosa que a 04-01 vam insistir molt: tornar a preguntar. Un encàrrec de dos paràgrafs sempre amaga decisions que el client dona per evidents i no ho són.

Aquestes set respostes valen més que les cent línies de SQL següents. La quarta, en particular, és la que evita l'error car: si la tarifa no es congela, el primer canvi de preus reescriu la facturació de dos anys.

Aquesta llista és part del requisit, no un extra. És el que a 04-01 anomenàvem el criteri d'èxit de l'esquema: un model que no les pot respondre és un model fallit, per elegant que sigui.

• C1 — Trajectes per districte i franja horària, mes a mes.
• C2 — Els deu parells d'estacions origen→destí més freqüents.
• C3 — Estacions que es buiden o s'omplen sistemàticament, per franja (el problema del reequilibratge: és el que costa diners de debò, perquè obliga a moure bicis en furgoneta).
• C4 — Ingressos per tipus d'abonament, separant quotes de recàrrecs.
• C5 — Bicicletes amb més avaries per hora d'ús (no en termes absoluts: una bici molt usada s'avaria més i això no la fa dolenta).

Del text surten sense discussió: districte, estació, ancoratge, bicicleta, model de bicicleta, persona abonada, tipus d'abonament, tarifa, abonament, trajecte, ordre de taller i cobrament. El que sí que té discussió són tres decisions, i són les que separen un model que aguanta d'un que no.

La temptació: guardar a estacions una columna num_ancoratges i una altra bicis_disponibles, i no modelar l'ancoratge. És més simple i aparentment suficient: per pintar l'app n'hi ha prou de saber quantes bicis hi ha.

Per què es descarta. Tres motius, en ordre de pes:

1. El client va dir que un ancoratge es pot avariar tot sol. Un atribut no té estat; una entitat sí. Sense ancoratges, una estació amb 20 ancoratges i 3 de trencats continua "tenint capacitat 20" i el sistema promet places que no existeixen.
2. L'app ha de dir a la persona en quin ancoratge és la bici que ha reservat i en quin l'ha de deixar. Aquesta dada no existeix si l'ancoratge no existeix.
3. RN1 i RN2 —"un ancoratge, una bici; una bici, un ancoratge"— són restriccions d'integritat, i al mòdul 4 vam fixar el principi: una restricció que l'esquema pot imposar no es delega al codi. Amb ancoratges com a taula són una clau primària i un UNIQUE; sense ella, són codi d'aplicació i confiança.

Com es modela: l'ancoratge és una entitat feble de l'estació (regla 8 de 04-03), amb clau primària composta (estacio_id, numero). El número 7 només significa alguna cosa dins d'una estació concreta, exactament igual que el número de rebut d'una multa depenia de la multa a BiblioRed.

Un trajecte connecta una persona (via el seu abonament), una bicicleta i dues estacions. En termes de 04-02 seria una relació de grau 4, i les relacions de grau alt són gairebé sempre un símptoma que falta una entitat.

Es modela com a entitat, i per quatre raons:

1. Té atributs propis i abundants: dos instants, durada, import, tarifa congelada.
2. Té identitat: la mateixa persona pot fer el mateix trajecte entre les mateixes dues estacions amb la mateixa bici dues vegades el mateix dia, i són dos fets diferents. Una taula d'unió amb clau composta els confondria.
3. Neix incomplet. Quan es desbloqueja la bici només es coneix la meitat del trajecte. Una relació que existeix a mitges és una entitat amb columnes nul·les, no una relació.
4. Altres coses el referencien: el cobrament del recàrrec apunta al trajecte.

Les dues estacions són dues relacions 1:N diferents cap a estacions (regla 5 de 04-03): estacio_origen i estacio_desti. No és un cas rar: és el mateix patró que "vol amb aeroport de sortida i d'arribada", i l'única precaució és no oblidar que les dues claus foranes apunten a la mateixa taula, cosa que obliga a posar àlies a totes les consultes que les facin servir.

Detall fi: la clau forana no apunta a estacions sinó a ancoratges(estacio_id, numero), perquè ens interessa saber de quin ancoratge concret va sortir i en quin va entrar. L'estació queda determinada per l'ancoratge.

Tota bicicleta és mecànica o elèctrica (jerarquia total i disjunta). Les elèctriques tenen tres atributs que a les mecàniques no tenen sentit: capacitat de bateria, autonomia i número de sèrie de la bateria. A 04-03, regla 10, vam veure les tres estratègies. Repassem la decisió amb els criteris d'allà:

Triem taula per subclasse (estratègia 2), igual que vam fer amb els materials de BiblioRed i pel mateix motiu dominant: hi ha taules que referencien la superclasse. trajectes, ancoratges i ordres_taller apunten a "una bicicleta", sense importar de quin tipus, i l'estratègia 3 faria això impossible sense duplicar totes les claus foranes.

L'estratègia 1 (taula única) era defensable —només són tres columnes— i amb 900 files el malbaratament és irrellevant. Es descarta pel quart criteri: n_serie_bateria ha de ser obligatori i únic en les elèctriques, i en una taula única només podria ser opcional. És exactament l'argument que a BiblioRed ens va fer separar materials_llibre per l'isbn.

I com a 04-03, l'estratègia 2 arrossega el seu forat conegut: res no impedeix per si sol que una bici amb tipus = 'mecanica' tingui fila a bicicletes_electriques. Es tapa amb el truc de la clau forana discriminada, que veuràs a l'esquema.

Llegeix-lo amb la notació de 04-02: || és participació total i cardinalitat 1, o{ és cardinalitat N amb participació parcial, |{ és N amb participació total. Que ESTACIO ||--|{ ANCORATGE sigui total als dos costats diu una cosa certa i no trivial: una estació sense cap ancoratge no és una estació, i un ancoratge sense estació no existeix. Que ANCORATGE |o--o| BICICLETA sigui parcial en tots dos costats diu el contrari: hi ha ancoratges buits i hi ha bicis fora de tot ancoratge (circulant o al taller).

Dues extensions abans de començar. btree_gist permet barrejar en un EXCLUDE columnes d'igualtat (un enter) amb columnes de solapament (un rang), que és just el que demanen RN3 i les tarifes.

Per què aquests tipus, un a un:

El truc del discriminant, explicat. La clau forana composta (bicicleta_id, tipus) obliga que la fila referenciada de bicicletes tingui tipus = 'electrica', perquè el CHECK d'aquesta taula fixa tipus a aquell valor. Resultat: és impossible donar d'alta una bateria per a una bici mecànica. El forat que 04-03 deixava obert a l'estratègia 2 queda tapat en la meitat que importa. L'altra meitat —que una elèctrica no tingui fila aquí— continua sense poder-se imposar declarativament i es controla en el procés d'alta.

Les dues regles més importants del sistema són dues línies d'esquema. La clau primària composta impedeix que l'ancoratge 7 de l'estació 12 allotgi dues bicis. L'UNIQUE (bicicleta_id) impedeix que la bici 417 sigui simultàniament en dos ancoratges; funciona perquè a PostgreSQL UNIQUE admet tants NULL com vulgui, així que tots els ancoratges buits conviuen sense conflicte.

ON DELETE CASCADE d'ancoratges cap a estacions està justificat: un ancoratge no té vida pròpia fora de la seva estació (és l'acció que 02-06 reservava precisament per a entitats febles). ON DELETE SET NULL cap a bicicletes és el correcte per al contrari: si una bici desaparegués del sistema, l'ancoratge ha de quedar lliure, no desaparèixer.

Sobre NUMERIC per als diners no hi ha debat i convé repetir-ho perquè és l'error més car que es comet amb els tipus: FLOAT no representa exactament 0,10, i un sistema que suma 1,6 milions d'imports amb FLOAT produeix un descompte comptable que ningú no sabrà explicar. NUMERIC(6,2) dona fins a 9.999,99 €, de sobres per a una quota anual.

DATERANGE en lloc de dues columnes data_inici/data_fi és el que fa possible l'EXCLUDE. Amb dues columnes soltes, "aquests dos abonaments se solapen" és una consulta amb quatre comparacions que cal escriure bé cada vegada; amb un rang és l'operador && i ho imposa el motor.

Tres tipus que mereixen un comentari:

• TIMESTAMPTZ, no TIMESTAMP. Vallmar canvia d'hora dues vegades l'any. El diumenge d'octubre en què els rellotges endarrereixen, un TIMESTAMP sense zona converteix "02:30" en un instant ambigu, i els trajectes d'aquella matinada poden sortir amb durada negativa. TIMESTAMPTZ guarda un instant absolut i l'ambigüitat desapareix. El preu és recordar-se de convertir a hora local quan s'agrupa per franja horària, cosa que farem explícitament a C1.
• INTERVAL generada. durada no s'emmagatzema a mà: és una columna generada (04-04). Compleix la regla 4 de 04-03 —els derivats no s'emmagatzemen— sense renunciar a poder indexar-la, perquè STORED sí que ocupa disc però es calcula sola i mai no pot discrepar de les seves fonts.
• BIGINT a trajecte_id. Amb 1,6 milions de trajectes l'any, INTEGER (2.147 milions) trigaria més de mil anys a esgotar-se. Tot i així es fa servir BIGINT: canviar el tipus d'una clau primària en producció és una de les migracions més doloroses que hi ha, i el cost avui són quatre bytes per fila.

ck_origen_del_cobrament és un exemple del patró que 04-04 anomenava restricció de coherència entre columnes: no n'hi ha prou que cada columna sigui vàlida per separat; la combinació ha de tenir sentit. Un cobrament de concepte abonament amb trajecte_id emplenat és una dada incoherent, i l'esquema la rebutja.

Val la pena veure les tres més interessants fallant, perquè d'una restricció que mai no has vist saltar no saps si funciona.

Aquest últim és el més valuós dels tres. La regla "una bici no pot estar en dos trajectes oberts" sembla codi d'aplicació pur, i en el 90 % dels sistemes ho és —amb el resultat que, sota càrrega, dues peticions simultànies la violen alegrement—. Un índex únic parcial la converteix en una garantia del motor que cap condició de cursa no pot burlar.

Repassem l'esquema amb el mètode de 05-03. Per a cada taula: identificar la clau, llistar les dependències funcionals i comprovar que tot atribut no primer depèn de la clau completa i de res més.

La nota sobre bicicletes.tipus. Existeix la dependència model_id → tipus, i model_id no és clau: és una dependència transitiva i per tant una violació de 3FN de manual. Es conserva per una raó concreta i verificable: és la columna que fa funcionar el discriminant de la jerarquia, i una clau forana no pot apuntar a un valor que s'ha d'anar a buscar a una altra taula. I no genera anomalies, perquè un model no canvia de tipus mai: una bici mecànica no es converteix en elèctrica. És el cas que 05-02 descrivia com a "dependència transitiva sobre un atribut immutable", on el risc d'anomalia d'actualització és zero. Tot i així, es blinda amb un activador de verificació en l'alta.

La nota sobre abonaments.tipus_abonament. Mateix raonament i mateixa conclusió: tarifa_id → tipus_abonament. Es conserva perquè les consultes d'explotació agrupen per tipus d'abonament constantment i evitar un JOIN en el 80 % dels informes ho justifica. Es blinda amb una clau forana composta:

Ara la redundància és impossible de contradir, que és l'única forma acceptable de conviure amb una redundància. És exactament el mateix patró del discriminant de les bicis.

Què es trenca: la dada és derivable amb SELECT COUNT(*) FROM ancoratges WHERE estacio_id = ? AND bicicleta_id IS NOT NULL.

Per què s'accepta: l'app mòbil demana la disponibilitat de les 60 estacions cada vegada que algú obre el mapa, i amb 24.000 persones abonades això són desenes de milers de peticions al dia que degeneren en un recompte sobre ancoratges cadascuna. És el cas de llibre de 05-04: lectura massiva, escriptura poc freqüent, dada petita.

Com es manté: amb un activador sobre ancoratges, no a mà des de l'aplicació. Que ho mantingui l'aplicació és el que garanteix que algun dia discrepi.

I, com mana 05-04, una consulta d'auditoria que s'executa cada nit i avisa si el comptador ha derivat:

Què es trenca: minuts_inclosos, minuts_fraccio, preu_fraccio i recarrec_electrica depenen de tarifa_id, no de trajecte_id.

Per què s'accepta: no és rendiment, és correcció. És la resposta a la quarta pregunta de la reunió inicial. Si el trajecte només guardés tarifa_id, qualsevol recàlcul posterior aplicaria la tarifa actual; el dia que l'ajuntament apugi el preu de la fracció, tot l'històric canviaria d'import. Les dades històriques han de ser reproduïbles: és un requisit comptable i de vegades legal.

Com es manté: copiant la tarifa en el moment del desbloqueig, dins de la transacció, i no tornant-la a tocar mai. Un activador BEFORE UPDATE que rebutgi canvis en aquelles quatre columnes és una protecció barata i assenyada.

Fixa't en la diferència entre les dues: la primera es desnormalitza per velocitat i cal vigilar-la; la segona es desnormalitza per semàntica i vigilar-la seria un error, perquè el seu valor ha de diferir de l'actual.

Aquest 1.512 és la primera dada de dimensionament útil: 1.512 ancoratges per a 900 bicicletes, un 68 % d'ocupació mitjana. És una folgança sana; per sota del 80 % el reequilibratge es torna un malson operatiu.

Tota decisió de rendiment de l'apartat 11 es refereix a aquests 1,6 milions anuals de trajectes. Les altres taules són irrellevants a efectes de pla d'execució, i confondre això és la manera més habitual de perdre una tarda indexant el que no toca.

Aquí és on el mòdul 6 deixa de ser teoria. L'escenari que cal resoldre és concret: són les 08:12, l'estació del Port té una sola bici lliure i dues persones premen "desbloquejar" amb 40 mil·lisegons de diferència.

Per què SKIP LOCKED i no FOR UPDATE a seques. Amb FOR UPDATE, la sessió B es queda esperant que A confirmi, i després reavalua: com que l'ancoratge ja no té bici, obté 0 files. El resultat final és correcte, però B ha esperat sense necessitat. Amb SKIP LOCKED, B ignora la fila bloquejada i continua buscant una altra bici a la mateixa estació; només si de debò no en queda cap retorna 0 files. En una estació amb 8 bicis i 8 persones desbloquejant alhora, la diferència és que les 8 ho aconsegueixen en paral·lel en comptes de fer cua. És exactament el patró de cua que vam veure a 06-02, aplicat a un inventari.

Per què FOR UPDATE OF a. Sense l'OF a, PostgreSQL bloquejaria també la fila de bicicletes, i no cal: la fila que decideix qui guanya és la de l'ancoratge. Bloquejar de més multiplica els interbloqueigs.

El nivell d'aïllament és READ COMMITTED, el predeterminat. No cal pujar a REPEATABLE READ: no hi ha cap lectura que s'hagi de repetir de manera estable, i el bloqueig explícit ja resol la cursa. Pujar l'aïllament "per si de cas" només afegeix errors de serialització que caldria reintentar.

Un trajecte de 68 minuts amb abonament anual: 68 − 30 inclosos = 38 minuts excedits, ceil(38/15) = 3 fraccions × 0,60 € = 1,80 €… més el recàrrec d'elèctrica 0,35 €. El resultat de dalt, 1,55 €, correspon a 2 fraccions (1,20 €) més 0,35 €, és a dir a un trajecte de 55 minuts. Comprova tu el càlcul amb els dos casos: verificar a mà el primer import que produeix un sistema de cobrament és un costum que estalvia disgustos.

Els tres detalls que fan robusta aquesta transacció:

1. AND t.ts_fi IS NULL al WHERE de l'UPDATE la fa idempotent. Si l'app reintenta l'ancoratge perquè va perdre la resposta per xarxa, el segon intent afecta 0 files i no torna a cobrar.
2. El RETURNING permet a l'aplicació comprovar quantes files va canviar. Zero files no és un èxit silenciós: és un error que cal tractar.
3. El comptador bicis_disponibles no es toca aquí: l'actualitza l'activador d'ancoratges. Si es toqués també a mà, el comptador pujaria de dos en dos, i aquesta és la fallada de desnormalització més comuna que hi ha.

C1 — Trajectes per districte i franja horària.

Allà s'hi llegeix un patró real: l'Eixample i l'Industrial són clarament pendulars (dues puntes, poca vall) mentre que el Port té el seu màxim a la vall — és turisme, no desplaçament a la feina. L'AT TIME ZONE 'Europe/Madrid' no és decoratiu: sense ell, a l'estiu les franges sortirien desplaçades dues hores.

C2 — Els parells origen→destí més freqüents, amb el seu rànquing dins del districte.

El RANK() OVER (PARTITION BY ...) sobre un COUNT(*) agregat és el patró "top N per grup" que vas practicar a 07-04. Nota que la funció de finestra s'avalua després del GROUP BY, per això pot ordenar per COUNT(*).

C3 — El problema del reequilibratge. Aquesta és la consulta que estalvia diners de debò, i la que millor demostra per què vam modelar el trajecte com a entitat amb dos extrems.

Els signes expliquen la història sencera: la 7 i la 41 són residencials (es buiden al matí, es reomplen a la tarda) i la 22 és un destí de feina. La columna net_mes diu quantes bicis cal moure en furgoneta cada mes, i el 08-12 davant del 16-20 diu a quina hora s'han de moure. La tècnica —convertir dues columnes d'una fila en dues files amb signe mitjançant UNION ALL— és la forma canònica de tractar qualsevol entitat amb dos extrems.

C4 — Ingressos per tipus d'abonament.

El pct_recarrec de l'abonament turístic (36,9 %) és la mena de troballa que justifica l'informe: les persones visitants es passen del temps inclòs gairebé la meitat de les vegades. O bé 15 minuts són pocs, o bé l'app no avisa. És una conversa de negoci que només existeix perquè la consulta la va fer possible.

C5 — Avaries per hora d'ús.

El WHERE u.hores >= 50 és la part important i la que gairebé ningú no posa: sense ell, una bici amb 2 hores d'ús i una avaria encapçala la llista amb 50 avaries/100 h i l'informe no té cap valor. Tota taxa necessita un llindar mínim de denominador.

Amb la taula buida tot va ràpid. El dia que trajectes té 1,6 milions de files, dues coses es trenquen. Seguim l'ordre d'intervenció de 06-03: mesurar primer, entendre el pla, i només llavors tocar.

Diagnòstic: Rows Removed by Filter: 488153 per a cadascun dels 3 processos. Es llegeixen 1,6 milions de files per quedar-se amb 45.180. És el símptoma clàssic de 06-03: un filtre molt selectiu (2,8 %) resolt amb recorregut seqüencial.

Intervenció. Aquí hi ha una elecció real entre dos índexs:

trajectes s'insereix sempre en ordre cronològic i mai no es reordena: la correlació física de ts_inici és pràcticament 1. És el cas exacte per al qual existeix BRIN.

De 2.189 ms a 119 ms, amb un índex de 48 KB. Rows Removed by Index Recheck: 2841 és normal en BRIN: l'índex treballa per blocs, així que retorna algun bloc de més i el motor descarta les files sobrants. A canvi ocupa mil vegades menys que el B-tree i el seu manteniment en les insercions és menyspreable.

És la consulta més freqüent del sistema: l'executa l'app cada vegada que algú l'obre amb una bici en curs.

I aquí hi ha el detall bonic: l'índex que ho arregla ja existeix. És uq_trajecte_obert_abon, l'índex únic parcial que vam crear a l'apartat 5 per imposar RN5. Un índex parcial sobre WHERE ts_fi IS NULL cobreix, amb 1,6 milions de files a la taula, només les 900 com a màxim que poden estar obertes simultàniament. L'única cosa que faltava era un ANALYZE:

De 1.893 ms a 0,049 ms. La lliçó és la que 06-03 repetia: una restricció ben triada és també un índex, i moltes vegades l'índex que necessites ja l'has creat sense adonar-te'n.

Els tres índexs restants que sí que cal crear a mà:

L'ordre de les columnes en els compostos segueix la regla de 06-03 —igualtat primer, rang després— i respon a C2 i C3. El segon és parcial perquè els trajectes oberts no tenen destí i no aporten res a l'índex.

El GRANT SELECT, UPDATE (estat) ON bicicletes és l'aplicació literal del principi de mínim privilegi de 06-04: el taller canvia l'estat d'una bici, i només l'estat. No pot tocar km_acumulats ni matricula.

Els informes de l'ajuntament no necessiten saber qui va fer cada trajecte. La vista que consumeix el perfil analista trenca el vincle:

Dues observacions honestes sobre això:

1. La vista no anonimitza de debò. Amb districte de residència, dècada d'edat i patró de trajectes, una persona amb recorregut singular pot ser reidentificable. Reduir el risc requereix agregació mínima (no publicar cel·les amb menys de N persones) i això és una decisió de política, no de SQL.
2. El compliment normatiu no el decideix qui dissenya la base de dades. Base legal del tractament, terminis de conservació de la telemetria, avaluació d'impacte, dret de supressió: tot això ho revisa un professional de protecció de dades o compliance. El que sí que és responsabilitat tècnica és que l'esquema permeti complir-lo: per això el document es guarda com a document_hash i no en clar, per això ON DELETE RESTRICT obliga a un procediment explícit de supressió en lloc d'esborrar en cascada l'històric comptable, i per això existeix la vista.

L'última fila és la que més se salta i l'única que garanteix alguna cosa. En paraules de 06-04: una còpia que no s'ha restaurat mai no és una còpia, és una esperança. Amb PITR configurat, l'escenari "a les 11:40 algú va executar un UPDATE sense WHERE sobre tarifes" es resol restaurant a les 11:39.

El sistema funciona, compleix els vuit requisits funcionals i respon a les cinc consultes. I tot i així hi ha tres coses de l'encàrrec inicial que aquest esquema resol malament. Val la pena ser precisos, perquè són exactament el material de la lliçó següent.

1. La telemetria GPS. L'encàrrec demanava guardar el rastre de cada trajecte: posicions cada 5 segons, bateria de les elèctriques, incidències. Un trajecte mitjà de 14 minuts són ~170 punts. Amb 1,6 milions de trajectes anuals, això és una taula posicions de 272 milions de files l'any, cadascuna amb trajecte_id, ts, lat, lon i poca cosa més. Es pot fer —PostgreSQL ho aguanta— però és un mal encaix: són dades que s'escriuen massivament, es llegeixen sempre senceres i per trajecte, no s'actualitzen mai, no necessiten integritat referencial estricta i caduquen al cap de pocs mesos. Estàs pagant el preu del model relacional (índex per fila, WAL per fila, visibilitat per fila) per a una dada que no fa servir cap dels seus avantatges.

2. La fitxa enriquida de l'estació. La nostra taula estacions té 11 columnes planes. La fitxa que vol l'app té fotos, horaris d'accés que varien per estació, accessibilitat, si està sota coberta, si té bomba d'aire, notes de l'empresa mantenidora, etiquetes de punts d'interès propers… i cada estació té un subconjunt diferent d'aquests atributs. Modelar-ho en el relacional porta a una de tres sortides, totes dolentes: 40 columnes nul·les, una taula clau-valor genèrica (l'antipatró EAV que 04-01 va marcar en vermell) o una taula nova per cada atribut que se li acudeixi a algú.

3. Les incidències amb estructura variable. ordres_taller té un motiu VARCHAR(60). Però una incidència de "frens" necessita registrar quin fre i quina mesura; una de "bateria" necessita cicles de càrrega i voltatge; una de "vandalisme" necessita fotos i número d'atestat. Són estructures diferents per tipus, i afegir un tipus nou no hauria de requerir un ALTER TABLE en producció.

Les tres tenen una cosa en comú: estructura variable o volum alt sense necessitat de transaccions. I les tres tenen una altra cosa en comú, encara més important: cap d'elles no és el nucli transaccional. Ningú no cobra diners a partir d'un punt GPS. Això és el que permet treure-les de PostgreSQL sense posar en risc el que importa, i és just el que fa la lliçó 08-02.

Error 1: començar pel CREATE TABLE. És l'error del qual es deriven gairebé tots els altres. Les set preguntes de l'apartat 1 van costar vint minuts de reunió i van determinar la meitat de l'esquema. Si no les haguéssim fet, hauríem descobert la necessitat de la tarifa congelada el dia de la primera pujada de preus, amb dos anys de facturació ja emesa.

Error 2: no modelar l'ancoratge "perquè amb un comptador n'hi ha prou". Funciona fins al primer ancoratge avariat. La regla general: si el client diu que alguna cosa té estat propi, és una entitat, encara que sembli un número.

Error 3: FLOAT per als diners. Continua passant. NUMERIC per a tot el que es cobra, es factura o se suma.

Error 4: TIMESTAMP sense zona en un sistema amb canvi horari. El diumenge d'octubre produirà trajectes de durada negativa i la restauració d'aquella nit serà un trencaclosques.

Error 5: mantenir a mà un comptador desnormalitzat. Si bicis_disponibles s'actualitza des de l'aplicació i des de l'activador, es duplica l'increment. Un dels dos, i millor l'activador.

Error 6: pujar el nivell d'aïllament en comptes de bloquejar la fila correcta. SERIALIZABLE no és una solució màgica: converteix una cursa en un error de serialització que l'aplicació ha de reintentar. Un FOR UPDATE SKIP LOCKED sobre la fila que decideix és més barat i més predictible.

Consell 1: escriu la llista de consultes abans que l'esquema. És el millor detector d'entitats que falten. C3 va ser la que va confirmar que el trajecte necessitava els seus dos extrems com a claus foranes independents.

Consell 2: cada restricció que escriguis, prova-la fallant. D'una restricció que mai no has vist rebutjar un INSERT no saps si està ben escrita.

Consell 3: mira si l'índex ja existeix abans de crear-lo. La degradació 2 es va resoldre sola. Els índexs únics, parcials o no, són índexs complets.

Consell 4: documenta les desnormalitzacions en el mateix esquema. El comentari -- Desnormalització deliberada núm. 1 evita que d'aquí a tres anys algú "arregli" l'esquema traient-la. Millor encara: COMMENT ON COLUMN.

L'ajuntament vol permetre reservar una bicicleta des de l'app: la persona reserva una bici concreta d'una estació i té 10 minuts per arribar-hi i desbloquejar-la; passat aquest temps, la reserva caduca i la bici torna a estar disponible. Una persona només pot tenir una reserva activa.

1. Decideix si la reserva és una entitat nova o un estat d'alguna cosa existent, i justifica-ho.
2. Escriu el CREATE TABLE (o l'ALTER TABLE) amb totes les restriccions necessàries.
3. Escriu la transacció de reserva, amb el seu control de concurrència.
4. Explica com caduquen les reserves i per què aquesta decisió.

S'introdueix una tarifa reduïda per a persones amb bo social municipal. La condició s'acredita cada any i pot deixar de complir-se. Un trajecte es cobra amb la tarifa reduïda si la persona tenia el bo social vigent en el moment del desbloqueig.

1. Modela l'acreditació del bo social sense duplicar la informació de persones_abonades.
2. Explica per què la restricció EXCLUDE d'abonaments no n'hi ha prou aquí.
3. Escriu la consulta que calcula, per al mes passat, quant ha deixat d'ingressar l'ajuntament per les tarifes reduïdes.

Una bicicleta fantasma és la que porta més de 48 hores en un trajecte obert: l'han robat, s'ha trencat el sistema d'ancoratge o l'app ha fallat en tancar-lo. Escriu la consulta que les llista amb l'última estació coneguda, quant temps porten i el nom de contacte de la persona que la va desbloquejar, ordenades de més antiga a més recent. Afegeix l'índex que la faci eficient, o explica per què no en cal cap.

Solució 1

1. Entitat nova. Tres arguments: (a) té atributs propis —instant de reserva, instant de caducitat, resultat—; (b) té historial: voldrem saber quantes reserves caduquen sense fer-se servir, i un estat sobreescrit no deixa rastre; (c) relaciona tres coses (persona, bici i ancoratge) amb temporalitat pròpia. Un estat = 'reservada' a bicicletes no permetria cap de les tres.

2. L'esquema:

Els dos índexs únics parcials imposen "una reserva activa per persona" i "una reserva activa per bici" — el mateix patró que RN4/RN5. El tercer és de servei, per al procés de caducitat.

3. La transacció:

L'anti-join NOT EXISTS exclou les bicis ja reservades; l'índex únic parcial és la xarxa de seguretat si dues peticions l'esquiven. Fixa't que no es marca la bici com a reservada a bicicletes: l'estat viu a reserves, en un sol lloc.

4. La caducitat. Dues opcions i una de triada.

Descartada: un procés que cada minut marqui com a caducada les vençudes. Funciona, però introdueix una finestra en què la reserva està vençuda i encara figura activa, i depèn que el procés no caigui.

Triada: caducitat implícita en la lectura. La reserva es considera activa si resultat = 'activa' AND ts_limit > now(); el NOT EXISTS de dalt hi afegeix aquesta condició. El procés de neteja continua existint, però només per deixar l'històric ordenat, i que es retardi no afecta la correcció. La regla general: quan el temps determina la validesa d'una dada, la veritat ha d'estar a la consulta, no en un procés.

Solució 2

1. El model. Una taula d'acreditacions amb vigència, no una columna booleana a persones_abonades:

Una columna te_bo_social BOOLEAN seria l'error clàssic: no guarda des de quan, no guarda l'expedient i, sobretot, no permet reconstruir si en tenia el dia del trajecte. L'acreditació és un fet amb data, i els fets amb data van en files, no en columnes.

2. Per què l'EXCLUDE d'abonaments no n'hi ha prou. Aquell EXCLUDE garanteix que no hi hagi dos abonaments solapats de la mateixa persona; no diu res sobre quina tarifa s'aplica. El bo social és una condició ortogonal a l'abonament i amb la seva pròpia vigència: una persona pot tenir un abonament anual de l'1 de gener al 31 de desembre i un bo social que caduca el 30 de juny. A partir de l'1 de juliol, el mateix abonament ha de generar trajectes amb tarifa normal. Com que el trajecte ja congela la seva tarifa (RN9), el sistema queda correcte sense tocar res més: n'hi ha prou que la transacció de desbloqueig triï la tarifa consultant bons_socials amb vigencia @> CURRENT_DATE.

3. La consulta de cost fiscal:

El NULLIF protegeix del zero: si la tarifa reduïda tingués preu_fraccio = 0, la divisió rebentaria.

Solució 3

Sobre l'índex: no en cal cap de nou. El filtre ts_fi IS NULL és tremendament selectiu —com a màxim 900 files d'1,6 milions— i uq_trajecte_obert_bici ja cobreix exactament aquell predicat parcial. El planificador el pot recórrer sencer (900 entrades) i filtrar després per ts_inici; afegir un índex sobre (ts_inici) WHERE ts_fi IS NULL milloraria marginalment una consulta que s'executa una vegada al dia i afegiria manteniment a 4.400 insercions diàries. No és rendible. Aquest és el raonament que 06-03 demanava: un índex es justifica per la freqüència de la consulta i el cost d'escriptura, no per la mida de la taula.

Has recorregut un projecte relacional sencer: d'una transcripció de reunió de dos paràgrafs a un sistema amb onze taules, vint restriccions que codifiquen regles de negoci reals, dues transaccions concurrents correctes, cinc informes d'explotació i un pla d'operació.

El que convé endur-se no és l'esquema —el del teu pròxim projecte serà un altre— sinó la forma de les decisions. Cadascuna de les importants va tenir el mateix format: una temptació simple, una pregunta al client que la va desmuntar, dues o tres alternatives posades en una taula i un criteri explícit per triar. L'ancoratge és una entitat perquè té estat propi. El trajecte és una entitat perquè neix incomplet i té identitat. La jerarquia fa servir taula per subclasse perquè altres taules referencien la superclasse i perquè el número de sèrie de la bateria ha de ser obligatori. bicis_disponibles trenca la 3FN per velocitat i es vigila; la tarifa congelada la trenca per semàntica i vigilar-la seria un error. Cap d'aquestes frases no és una preferència estètica: totes són arguments que es poden discutir i, si calgués, rebatre.

També has vist que les eines del curs no es fan servir d'una en una. L'EXCLUDE USING gist de 04-04 imposa una regla de negoci de 04-01; l'índex únic parcial que imposa RN5 resulta ser, sense canviar una línia, l'índex que arregla la consulta més freqüent del sistema; la desnormalització de 05-04 necessita l'activador de 05-04 i l'auditoria nocturna de 05-04, les tres coses o cap. Un sistema en producció és això: peces del temari funcionant alhora i sostenint-se entre si.

I acaba amb una llista de tres fracassos, que és la part més honesta del cas. La telemetria GPS, la fitxa enriquida de l'estació i les incidències d'estructura variable no encaixen bé en aquest esquema, i forçar-les-hi produiria columnes nul·les, taules EAV i migracions cada vegada que algú inventa un tipus d'incidència. A la lliçó 08-02 agafem aquestes tres peces exactes —ni una més— i les modelem a MongoDB amb el mètode de 03-03, comprovant què s'hi guanya, què s'hi perd i per què la temptació de "migrar-ho tot a Mongo" és la resposta equivocada. El nucli transaccional que acabes de construir es queda on és: és ell qui cobra els diners.