Tienes el encargo (12-01) y el contrato (12-02). Falta construirlo, y construirlo tiene un orden: si escribes las consultas antes de tener datos, no puedes comprobarlas; si creas los índices antes de tener las consultas, los estás inventando; y si empiezas por el CREATE TABLE sin haber dibujado nada, la tercera tabla te obligará a rehacer las dos primeras.

Esta lección es la guía de construcción en siete pasos: el modelo completo, el DDL entero, la técnica para generar datos coherentes, el método para escribir consultas sin equivocarte y el criterio para decidir índices y encapsulados. Lo que no te da son las quince consultas del enunciado hechas: eso es 12-04, y llegar allí sin haberlo intentado es tirar el proyecto.

1. Paso 1 — Del enunciado al modelo
2. Paso 2 — El DDL: 01-esquema.sql
3. Paso 3 — Datos de prueba coherentes: 02-datos.sql
4. Paso 4 — Las consultas: método de trabajo
5. Paso 5 — Los índices, después de las consultas
6. Paso 6 — Vistas, procedimientos y triggers
7. Paso 7 — Seguridad y entrega
8. Cronograma orientativo
9. Errores Comunes y Consejos
10. Ejercicios
11. Conclusión

Doce tablas y dieciséis relaciones: catorce 1:N, una N:M resuelta con la tabla puente obras_autores, una reflexiva en bibliotecarios y una 1 a 0..1 entre prestamos y multas. La forma es la de TiendaVerde; el contenido, no. Las columnas están en el DDL del paso 2.

1. Por qué ejemplares es una tabla y no un contador. Argumentado en 12-01: el ejemplar tiene sede, estado e historia propios. La consecuencia técnica es más contundente que el argumento conceptual: sin esa tabla, la FK de prestamos apuntaría a obras y sería imposible saber cuál de los tres volvió. No es estilo: es que el modelo no puede representar el hecho.

2. Por qué prestamos guarda fecha_prevista en lugar de calcularla. Podría deducirse (fecha_prestamo + plazo(tipo del socio)), y no se hace: el argumento es idéntico al de lineas_pedido.precio_unitario (01-06, 11-02), es un dato histórico. Si Marta pasa de general a senior, sus préstamos antiguos se recalcularían a 30 días y uno que llegó tarde pasaría a estar en plazo. Además se mueve con las renovaciones (RN-03), así que ni siquiera es función de la fecha inicial. Regla: si el valor depende de una condición del pasado que puede cambiar, se guarda.

3. Por qué las multas son una tabla y no una columna. Tiene vida propia —se genera, se paga, se puede condonar—, y eso son tres columnas más, nulas para el 83 % de los préstamos; permite contar y sumar sin recorrer todos los préstamos; y mañana podría haber multas que no vengan de un retraso (un libro dañado), y bastaría hacer nulable la FK. Relación 1 a 0..1, forzada con UNIQUE (prestamo_id).

4. Cómo se modela la cola de reservas. Con fecha_reserva y nada más. Las tres opciones:

5. Por qué el estado del préstamo no se almacena y el del ejemplar sí. Parecen simétricos y no lo son. El del préstamo (activo, vencido, devuelto) es función de dos fechas y del reloj: guardarlo obligaría a un proceso nocturno que marcara los vencidos. El del ejemplar (disponible, reparacion, extraviado, baja) es un hecho físico que alguien decide y no se deduce de nada. Por eso ejemplares.estado no incluye prestado: eso sí se deduce, y tenerlo sería guardar dos veces lo mismo con dos formas de contradecirse.

El orden es el de 05-01: DROP de hijos a padres, CREATE de padres a hijos.

Ocho de las doce tablas no tienen ninguna sorpresa: son el patrón exacto de categorias, proveedores y devoluciones en TiendaVerde, así que se resumen en esta tabla —columnas, nulables y restricciones nombradas— y escribir su CREATE TABLE te llevará cinco minutos:

Y estas son las cuatro que sí tienen decisiones dentro del propio CREATE TABLE:

Es la pieza técnica del proyecto. Un índice parcial (08-02) se construye solo sobre las filas que cumplen una condición; cuando además es UNIQUE, la unicidad solo se exige entre esas filas. Se lee literalmente: "entre los préstamos sin devolver, el ejemplar_id es único". Sus dos pruebas:

Tres propiedades lo hacen la solución correcta y no un truco. Es declarativo: lo hace cumplir el motor, no la aplicación, así que sobrevive a scripts, a otra aplicación y a una consola abierta a las tres de la mañana. Es diminuto: con 300 000 préstamos históricos y 400 activos, el índice tiene 400 entradas, no 300 000. Y además acelera, porque toda consulta con WHERE fecha_devolucion IS NULL puede usarlo: una restricción que de propina es un índice útil.

Y su límite, que hay que decir en el informe: impide dos préstamos activos, no dos préstamos solapados en el pasado. Si alguien registra hoy un préstamo de enero ya devuelto que se pisa con otro de enero, el índice no lo ve. La solución completa está en 12-04.

Los datos de prueba tienen dos objetivos que se estorban: ser suficientes para que las consultas signifiquen algo y pequeños para leerlos con los ojos. El reparto: a mano, con id explícitos, todo lo que aparezca en las consultas —así "el préstamo 29" significa siempre lo mismo—; con generate_series, el volumen para medir rendimiento.

El detalle que decide si esto funciona: todas las filas generadas llevan fecha_devolucion. Si dejaras nulos al azar, el índice único parcial abortaría la carga en cuanto dos préstamos del mismo ejemplar quedaran abiertos. Lejos de ser una molestia, es la prueba de que la restricción funciona.

TiendaVerde tenía huecos deliberados —clientes sin pedidos, productos sin vender, pedidos sin empleado— y las lecciones los necesitaban. Ahora te toca ponerlos: sin ellos, media consulta mal escrita devuelve lo mismo que una bien escrita y no te enteras.

Como en 01-06, comprueba antes de escribir una sola consulta. El juego de datos de referencia tiene 3 sedes, 6 materias, 5 editoriales, 10 autores, 8 bibliotecarios, 12 obras, 15 filas de obras_autores, 20 ejemplares, 15 socios, 36 préstamos, 5 reservas y 6 multas. Y los huecos, en una sola consulta:

1. Empieza por el FROM, no por el SELECT. Decide primero de qué tabla sale una fila del resultado: ¿una por obra? El FROM es obras. ¿Una por préstamo? Es prestamos. Todo lo demás se le une.
2. Comprueba el recuento después de cada JOIN. Si al unir ejemplares con prestamos pasas de 20 filas a 36, has cambiado de granularidad: puede ser correcto, pero tienes que saberlo, porque a partir de ahí COUNT(*) cuenta préstamos, no ejemplares.
3. Decide JOIN o LEFT JOIN preguntando por los ceros. ¿Quiero ver la obra sin ejemplares, la sede sin préstamos, el mes vacío? Entonces LEFT JOIN — y recuerda que COUNT(*) cuenta la fila fantasma y COUNT(columna_de_la_derecha) no (04-04). Y valida el total por dos caminos: el pivote de RC-15 debe sumar lo mismo que SELECT COUNT(*) FROM prestamos; si no cuadra, no discutas con la consulta, está mal (11-04).

"Dime dónde están los tres ejemplares de El jardín de las horas y quién los tiene." Es la pregunta con la que empezaba el correo de Helena, y resume el proyecto entero.

Tres decisiones que justificar. FROM ejemplares, porque quiero una fila por ejemplar, esté prestado o no. La condición p.fecha_devolucion IS NULL va en el ON, no en el WHERE: allí desaparecerían los ejemplares sin préstamo activo y el LEFT JOIN sería un JOIN disfrazado (03-03). Y estado sigue diciendo disponible en los tres, y está bien: es la condición física del volumen; que esté fuera se ve en lo_tiene. Es la separación del paso 1, hecha columna.

Helena pidió "los socios que no vienen desde hace tiempo". Antes de escribir nada hay que definirlo (11-04): aquí, socio en estado activo cuyo último préstamo es anterior a hace 90 días o que no ha tomado ninguno nunca. Los de baja quedan fuera a propósito.

Cuatro socios y tres lecciones. El LEFT JOIN es obligatorio: con un JOIN normal, los tres que nunca han pedido nada —los más inactivos de todos— desaparecerían. El COALESCE del HAVING es lo que los deja pasar, porque NULL < fecha da UNKNOWN y HAVING descarta lo que no es cierto (04-03). Y COUNT(p.id), no COUNT(*): con COUNT(*) los tres saldrían con 1 préstamo en vez de 0. Es el error más repetido del proyecto entero. Y una lectura que no es de SQL: los tres sin préstamos no son el mismo problema —Carla se dio de alta en febrero, Irene lleva un año con el carné sin usar y Marta tiene cuatro préstamos y tres meses sin venir—; meterlos en la misma cifra es lo que 11-04 llamaba mezclar dos preguntas en un número.

El orden es innegociable: primero las quince consultas, después los índices. Un índice existe para servir a una consulta concreta; si no puedes nombrarla, sobra y solo frena las escrituras (08-02). El método es mecánico:

1. Lista el WHERE, el JOIN y el ORDER BY de cada consulta. Esa lista es tu lista de candidatos, y solo esa (11-05).
2. Añade las claves foráneas que navegas: PostgreSQL indexa la PK, no la FK (08-01). Es la causa número uno de barridos secuenciales en un esquema de doce tablas.
3. Descarta lo que no aporta —una columna de tres valores como socios.tipo o una tabla de seis filas como materias no se indexan: el planificador las ignorará, y hará bien— y mide con EXPLAIN (08-05), no con la intuición.

Sin índice sobre prestamos(socio_id) y con volumen, el plan es un Seq Scan que lee la tabla entera; con él, un Index Scan que va directo. Lo importante no es que mejore, es que lo compruebes y lo escribas (RP-06). Y con las 36 filas del juego de pruebas verás Seq Scan en todo, porque leer 36 filas es más barato que abrir un índice — lo cual es una lección en sí misma: para hablar de rendimiento hay que tener volumen, y para eso está el generate_series del paso 3. Los índices de referencia, con su plan, están en 12-04.

La regla que evita el desastre: encapsula lo que se repite y lo que es delicado; no encapsules por gusto. Tres piezas bastan y una cuarta ya sería sospechosa. El código está en 12-04; aquí va el criterio, que es lo que hay que saber decidir.

Dos detalles de la vista importan más de lo que parecen: su columna se llama multa_estimada, no multa, porque todavía no existe (RN-10) —llamarla multa sería la primera piedra de un informe que suma dinero que nadie debe—; y usa CURRENT_DATE, así que cambia sola cada noche, que es exactamente lo que se quería y la razón de no guardar "vencido" en una columna.

Y aquí se para. La tentación de añadir un trigger para el máximo de préstamos simultáneos, otro para validar renovaciones y otro para recalcular el bloqueo es enorme, y es un error: los triggers son lógica invisible —quien lee el INSERT no ve lo que pasa— y depurarlos es incómodo. El criterio de 10-05: trigger para lo que debe cumplirse pase lo que pase y no se pueda declarar; procedimiento para la operación de negocio con varios pasos; aplicación para el resto.

Los tres roles de RS-01 a RS-03, con el patrón de 11-03: privilegios al grupo, nunca al usuario.

Dos falsos, que es lo que se pedía. Esa comprobación de dos líneas es la prueba de RS-01 y RS-04, y va en el informe.

Por sesiones de unas dos horas. Desviarse mucho en la sesión 3 suele ser señal de que el modelo tiene un problema, no de que vayas lento:

• Crear las tablas en el orden en que se te ocurren. La integridad referencial impone el orden: padres antes que hijos al crear, al revés al borrar (05-01). Si tu script solo funciona la primera vez, no es idempotente y RE-02 no se cumple.
• Poner la condición del LEFT JOIN en el WHERE. El error más caro y más silencioso del proyecto: convierte el LEFT JOIN en JOIN, desaparecen los ceros y el resultado sigue pareciendo razonable. Condición sobre la tabla de la derecha ⇒ va en el ON.
• COUNT(*) después de un LEFT JOIN. Cuenta la fila fantasma y los socios sin préstamos salen con 1. Usa COUNT(columna_de_la_derecha).
• Añadir el valor prestado a ejemplares.estado, o generar datos al azar sin mirar las restricciones. Lo primero duplica información que acabará contradiciendo a prestamos; lo segundo (devoluciones anteriores al préstamo, dos activos del mismo ejemplar, multas sin retraso) aborta el script a la mitad y deja la base a medio cargar.
• Consejo: después de crear el esquema, intenta violarlo, y escribe el DDL con los -- RI-nn al lado de cada restricción. Un INSERT que debe fallar y falla vale más que tres párrafos de informe, y repasar los catorce requisitos con la rúbrica te llevará un minuto en vez de media hora.
• Consejo: guarda un 99-comprobaciones.sql con los recuentos, los ocho huecos, los INSERT que deben fallar y las dos consultas de privilegios. Es tu batería de pruebas: ejecutarla tras cada cambio te dirá al instante si has roto algo.
• Consejo: escribe el informe a medida que decides. Cada decisión difícil, su párrafo, en el momento. Reconstruirlo la última noche es imposible y se nota.

Implementa la RN-04 completa: no se puede renovar si se alcanzó el máximo, si el préstamo está vencido o si la obra tiene reservas en espera. (1) ¿Cuáles de las tres condiciones se pueden declarar con un CHECK y cuáles no, y por qué? (2) Escribe un UPDATE que renueve el préstamo 32 solo si las tres lo permiten. (3) ¿Qué devuelve con los datos del proyecto?

El préstamo 29 (Lena Fuentes, ejemplar ALV-0003) lleva 36 días de retraso y se devuelve hoy, 2026-06-30. (1) Enumera todas las filas que cambian, en qué tablas y con qué valores. (2) ¿Por qué las tres operaciones tienen que ir en la misma transacción, y qué pasaría si fallara la tercera después de las dos primeras? (3) ¿Qué efecto tiene la devolución sobre el índice único parcial?

Un compañero propone añadir a obras una columna num_ejemplares INTEGER "para no tener que contar cada vez". (1) Da tres argumentos en contra. (2) Da un escenario en el que sí sería defendible. (3) Si hubiera que hacerlo, ¿cómo lo mantendrías y qué te costaría?

Solución 1 — (1) Ninguna, y por motivos distintos. El máximo de renovaciones depende del tipo del socio, que está en otra tabla, y un CHECK no puede consultar otras tablas (05-01): lo único declarable es el rango absoluto BETWEEN 0 AND 2. Que el préstamo esté vencido depende de CURRENT_DATE, y un CHECK con función no determinista está desaconsejado —una fila válida hoy dejaría de serlo mañana y una restauración de copia fallaría sin motivo—. Las reservas están en otra tabla. Las tres son lógica de operación. (2) Todo en el WHERE, que es donde se comprueba sin leer antes:

(3) Devuelve UPDATE 0. El préstamo 32 es de Sofía Terán con el ejemplar ALV-0001, de El jardín de las horas — la obra que tiene tres socios en la cola. La tercera condición lo bloquea, y hace bien: renovarlo dejaría a Nuno Barros esperando un mes más por un libro cuyos tres ejemplares están fuera. Y UPDATE 0 no es un error: hay que comprobarlo en la aplicación y traducirlo a un mensaje.

Solución 2 — (1) Cambian tres filas en tres tablas:

(2) Porque las tres son una sola operación de negocio: un ejemplar devuelto que genera su multa y activa su reserva. Si fallara la tercera sin transacción, quedaría un préstamo cerrado con su multa correcta y una cola que nadie ha avisado: Nuno seguiría esperando un libro que ya está en el mostrador, y sin huella del fallo. Es ACID en su forma más simple (09-01, 09-02): o las tres, o ninguna. En PL/pgSQL el bloque del procedimiento ya es una transacción implícita, así que una excepción en el paso 3 deshace los dos primeros. (3) Al dejar de estar activo, el préstamo 29 sale del índice único parcial —su fila ya no cumple WHERE fecha_devolucion IS NULL— y el ejemplar ALV-0003 vuelve a poder prestarse. La restricción se suelta sola: esa es la elegancia del índice parcial frente a una columna activo que alguien tendría que acordarse de cambiar.

Solución 3 — (1) Primero, es redundancia pura: el dato ya está en ejemplares y un COUNT(*) lo obtiene en microsegundos con el índice de ejemplares(obra_id). Segundo, hay que mantenerlo en cada alta, baja y traslado de ejemplar; el día que alguien inserte desde un script, la cifra queda mal para siempre y nadie se entera, porque un número plausible no salta a la vista. Tercero, no responde a la pregunta real: nadie pregunta cuántos ejemplares hay, preguntan cuántos hay disponibles ahora, que depende de prestamos y cambia cada minuto.

(2) Sería defendible con millones de obras, en una pantalla de catálogo que muestra el recuento en cada resultado de búsqueda y se lee miles de veces por segundo, si el agregado se hubiera medido y fuera el cuello de botella. Es decir: cuando haya un EXPLAIN que lo justifique, no antes (08-04). Y aun así, la primera opción sería una vista materializada (10-01) refrescada cada noche, que aísla la redundancia en un objeto marcado como derivado en lugar de esconderla en una columna que parece un dato.

(3) Con un trigger AFTER INSERT OR UPDATE OR DELETE sobre ejemplares que sume o reste. El coste: cada escritura en ejemplares escribe también en obras, lo que crea contención sobre la fila de la obra —dos altas simultáneas de ejemplares del mismo título se serializan (09-05)— y añade lógica invisible; más una consulta de recálculo desde cero, para corregirlo cuando (no si) se desincronice. Todo eso, para ahorrar un COUNT.

Ya sabes construirlo:

• El modelo: doce tablas y dieciséis relaciones, con una N:M de clave primaria compuesta y una reflexiva. Y las cinco decisiones difíciles argumentadas: ejemplares es tabla porque el objeto tiene sede, estado e historia propios; fecha_prevista se guarda porque es dato histórico y además se mueve con las renovaciones, igual que precio_unitario; las multas son tabla porque tienen ciclo de vida; la cola se calcula desde fecha_reserva en vez de guardar una posición; y el estado del préstamo se deriva mientras el del ejemplar se almacena, porque uno es función del reloj y el otro un hecho físico.
• El DDL, con restricciones nombradas y anotadas con su RI-nn, y el índice único parcial WHERE fecha_devolucion IS NULL: declarativo, diminuto y útil también como índice, con su límite reconocido —no detecta solapamientos históricos—. Los datos: a mano lo que aparece en las consultas, generate_series para el volumen (con todas las filas devueltas, o RI-03 aborta la carga) y los casos límite sin los cuales una consulta mal escrita pasa por buena, con los recuentos y los ocho huecos verificados antes de escribir la primera consulta.
• El método: empezar por el FROM decidiendo qué es una fila del resultado, comprobar el recuento tras cada JOIN, elegir LEFT JOIN preguntando por los ceros y validar el total por dos caminos. Los dos ejemplos resueltos enseñan las dos trampas: la condición del LEFT JOIN va en el ON, y COUNT(*) cuenta la fila fantasma.
• Los índices después de las consultas, no al revés; tres encapsulados y ni uno más —la vista de vencidos con su honesto multa_estimada, el procedimiento atómico de devolución y el único trigger necesario—; y tres roles de privilegio mínimo verificados con has_table_privilege.

Ya lo has construido; ahora toca compararlo, que es donde de verdad se aprende. En la lección siguiente, Soluciones comentadas del proyecto, está el solucionario: las decisiones del esquema justificadas una a una con las alternativas que también serían correctas —incluida la restricción EXCLUDE y el debate entre estado derivado y almacenado—; las 15 consultas resueltas con su resultado, su decisión clave y el error típico de cada una; los índices de referencia con el plan que los aprovecha; el código de la vista, el procedimiento y el trigger; y el catálogo de lo que más falla en este proyecto en concreto.