Diagrama ER: de la descripción al esquema
En resumen: Un diagrama ER (entidad-relación) muestra las entidades, sus atributos y las relaciones entre ellas con su cardinalidad, antes de que se conviertan en tablas con CREATE TABLE.
Problema
El cuaderno de la biblioteca
Una biblioteca de barrio apunta sus préstamos en un cuaderno. La bibliotecaria describe lo que quiere del programa nuevo:
«Tenemos unos 2 000 libros, algunos en varios ejemplares. Los socios se inscriben con nombre y correo electrónico. Cualquiera puede llevarse un libro por tres semanas. Quiero saber qué libros están fuera, quién los tiene y quién se ha retrasado. Y quiero buscar por autor.»
Antes del primer CREATE TABLE necesitas saber qué tablas hay, qué guardan y cómo se conectan. Si no, un mes después estarás rehaciendo el esquema con la base de datos llena.
La idea
Entidades y atributos
Recorres la descripción y buscas las entidades: las cosas de las que guardas datos. Suelen ser sustantivos.
- Socio: nombre, correo, cuándo se inscribió.
- Libro: título, año.
- Autor: nombre.
- Ejemplar: una copia física del libro, en un estante concreto. «Algunos en varios ejemplares» significa que el libro y el ejemplar son cosas distintas.
- Préstamo: qué ejemplar, qué socio, cuándo se llevó, cuándo vence, cuándo se devolvió.
Cada entidad se convierte en una tabla, y sus atributos en columnas. Un préstamo es un verbo, pero tiene atributos propios (fechas), así que también es una tabla.
«Cuáles están fuera» y «quién se ha retrasado» no son columnas. Son preguntas que responderán las consultas.
La idea
Relaciones y cardinalidad
Después dices cómo se conectan las entidades y cuántas con cuántas. El dibujo de esto se llama diagrama ER (entidad-relación). Su notación más común es la pata de gallo (crow's foot), llamada así por el signo de «muchos», que parece la pata de un ave.
Cada extremo de una línea tiene dos signos. El signo junto a la tabla dice cuántos como máximo, y el otro cuántos como mínimo. Así se escriben en el extremo derecho:
||exactamente uno;o|cero o uno;|{uno o muchos;o{cero o muchos.{es la «pata».
En el extremo izquierdo van en espejo: ||, |o, }|, }o.
Aquí está la biblioteca. La notación es Mermaid, que GitHub y muchos editores dibujan como imagen:
erDiagrammembers ||--o{ loans : "borrows"copies ||--o{ loans : "is lent"books ||--|{ copies : "has"books ||--|{ book_authors : "is by"authors ||--o{ book_authors : "writes"El texto después de los dos puntos es la etiqueta de la línea. Mermaid la exige, y no significa nada para el esquema.
members ||--o{ loans se lee así: un socio tiene cero o muchos préstamos, y cada préstamo pertenece a exactamente un socio. books ||--|{ copies: cada libro tiene al menos un ejemplar, y cada ejemplar es de exactamente un libro.
Pruébalo
¿Qué relación es?
La idea
Del diagrama a las tablas
Las reglas son de la lección anterior:
- 1:N: una clave foránea en el lado «muchos» (
copies.book_id,loans.copy_id,loans.member_id). - M:N: una tabla de enlace con una clave primaria de las dos columnas (
book_authors). - 1:1: una clave foránea con
UNIQUE. La biblioteca no tiene ninguna.
members
- PK
idinteger nametext *emailtext *joined_attext *
authors
- PK
idinteger nametext *
books
- PK
idinteger titletext *yearinteger
book_authors
- PK
book_idinteger→ books - PK
author_idinteger→ authors
copies
- PK
idinteger - FK
book_idinteger *→ books shelftext *
loans
- PK
idinteger - FK
copy_idinteger *→ copies - FK
member_idinteger *→ members loaned_attext *due_attext *returned_attext
Los campos de abajo tienen este esquema y unos cuantos libros y socios. ¿Qué ejemplares de «Bajo el yugo» hay, y en qué estante?
Más precisamente, para curiosos
returned_at es NULL mientras el libro está fuera. Así que una columna guarda a la vez «si se ha devuelto» y «cuándo». Otra opción es una columna status, pero entonces tienes que asegurarte de que las dos nunca se contradigan.
Los diagramas también se dibujan con herramientas como dbdiagram.io y draw.io, y la mayoría de los programas de bases de datos (DBeaver, DataGrip, pgAdmin) dibujan un diagrama ER a partir de una base existente. La notación es la misma.
Míralo
¿Libro o ejemplar?
La tentación es hacer que el préstamo apunte directamente al libro. Mira el historial de «Bajo el yugo»:
El ejemplar 1 estuvo con dos socios uno tras otro, y el ejemplar 2 lo tiene ahora Borislav. Si el préstamo guardara solo «Bajo el yugo», no sabrías cuál de los tres está fuera, cuál volvió roto ni cuántos hay en el estante.
Por eso, cuando diseñas, preguntas: ¿qué pasa exactamente en el mundo? El socio coge un objeto del estante, no un título.
Pruébalo
Qué está fuera
La bibliotecaria pregunta qué libros están fuera ahora mismo y quién los tiene. Muestra el título, el número de ejemplar y el socio.
Solución
SELECT b.title, c.id AS copy, m.name
FROM loans l
JOIN copies c ON c.id = l.copy_id
JOIN books b ON b.id = c.book_id
JOIN members m ON m.id = l.member_id
WHERE l.returned_at IS NULL;Un préstamo sin returned_at sigue fuera. Vas de loans a copies (qué ejemplar), de ahí a books (el título) y a members (quién lo tiene). Cinco ejemplares, entre ellos los dos de «Buenos presagios».
Ejercicio
Dos tareas
1. Hoy es 25 de octubre de 2026. ¿Quién se ha retrasado? Muestra el socio, el título y la fecha en que vencía el libro.
Los préstamos que no se han devuelto y ya pasaron su fecha de vencimiento:
Solución
SELECT m.name, b.title, l.due_at
FROM loans l
JOIN copies c ON c.id = l.copy_id
JOIN books b ON b.id = c.book_id
JOIN members m ON m.id = l.member_id
WHERE l.returned_at IS NULL AND l.due_at < '2026-10-25';Dos condiciones: el libro no se ha devuelto (returned_at IS NULL) y la fecha de vencimiento es anterior a hoy (due_at < '2026-10-25'). Borislav y Galin. Anna tiene que devolver el suyo mañana, así que todavía no se ha retrasado.
2. La búsqueda por autor en el catálogo muestra cada libro con sus autores. Muestra el título y el nombre del autor.
Todos los libros con sus autores:
Solución
SELECT b.title, a.name
FROM books b
JOIN book_authors ba ON ba.book_id = b.id
JOIN authors a ON a.id = ba.author_id;Pasas por la tabla de enlace book_authors. Obtienes 8 filas para 7 libros: «Buenos presagios» tiene dos autores y aparece dos veces.
Test