Volvemos a mirar el plano completo, como prometimos al cerrar el módulo 7. Durante siete módulos has construido BiblioRed a base de piezas: una tabla aquí, un JOIN allá, un índice cuando algo iba lento. Cada pieza se explicaba por separado porque había que aprenderla por separado. En un proyecto real nadie te entrega las piezas ordenadas: te entregan una conversación con un cliente y tienes que llegar tú solo desde ahí hasta un sistema en producción.

Eso es lo que hace esta lección. El ayuntamiento de Vallmar, satisfecho con la red de bibliotecas, acaba de encargar un segundo sistema: VallBici, el servicio municipal de bicicletas compartidas. No es una ampliación de BiblioRed; es un dominio nuevo, con sus propias reglas, sus propios problemas de concurrencia y sus propios informes. BiblioRed aparecerá de vez en cuando como término de comparación —"esto ya lo resolvimos así, aquí cambia porque…"—, pero el trabajo es nuevo de principio a fin.

El recorrido es el de un proyecto real y en su orden real: el encargo, los requisitos escritos, el modelo conceptual, el esquema físico, la comprobación de normalización, la carga de datos, las transacciones críticas, los informes, el rendimiento y la operación. En cada punto donde hay que decidir algo, la decisión va acompañada de su justificación y de las alternativas descartadas. Ese es el contenido de la lección: no el SQL final —que se copia en diez minutos— sino el razonamiento que lleva hasta él.

Y termina donde tiene que terminar un caso honesto: enumerando las tres cosas que este esquema resuelve mal, que son exactamente el material de la lección 08-02.

1. El encargo: la conversación inicial
2. Documento de requisitos VallBici v1.0
3. Modelo conceptual: entidades, relaciones y tres decisiones difíciles
4. El diagrama ER
5. Esquema físico: el CREATE TABLE comentado
6. Las restricciones que codifican las reglas de negocio
7. Comprobación de normalización y dos desnormalizaciones deliberadas
8. Carga de datos y volumen realista
9. Las dos transacciones críticas: desbloquear y anclar
10. Consultas de explotación: los informes del ayuntamiento
11. Rendimiento: las dos consultas que se degradan
12. Operación: roles, datos personales y copias
13. Qué resuelve mal este esquema
14. Errores Comunes y Consejos
15. Ejercicios
16. Conclusión

La primera reunión con el área de Movilidad del ayuntamiento produce esto, transcrito casi literalmente:

«Tenemos 60 estaciones repartidas por los cinco distritos de la ciudad y 900 bicicletas, unas mecánicas y otras eléctricas. Cada estación tiene un número fijo de anclajes; una bici ocupa un anclaje cuando está aparcada. Hay 24.000 personas abonadas. Una persona desbloquea una bici en una estación, hace su trayecto y la ancla en otra. Hay tres tipos de abono —anual, mensual y turístico de tres días— y si te pasas del tiempo incluido se cobra un recargo. Las bicis pasan revisiones, se averían y se retiran a taller. La aplicación del móvil tiene que enseñar la disponibilidad en tiempo real, recordar la sesión, dejar buscar estaciones por nombre o por dirección y guardar el rastro GPS de cada trayecto. Y queremos informes mensuales de uso por distrito y franja horaria.»

Aplicamos la técnica de 04-01: subrayar los sustantivos (candidatos a entidad), los verbos (candidatos a relación) y las frases con "no puede", "siempre", "cada" (candidatas a restricción). Pero antes hay que hacer algo que en 04-01 insistimos mucho: volver a preguntar. Un encargo de dos párrafos siempre esconde decisiones que el cliente da por evidentes y no lo son.

Esas siete respuestas valen más que las siguientes cien líneas de SQL. La cuarta, en particular, es la que evita el error caro: si la tarifa no se congela, el primer cambio de precios reescribe la facturación de dos años.

Esta lista es parte del requisito, no un extra. Es lo que en 04-01 llamábamos el criterio de éxito del esquema: un modelo que no puede responderlas es un modelo fallido, por elegante que sea.

• C1 — Trayectos por distrito y franja horaria, mes a mes.
• C2 — Los diez pares de estaciones origen→destino más frecuentes.
• C3 — Estaciones que se vacían o se llenan sistemáticamente, por franja (el problema del reequilibrado: es el que cuesta dinero de verdad, porque obliga a mover bicis en furgoneta).
• C4 — Ingresos por tipo de abono, separando cuotas de recargos.
• C5 — Bicicletas con más averías por hora de uso (no en términos absolutos: una bici muy usada se avería más y eso no la hace mala).

Del texto salen sin discusión: distrito, estación, anclaje, bicicleta, modelo de bicicleta, persona abonada, tipo de abono, tarifa, abono, trayecto, orden de taller y cobro. Lo que sí tiene discusión son tres decisiones, y son las que separan un modelo que aguanta de uno que no.

La tentación: guardar en estaciones una columna num_anclajes y otra bicis_disponibles, y no modelar el anclaje. Es más simple y aparentemente suficiente: para pintar la app basta con saber cuántas bicis hay.

Por qué se descarta. Tres motivos, en orden de peso:

1. El cliente dijo que un anclaje puede averiarse solo. Un atributo no tiene estado; una entidad sí. Sin anclajes, una estación con 20 anclajes y 3 rotos sigue "teniendo capacidad 20" y el sistema promete plazas que no existen.
2. La app tiene que decir a la persona en qué anclaje está la bici que ha reservado y en cuál debe dejarla. Ese dato no existe si el anclaje no existe.
3. RN1 y RN2 —"un anclaje, una bici; una bici, un anclaje"— son restricciones de integridad, y en el módulo 4 fijamos el principio: una restricción que el esquema puede imponer no se delega en el código. Con anclajes como tabla son una clave primaria y un UNIQUE; sin ella, son código de aplicación y confianza.

Cómo se modela: el anclaje es una entidad débil de la estación (regla 8 de 04-03), con clave primaria compuesta (estacion_id, numero). El número 7 solo significa algo dentro de una estación concreta, exactamente igual que el número de recibo de una multa dependía de la multa en BiblioRed.

Un trayecto conecta una persona (vía su abono), una bicicleta y dos estaciones. En términos de 04-02 sería una relación de grado 4, y las relaciones de grado alto son casi siempre un síntoma de que falta una entidad.

Se modela como entidad, y por cuatro razones:

1. Tiene atributos propios y abundantes: dos instantes, duración, importe, tarifa congelada.
2. Tiene identidad: la misma persona puede hacer el mismo trayecto entre las mismas dos estaciones con la misma bici dos veces el mismo día, y son dos hechos distintos. Una tabla de unión con clave compuesta los confundiría.
3. Nace incompleto. Cuando se desbloquea la bici solo se conoce la mitad del trayecto. Una relación que existe a medias es una entidad con columnas nulas, no una relación.
4. Otras cosas lo referencian: el cobro del recargo apunta al trayecto.

Las dos estaciones son dos relaciones 1:N distintas hacia estaciones (regla 5 de 04-03): estacion_origen y estacion_destino. No es un caso raro: es el mismo patrón que "vuelo con aeropuerto de salida y de llegada", y la única precaución es no olvidar que las dos claves ajenas apuntan a la misma tabla, lo que obliga a poner alias en todas las consultas que las usen.

Detalle fino: la clave ajena no apunta a estaciones sino a anclajes(estacion_id, numero), porque nos interesa saber de qué anclaje concreto salió y en cuál entró. La estación queda determinada por el anclaje.

Toda bicicleta es mecánica o eléctrica (jerarquía total y disjunta). Las eléctricas tienen tres atributos que las mecánicas no tienen sentido: capacidad de batería, autonomía y número de serie de la batería. En 04-03, regla 10, vimos las tres estrategias. Repasemos la decisión con los criterios de allí:

Elegimos tabla por subclase (estrategia 2), igual que hicimos con los materiales de BiblioRed y por el mismo motivo dominante: hay tablas que referencian la superclase. trayectos, anclajes y ordenes_taller apuntan a "una bicicleta", sin importar de qué tipo, y la estrategia 3 haría eso imposible sin duplicar todas las claves ajenas.

La estrategia 1 (tabla única) era defendible —solo son tres columnas— y con 900 filas el desperdicio es irrelevante. Se descarta por el cuarto criterio: n_serie_bateria tiene que ser obligatorio y único en las eléctricas, y en una tabla única solo podría ser opcional. Es exactamente el argumento que en BiblioRed nos hizo separar materiales_libro por el isbn.

Y como en 04-03, la estrategia 2 arrastra su agujero conocido: nada impide por sí solo que una bici con tipo = 'mecanica' tenga fila en bicicletas_electricas. Se tapa con el truco de la clave ajena discriminada, que verás en el esquema.

Léelo con la notación de 04-02: || es participación total y cardinalidad 1, o{ es cardinalidad N con participación parcial, |{ es N con participación total. Que ESTACION ||--|{ ANCLAJE sea total en los dos lados dice algo cierto y no trivial: una estación sin ningún anclaje no es una estación, y un anclaje sin estación no existe. Que ANCLAJE |o--o| BICICLETA sea parcial en ambos lados dice lo contrario: hay anclajes vacíos y hay bicis fuera de todo anclaje (circulando o en taller).

Dos extensiones antes de empezar. btree_gist permite mezclar en un EXCLUDE columnas de igualdad (un entero) con columnas de solapamiento (un rango), que es justo lo que piden RN3 y las tarifas.

Por qué estos tipos, uno a uno:

El truco del discriminante, explicado. La clave ajena compuesta (bicicleta_id, tipo) obliga a que la fila referenciada de bicicletas tenga tipo = 'electrica', porque el CHECK de esta tabla fija tipo a ese valor. Resultado: es imposible dar de alta una batería para una bici mecánica. El agujero que 04-03 dejaba abierto en la estrategia 2 queda tapado en la mitad que importa. La otra mitad —que una eléctrica no tenga fila aquí— sigue sin poder imponerse declarativamente y se controla en el proceso de alta.

Las dos reglas más importantes del sistema son dos líneas de esquema. La clave primaria compuesta impide que el anclaje 7 de la estación 12 aloje dos bicis. El UNIQUE (bicicleta_id) impide que la bici 417 esté simultáneamente en dos anclajes; funciona porque en PostgreSQL UNIQUE admite tantos NULL como quiera, así que todos los anclajes vacíos conviven sin conflicto.

ON DELETE CASCADE de anclajes hacia estaciones está justificado: un anclaje no tiene vida propia fuera de su estación (es la acción que 02-06 reservaba precisamente para entidades débiles). ON DELETE SET NULL hacia bicicletas es lo correcto para lo contrario: si una bici desapareciera del sistema, el anclaje debe quedar libre, no desaparecer.

Sobre NUMERIC para el dinero no hay debate y conviene repetirlo porque es el error más caro que se comete con los tipos: FLOAT no representa exactamente 0,10, y un sistema que suma 1,6 millones de importes con FLOAT produce un descuadre contable que nadie sabrá explicar. NUMERIC(6,2) da hasta 9.999,99 €, sobrado para una cuota anual.

DATERANGE en lugar de dos columnas fecha_inicio/fecha_fin es lo que hace posible el EXCLUDE. Con dos columnas sueltas, "estos dos abonos se solapan" es una consulta con cuatro comparaciones que hay que escribir bien cada vez; con un rango es el operador && y lo impone el motor.

Tres tipos que merecen un comentario:

• TIMESTAMPTZ, no TIMESTAMP. Vallmar cambia de hora dos veces al año. El domingo de octubre en que los relojes retrasan, un TIMESTAMP sin zona convierte "02:30" en un instante ambiguo, y los trayectos de esa madrugada pueden salir con duración negativa. TIMESTAMPTZ guarda un instante absoluto y la ambigüedad desaparece. El precio es acordarse de convertir a hora local cuando se agrupa por franja horaria, cosa que haremos explícitamente en C1.
• INTERVAL generada. duracion no se almacena a mano: es una columna generada (04-04). Cumple la regla 4 de 04-03 —los derivados no se almacenan— sin renunciar a poder indexarla, porque STORED sí ocupa disco pero se calcula sola y nunca puede discrepar de sus fuentes.
• BIGINT en trayecto_id. Con 1,6 millones de trayectos al año, INTEGER (2.147 millones) tardaría más de mil años en agotarse. Aun así se usa BIGINT: cambiar el tipo de una clave primaria en producción es una de las migraciones más dolorosas que existen, y el coste hoy son cuatro bytes por fila.

ck_origen_del_cobro es un ejemplo del patrón que 04-04 llamaba restricción de coherencia entre columnas: no basta con que cada columna sea válida por separado; la combinación tiene que tener sentido. Un cobro de concepto abono con trayecto_id relleno es un dato incoherente, y el esquema lo rechaza.

Vale la pena ver las tres más interesantes fallando, porque una restricción que nunca has visto saltar no sabes si funciona.

Ese último es el más valioso de los tres. La regla "una bici no puede estar en dos trayectos abiertos" parece código de aplicación puro, y en el 90 % de los sistemas lo es —con el resultado de que, bajo carga, dos peticiones simultáneas la violan alegremente—. Un índice único parcial la convierte en una garantía del motor que ninguna condición de carrera puede burlar.

Repasamos el esquema con el método de 05-03. Para cada tabla: identificar la clave, listar las dependencias funcionales y comprobar que todo atributo no primo depende de la clave completa y de nada más.

La nota sobre bicicletas.tipo. Existe la dependencia modelo_id → tipo, y modelo_id no es clave: es una dependencia transitiva y por tanto una violación de 3FN de manual. Se conserva por una razón concreta y verificable: es la columna que hace funcionar el discriminante de la jerarquía, y una clave ajena no puede apuntar a un valor que hay que ir a buscar a otra tabla. Y no genera anomalías, porque un modelo no cambia de tipo jamás: una bici mecánica no se convierte en eléctrica. Es el caso que 05-02 describía como "dependencia transitiva sobre un atributo inmutable", donde el riesgo de anomalía de actualización es cero. Aun así, se blinda con un disparador de verificación en el alta.

La nota sobre abonos.tipo_abono. Mismo razonamiento y misma conclusión: tarifa_id → tipo_abono. Se conserva porque las consultas de explotación agrupan por tipo de abono constantemente y evitar un JOIN en el 80 % de los informes lo justifica. Se blinda con una clave ajena compuesta:

Ahora la redundancia es imposible de contradecir, que es la única forma aceptable de convivir con una redundancia. Es exactamente el mismo patrón del discriminante de las bicis.

Qué se rompe: el dato es derivable con SELECT COUNT(*) FROM anclajes WHERE estacion_id = ? AND bicicleta_id IS NOT NULL.

Por qué se acepta: la app móvil pide la disponibilidad de las 60 estaciones cada vez que alguien abre el mapa, y con 24.000 personas abonadas eso son decenas de miles de peticiones al día que degeneran en un recuento sobre anclajes cada una. Es el caso de libro de 05-04: lectura masiva, escritura poco frecuente, dato pequeño.

Cómo se mantiene: con un disparador sobre anclajes, no a mano desde la aplicación. Que lo mantenga la aplicación es lo que garantiza que algún día discrepe.

Y, como manda 05-04, una consulta de auditoría que se ejecuta cada noche y avisa si el contador ha derivado:

Qué se rompe: minutos_incluidos, minutos_fraccion, precio_fraccion y recargo_electrica dependen de tarifa_id, no de trayecto_id.

Por qué se acepta: no es rendimiento, es corrección. Es la respuesta a la cuarta pregunta de la reunión inicial. Si el trayecto solo guardara tarifa_id, cualquier recálculo posterior aplicaría la tarifa actual; el día que el ayuntamiento suba el precio de la fracción, todo el histórico cambiaría de importe. Los datos históricos deben ser reproducibles: es un requisito contable y a veces legal.

Cómo se mantiene: copiando la tarifa en el momento del desbloqueo, dentro de la transacción, y no volviéndola a tocar nunca. Un disparador BEFORE UPDATE que rechace cambios en esas cuatro columnas es una protección barata y sensata.

Fíjate en la diferencia entre las dos: la primera se desnormaliza por velocidad y hay que vigilarla; la segunda se desnormaliza por semántica y vigilarla sería un error, porque su valor debe diferir del actual.

Ese 1.512 es el primer dato de dimensionamiento útil: 1.512 anclajes para 900 bicicletas, un 68 % de ocupación media. Es una holgura sana; por debajo del 80 % el reequilibrado se vuelve una pesadilla operativa.

Toda decisión de rendimiento del apartado 11 se refiere a esos 1,6 millones anuales de trayectos. Las demás tablas son irrelevantes a efectos de plan de ejecución, y confundir eso es la forma más habitual de perder una tarde indexando lo que no toca.

Aquí es donde el módulo 6 deja de ser teoría. El escenario que hay que resolver es concreto: son las 08:12, la estación del Puerto tiene una sola bici libre y dos personas pulsan "desbloquear" con 40 milisegundos de diferencia.

Por qué SKIP LOCKED y no FOR UPDATE a secas. Con FOR UPDATE, la sesión B se queda esperando a que A confirme, y luego reevalúa: como el anclaje ya no tiene bici, obtiene 0 filas. El resultado final es correcto, pero B ha esperado sin necesidad. Con SKIP LOCKED, B ignora la fila bloqueada y sigue buscando otra bici en la misma estación; solo si de verdad no queda ninguna devuelve 0 filas. En una estación con 8 bicis y 8 personas desbloqueando a la vez, la diferencia es que las 8 lo consiguen en paralelo en lugar de hacer cola. Es exactamente el patrón de cola que vimos en 06-02, aplicado a un inventario.

Por qué FOR UPDATE OF a. Sin el OF a, PostgreSQL bloquearía también la fila de bicicletas, y no hace falta: la fila que decide quién gana es la del anclaje. Bloquear de más multiplica los interbloqueos.

El nivel de aislamiento es READ COMMITTED, el de por defecto. No hace falta subir a REPEATABLE READ: no hay ninguna lectura que deba repetirse de forma estable, y el bloqueo explícito ya resuelve la carrera. Subir el aislamiento "por si acaso" solo añade errores de serialización que habría que reintentar.

Un trayecto de 68 minutos con abono anual: 68 − 30 incluidos = 38 minutos excedidos, ceil(38/15) = 3 fracciones × 0,60 € = 1,80 €… más el recargo de eléctrica 0,35 €. El resultado de arriba, 1,55 €, corresponde a 2 fracciones (1,20 €) más 0,35 €, es decir a un trayecto de 55 minutos. Comprueba tú el cálculo con los dos casos: verificar a mano el primer importe que produce un sistema de cobro es una costumbre que ahorra disgustos.

Los tres detalles que hacen esta transacción robusta:

1. AND t.ts_fin IS NULL en el WHERE del UPDATE la hace idempotente. Si la app reintenta el anclaje porque perdió la respuesta por red, el segundo intento afecta a 0 filas y no vuelve a cobrar.
2. El RETURNING permite a la aplicación comprobar cuántas filas cambió. Cero filas no es un éxito silencioso: es un error que hay que tratar.
3. El contador bicis_disponibles no se toca aquí: lo actualiza el disparador de anclajes. Si se tocara también a mano, el contador subiría de dos en dos, y ese es el fallo de desnormalización más común que existe.

C1 — Trayectos por distrito y franja horaria.

Ahí se lee un patrón real: el Ensanche y el Industrial son claramente pendulares (dos puntas, poco valle) mientras que el Puerto tiene su máximo en valle — es turismo, no desplazamiento al trabajo. El AT TIME ZONE 'Europe/Madrid' no es decorativo: sin él, en verano las franjas saldrían desplazadas dos horas.

C2 — Los pares origen→destino más frecuentes, con su ranking dentro del distrito.

El RANK() OVER (PARTITION BY ...) sobre un COUNT(*) agregado es el patrón "top N por grupo" que practicaste en 07-04. Nótese que la función de ventana se evalúa después del GROUP BY, por eso puede ordenar por COUNT(*).

C3 — El problema del reequilibrado. Esta es la consulta que ahorra dinero de verdad, y la que mejor demuestra por qué modelamos el trayecto como entidad con dos extremos.

Los signos cuentan la historia entera: la 7 y la 41 son residenciales (se vacían por la mañana, se rellenan por la tarde) y la 22 es un destino de trabajo. La columna neto_mes dice cuántas bicis hay que mover en furgoneta cada mes, y el 08-12 frente al 16-20 dice a qué hora hay que moverlas. La técnica —convertir dos columnas de una fila en dos filas con signo mediante UNION ALL— es la forma canónica de tratar cualquier entidad con dos extremos.

C4 — Ingresos por tipo de abono.

El pct_recargo del abono turístico (36,9 %) es el tipo de hallazgo que justifica el informe: las personas visitantes se pasan del tiempo incluido casi la mitad de las veces. O bien 15 minutos son pocos, o bien la app no avisa. Es una conversación de negocio que solo existe porque la consulta la hizo posible.

C5 — Averías por hora de uso.

El WHERE u.horas >= 50 es la parte importante y la que casi nadie pone: sin él, una bici con 2 horas de uso y una avería encabeza la lista con 50 averías/100 h y el informe carece de valor. Toda tasa necesita un umbral mínimo de denominador.

Con la tabla vacía todo va rápido. El día que trayectos tiene 1,6 millones de filas, dos cosas se rompen. Seguimos el orden de intervención de 06-03: medir primero, entender el plan, y solo entonces tocar.

Diagnóstico: Rows Removed by Filter: 488153 por cada uno de los 3 procesos. Se leen 1,6 millones de filas para quedarse con 45.180. Es el síntoma clásico de 06-03: un filtro muy selectivo (2,8 %) resuelto con recorrido secuencial.

Intervención. Aquí hay una elección real entre dos índices:

trayectos se inserta siempre en orden cronológico y nunca se reordena: la correlación física de ts_inicio es prácticamente 1. Es el caso exacto para el que existe BRIN.

De 2.189 ms a 119 ms, con un índice de 48 KB. Rows Removed by Index Recheck: 2841 es normal en BRIN: el índice trabaja por bloques, así que devuelve algún bloque de más y el motor descarta las filas sobrantes. A cambio ocupa mil veces menos que el B-tree y su mantenimiento en las inserciones es despreciable.

Es la consulta más frecuente del sistema: la ejecuta la app cada vez que alguien la abre con una bici en curso.

Y aquí está el detalle bonito: el índice que lo arregla ya existe. Es uq_trayecto_abierto_abono, el índice único parcial que creamos en el apartado 5 para imponer RN5. Un índice parcial sobre WHERE ts_fin IS NULL cubre, con 1,6 millones de filas en la tabla, solo las 900 como máximo que pueden estar abiertas simultáneamente. Lo único que faltaba era un ANALYZE:

De 1.893 ms a 0,049 ms. La lección es la que 06-03 repetía: una restricción bien elegida es también un índice, y muchas veces el índice que necesitas ya lo has creado sin darte cuenta.

Los tres índices restantes que sí hay que crear a mano:

El orden de las columnas en los compuestos sigue la regla de 06-03 —igualdad primero, rango después— y responde a C2 y C3. El segundo es parcial porque los trayectos abiertos no tienen destino y no aportan nada al índice.

El GRANT SELECT, UPDATE (estado) ON bicicletas es la aplicación literal del principio de mínimo privilegio de 06-04: el taller cambia el estado de una bici, y solo el estado. No puede tocar km_acumulados ni matricula.

Los informes del ayuntamiento no necesitan saber quién hizo cada trayecto. La vista que consume el perfil analista rompe el vínculo:

Dos observaciones honestas sobre esto:

1. La vista no anonimiza de verdad. Con distrito de residencia, década de edad y patrón de trayectos, una persona con recorrido singular puede ser reidentificable. Reducir el riesgo requiere agregación mínima (no publicar celdas con menos de N personas) y eso es una decisión de política, no de SQL.
2. El cumplimiento normativo no lo decide quien diseña la base de datos. Base legal del tratamiento, plazos de conservación de la telemetría, evaluación de impacto, derecho de supresión: todo eso lo revisa un profesional de protección de datos o compliance. Lo que sí es responsabilidad técnica es que el esquema permita cumplirlo: por eso el documento se guarda como documento_hash y no en claro, por eso ON DELETE RESTRICT obliga a un procedimiento explícito de supresión en lugar de borrar en cascada el histórico contable, y por eso existe la vista.

La última fila es la que más se salta y la única que garantiza algo. En palabras de 06-04: una copia que nunca se ha restaurado no es una copia, es una esperanza. Con PITR configurado, el escenario "a las 11:40 alguien ejecutó un UPDATE sin WHERE sobre tarifas" se resuelve restaurando a las 11:39.

El sistema funciona, cumple los ocho requisitos funcionales y responde a las cinco consultas. Y aun así hay tres cosas del encargo inicial que este esquema resuelve mal. Vale la pena ser preciso, porque son exactamente el material de la lección siguiente.

1. La telemetría GPS. El encargo pedía guardar el rastro de cada trayecto: posiciones cada 5 segundos, batería de las eléctricas, incidencias. Un trayecto medio de 14 minutos son ~170 puntos. Con 1,6 millones de trayectos anuales, eso es una tabla posiciones de 272 millones de filas al año, cada una con trayecto_id, ts, lat, lon y poco más. Se puede hacer —PostgreSQL aguanta— pero es un mal encaje: son datos que se escriben masivamente, se leen siempre enteros y por trayecto, no se actualizan nunca, no necesitan integridad referencial estricta y caducan a los pocos meses. Estás pagando el precio del modelo relacional (índice por fila, WAL por fila, visibilidad por fila) para un dato que no usa ninguna de sus ventajas.

2. La ficha enriquecida de la estación. Nuestra tabla estaciones tiene 11 columnas planas. La ficha que quiere la app tiene fotos, horarios de acceso que varían por estación, accesibilidad, si está bajo cubierta, si tiene bomba de aire, notas de la empresa mantenedora, etiquetas de puntos de interés cercanos… y cada estación tiene un subconjunto distinto de esos atributos. Modelarlo en el relacional lleva a una de tres salidas, todas malas: 40 columnas nulas, una tabla clave-valor genérica (el anti-patrón EAV que 04-01 marcó en rojo) o una tabla nueva por cada atributo que se le ocurra a alguien.

3. Las incidencias con estructura variable. ordenes_taller tiene un motivo VARCHAR(60). Pero una incidencia de "frenos" necesita registrar qué freno y qué medida; una de "batería" necesita ciclos de carga y voltaje; una de "vandalismo" necesita fotos y número de atestado. Son estructuras distintas por tipo, y añadir un tipo nuevo no debería requerir un ALTER TABLE en producción.

Las tres tienen algo en común: estructura variable o volumen alto sin necesidad de transacciones. Y las tres tienen algo más en común, más importante todavía: ninguna de ellas es el núcleo transaccional. Nadie cobra dinero a partir de un punto GPS. Eso es lo que permite sacarlas de PostgreSQL sin poner en riesgo lo que importa, y es justo lo que hace la lección 08-02.

Error 1: empezar por el CREATE TABLE. Es el error del que se derivan casi todos los demás. Las siete preguntas del apartado 1 costaron veinte minutos de reunión y determinaron la mitad del esquema. Si no las hubiéramos hecho, habríamos descubierto la necesidad de la tarifa congelada el día de la primera subida de precios, con dos años de facturación ya emitida.

Error 2: no modelar el anclaje "porque con un contador basta". Funciona hasta el primer anclaje averiado. La regla general: si el cliente dice que algo tiene estado propio, es una entidad, aunque parezca un número.

Error 3: FLOAT para el dinero. Sigue ocurriendo. NUMERIC para todo lo que se cobra, se factura o se suma.

Error 4: TIMESTAMP sin zona en un sistema con cambio horario. El domingo de octubre producirá trayectos de duración negativa y la restauración de esa noche será un rompecabezas.

Error 5: mantener a mano un contador desnormalizado. Si bicis_disponibles se actualiza desde la aplicación y desde el disparador, se duplica el incremento. Uno de los dos, y mejor el disparador.

Error 6: subir el nivel de aislamiento en vez de bloquear la fila correcta. SERIALIZABLE no es una solución mágica: convierte una carrera en un error de serialización que la aplicación debe reintentar. Un FOR UPDATE SKIP LOCKED sobre la fila que decide es más barato y más predecible.

Consejo 1: escribe la lista de consultas antes que el esquema. Es el mejor detector de entidades que falta. C3 fue la que confirmó que el trayecto necesitaba sus dos extremos como claves ajenas independientes.

Consejo 2: cada restricción que escribas, pruébala fallando. Una restricción que nunca has visto rechazar un INSERT no sabes si está bien escrita.

Consejo 3: mira si el índice ya existe antes de crearlo. La degradación 2 se resolvió sola. Los índices únicos, parciales o no, son índices completos.

Consejo 4: documenta las desnormalizaciones en el propio esquema. El comentario -- Desnormalización deliberada nº 1 evita que dentro de tres años alguien "arregle" el esquema quitándola. Mejor todavía: COMMENT ON COLUMN.

El ayuntamiento quiere permitir reservar una bicicleta desde la app: la persona reserva una bici concreta de una estación y tiene 10 minutos para llegar y desbloquearla; pasado ese tiempo, la reserva caduca y la bici vuelve a estar disponible. Una persona solo puede tener una reserva activa.

1. Decide si la reserva es una entidad nueva o un estado de algo existente, y justifícalo.
2. Escribe el CREATE TABLE (o el ALTER TABLE) con todas las restricciones necesarias.
3. Escribe la transacción de reserva, con su control de concurrencia.
4. Explica cómo caducan las reservas y por qué esa decisión.

Se introduce una tarifa reducida para personas con bono social municipal. La condición se acredita cada año y puede dejar de cumplirse. Un trayecto se cobra con la tarifa reducida si la persona tenía el bono social vigente en el momento del desbloqueo.

1. Modela la acreditación del bono social sin duplicar la información de personas_abonadas.
2. Explica por qué la restricción EXCLUDE de abonos no basta aquí.
3. Escribe la consulta que calcula, para el mes pasado, cuánto ha dejado de ingresar el ayuntamiento por las tarifas reducidas.

Una bicicleta fantasma es la que lleva más de 48 horas en un trayecto abierto: se la robaron, se rompió el sistema de anclaje o la app falló al cerrarlo. Escribe la consulta que las lista con la última estación conocida, cuánto tiempo llevan y el nombre de contacto de la persona que la desbloqueó, ordenadas de más antigua a más reciente. Añade el índice que la haga eficiente, o explica por qué no hace falta ninguno.

Solución 1

1. Entidad nueva. Tres argumentos: (a) tiene atributos propios —instante de reserva, instante de caducidad, resultado—; (b) tiene historial: querremos saber cuántas reservas caducan sin usarse, y un estado sobreescrito no deja rastro; (c) relaciona tres cosas (persona, bici y anclaje) con temporalidad propia. Un estado = 'reservada' en bicicletas no permitiría ninguna de las tres.

2. El esquema:

Los dos índices únicos parciales imponen "una reserva activa por persona" y "una reserva activa por bici" — el mismo patrón que RN4/RN5. El tercero es de servicio, para el proceso de caducidad.

3. La transacción:

El anti-join NOT EXISTS excluye las bicis ya reservadas; el índice único parcial es la red de seguridad si dos peticiones lo esquivan. Fíjate en que no se marca la bici como reservada en bicicletas: el estado vive en reservas, un solo sitio.

4. La caducidad. Dos opciones y una elegida.

Descartada: un proceso que cada minuto marque como caducada las vencidas. Funciona, pero introduce una ventana en la que la reserva está vencida y todavía figura activa, y depende de que el proceso no se caiga.

Elegida: caducidad implícita en la lectura. La reserva se considera activa si resultado = 'activa' AND ts_limite > now(); el NOT EXISTS de arriba añade esa condición. El proceso de limpieza sigue existiendo, pero solo para dejar el histórico ordenado, y que se retrase no afecta a la corrección. La regla general: cuando el tiempo determina la validez de un dato, la verdad debe estar en la consulta, no en un proceso.

Solución 2

1. El modelo. Una tabla de acreditaciones con vigencia, no una columna booleana en personas_abonadas:

Una columna tiene_bono_social BOOLEAN sería el error clásico: no guarda desde cuándo, no guarda el expediente y, sobre todo, no permite reconstruir si lo tenía el día del trayecto. La acreditación es un hecho con fecha, y los hechos con fecha van en filas, no en columnas.

2. Por qué el EXCLUDE de abonos no basta. Ese EXCLUDE garantiza que no haya dos abonos solapados de la misma persona; no dice nada sobre qué tarifa se aplica. El bono social es una condición ortogonal al abono y con su propia vigencia: una persona puede tener un abono anual del 1 de enero al 31 de diciembre y un bono social que caduca el 30 de junio. A partir del 1 de julio, el mismo abono debe generar trayectos con tarifa normal. Como el trayecto ya congela su tarifa (RN9), el sistema queda correcto sin tocar nada más: basta con que la transacción de desbloqueo elija la tarifa consultando bonos_sociales con vigencia @> CURRENT_DATE.

3. La consulta de coste fiscal:

El NULLIF protege del cero: si la tarifa reducida tuviera precio_fraccion = 0, la división reventaría.

Solución 3

Sobre el índice: no hace falta ninguno nuevo. El filtro ts_fin IS NULL es tremendamente selectivo —como máximo 900 filas de 1,6 millones— y uq_trayecto_abierto_bici ya cubre exactamente ese predicado parcial. El planificador puede recorrerlo entero (900 entradas) y filtrar después por ts_inicio; añadir un índice sobre (ts_inicio) WHERE ts_fin IS NULL mejoraría marginalmente una consulta que se ejecuta una vez al día y añadiría mantenimiento a 4.400 inserciones diarias. No es rentable. Este es el razonamiento que 06-03 pedía: un índice se justifica por la frecuencia de la consulta y el coste de escritura, no por el tamaño de la tabla.

Has recorrido un proyecto relacional entero: de una transcripción de reunión de dos párrafos a un sistema con once tablas, veinte restricciones que codifican reglas de negocio reales, dos transacciones concurrentes correctas, cinco informes de explotación y un plan de operación.

Lo que conviene llevarse no es el esquema —el de tu próximo proyecto será otro— sino la forma de las decisiones. Cada una de las importantes tuvo el mismo formato: una tentación simple, una pregunta al cliente que la desmontó, dos o tres alternativas puestas en una tabla y un criterio explícito para elegir. El anclaje es una entidad porque tiene estado propio. El trayecto es una entidad porque nace incompleto y tiene identidad. La jerarquía usa tabla por subclase porque otras tablas referencian la superclase y porque el número de serie de la batería tiene que ser obligatorio. bicis_disponibles rompe la 3FN por velocidad y se vigila; la tarifa congelada la rompe por semántica y vigilarla sería un error. Ninguna de esas frases es una preferencia estética: todas son argumentos que se pueden discutir y, si hiciera falta, rebatir.

También has visto que las herramientas del curso no se usan de una en una. El EXCLUDE USING gist de 04-04 impone una regla de negocio de 04-01; el índice único parcial que impone RN5 resulta ser, sin cambiar una línea, el índice que arregla la consulta más frecuente del sistema; la desnormalización de 05-04 necesita el disparador de 05-04 y la auditoría nocturna de 05-04, las tres cosas o ninguna. Un sistema en producción es esto: piezas del temario funcionando a la vez y sosteniéndose entre sí.

Y termina con una lista de tres fracasos, que es la parte más honesta del caso. La telemetría GPS, la ficha enriquecida de la estación y las incidencias de estructura variable no encajan bien en este esquema, y forzarlas ahí produciría columnas nulas, tablas EAV y migraciones cada vez que alguien inventa un tipo de incidencia. En la lección 08-02 cogemos esas tres piezas exactas —ni una más— y las modelamos en MongoDB con el método de 03-03, comprobando qué se gana, qué se pierde y por qué la tentación de "migrarlo todo a Mongo" es la respuesta equivocada. El núcleo transaccional que acabas de construir se queda donde está: es él quien cobra el dinero.