Diseño de Base de Datos: Library System

Sobre el Proyecto

El proyecto Library System es una implementación de base de datos relacional desarrollada en MySQL 8.0, concebida para modelar la operativa diaria de una biblioteca moderna. A diferencia de un sistema de ventas estándar, este modelo aborda los desafíos logísticos de rastrear inventario físico circulante, la gestión concurrente de reservas y la trazabilidad histórica de préstamos devueltos y vencidos.

El objetivo central del proyecto es diseñar un esquema robusto que garantice la integridad referencial de los datos y prevenga anomalías en las operaciones de inserción, actualización o borrado mediante una rigurosa aplicación de la Tercera Forma Normal (3NF).

Estructura de Archivos del Proyecto

Para evidenciar el desacoplamiento entre el diseño lógico de la base de datos y la interfaz interactiva, la capa de persistencia y definición de datos se organiza de forma modular en archivos independientes de definición y carga:

Arquitectura y Normalización

Para alcanzar un nivel profesional en el modelado, la arquitectura de la base de datos se ha dividido en 11 tablas principales y 2 tablas intermedias diseñadas para resolver relaciones muchos a muchos.

Separación Lógica vs Física

Uno de los mayores aciertos de diseño en el modelado de la biblioteca es la disociación estricta entre la entidad lógica (el libro como concepto y metadatos) y la entidad física (los ejemplares tangibles que circulan fuera del sistema).

Resolución de Relaciones Muchos-a-Muchos (M:N)

En el contexto editorial, un libro puede pertenecer a varios géneros simultáneamente y ser escrito en coautoría por múltiples autores. A su vez, un mismo autor puede tener diversas obras en el sistema. Para resolver este escenario sin comprometer la normalización, se implementaron tablas intermedias de vinculación:

• book_category: Permite asignar múltiples categorías literarias (Ej: Ficción y Misterio) a una obra mediante una clave primaria compuesta por la combinación de (book_id, category_id).
• book_author: No solo vincula al autor con el libro, sino que introduce un atributo exclusivo de la relación: el author_role. Esto permite segmentar y definir roles específicos como “Autor Principal”, “Coautor”, “Ilustrador” o “Editor”, lo cual es indispensable para el catálogo de obras colectivas.

Análisis de Consultas (SQL Queries)

El esquema relacional diseñado permite realizar agrupaciones y JOINs complejos para la toma de decisiones en tiempo real y el control administrativo del inventario. A continuación se desglosa el análisis técnico de las tres consultas principales de la base de datos.

SELECT
  b.book_id,
  b.title AS libro,
  GROUP_CONCAT(DISTINCT c.name SEPARATOR ', ') AS categoria,
  GROUP_CONCAT(DISTINCT CONCAT(a.first_name,' ',a.last_name) 
               ORDER BY a.last_name SEPARATOR ', ') AS autores
FROM book b
JOIN book_category bc ON bc.book_id = b.book_id
JOIN category c ON c.category_id = bc.category_id
JOIN book_author ba ON ba.book_id = b.book_id
JOIN author a ON a.author_id = ba.author_id
GROUP BY b.book_id, b.title
ORDER BY b.book_id ASC;

SELECT
  u.user_id,
  CONCAT(u.first_name, ' ', u.last_name) AS usuario,
  COUNT(*) AS prestamos_no_devueltos,
  SUM(CASE WHEN l.status = 'Overdue' THEN 1 ELSE 0 END) AS prestamos_vencidos
FROM library_user u
JOIN loan l ON l.user_id = u.user_id
WHERE l.status IN ('Active','Overdue')
GROUP BY u.user_id, usuario
ORDER BY prestamos_vencidos DESC, prestamos_no_devueltos DESC;

SELECT
  cp.copy_id AS ejemplar_id,
  b.title AS libro,
  cp.status AS estado_ejemplar,
  (SELECT COUNT(*)
    FROM reservation_queue q
    WHERE q.book_id = b.book_id
      AND q.status = 'Waiting'
  ) AS usuarios_esperando
FROM copy cp
JOIN book b ON b.book_id = cp.book_id
WHERE cp.status IN ('Borrowed','Reserved','Maintenance')
ORDER BY usuarios_esperando DESC, b.title;

Restricciones de Integridad (Constraints)

El diseño delega la consistencia de los datos directamente en el motor relacional mediante restricciones estrictas. Esto previene que se almacene información corrupta o inconsistente sin depender de validaciones complejas en la capa de backend:

Aspectos de Mejora y Futuras Fases

Para evolucionar el proyecto hacia un entorno de producción real, se implementarán las siguientes características relacionales avanzadas:

• Automatización de Inventario mediante Triggers: Configurar disparadores automáticos en la base de datos. Al actualizar un préstamo como devuelto (UPDATE loan SET status = 'Returned'), un trigger revisará la reservation_queue. Si hay usuarios esperando, asignará la copia físicamente al primer usuario (estado Reserved) y reordenará la cola. Si no hay espera, la copia volverá automáticamente a Available.
• Encapsulamiento con Vistas (Views): Crear vistas dedicadas de base de datos para simplificar las queries complejas (como reportes de inventario y balances administrativos de morosidad), agilizando el consumo seguro desde servicios web API REST externos.
• Control de Accesos DCL (Data Control Language): Segregar roles y seguridad interna creando usuarios dedicados de motor (Librarian, Member, Admin) y limitando los accesos a tablas y operaciones DDL/DML mediante sentencias GRANT y REVOKE.