Acciones

Historial

Guía Diseño de Bases de Datos Relacionales¶

1. Introducción al Diseño de Bases de Datos Relacionales¶

Una base de datos relacional organiza los datos en tablas relacionadas mediante claves primarias y foráneas, basándose en el modelo relacional de Edgar F. Codd. Un diseño bien estructurado optimiza el almacenamiento, asegura la integridad de los datos y facilita consultas eficientes.

Objetivos del diseño:¶

Minimizar redundancia: Evitar datos duplicados para reducir el uso de almacenamiento y prevenir inconsistencias.
Garantizar integridad: Asegurar que los datos sean precisos y consistentes mediante restricciones.
Optimizar consultas: Estructurar los datos para un acceso rápido y eficiente.
Escalabilidad y mantenimiento: Diseñar para soportar el crecimiento y facilitar modificaciones futuras.
Seguridad: Proteger los datos mediante controles de acceso y encriptación.

Beneficios de un buen diseño:¶

Mejora del rendimiento de las aplicaciones.
Reducción de errores en la manipulación de datos.
Facilidad para generar informes y análisis.
Adaptabilidad a cambios en los requisitos del negocio.

2. Pasos para el Diseño de una Base de Datos Relacional¶

2.1. Análisis de Requisitos¶

El primer paso es comprender el dominio del problema y los requisitos del sistema.

Identificar entidades principales: Determinar los objetos clave del sistema (ej. Clientes, Pedidos, Productos, Categorías).
Definir atributos: Especificar las propiedades de cada entidad (ej. Cliente: id_cliente, nombre, email, teléfono, dirección).
Establecer relaciones: Identificar cómo interactúan las entidades (ej. Un Cliente realiza múltiples Pedidos, un Pedido contiene varios Productos).
Reglas de negocio: Documentar restricciones específicas, como límites en cantidades, formatos de datos o dependencias (ej. Un pedido no puede existir sin un cliente).
Consultas esperadas: Anticipar las consultas más frecuentes para optimizar la estructura (ej. Búsquedas por cliente, reportes de ventas por producto).
Volumen de datos: Estimar la cantidad de datos y el crecimiento esperado para planificar la escalabilidad.

Ejemplo: Para una tienda online, las entidades podrían ser:

Clientes: Personas que compran.
Productos: Artículos en venta.
Pedidos: Solicitudes de compra.
Categorías: Grupos de productos.
Detalle_Pedido: Relación entre Pedidos y Productos.

2.2. Diseño Conceptual¶

El diseño conceptual utiliza un Diagrama Entidad-Relación (ER) para modelar las entidades, sus atributos y relaciones.

Componentes del Diagrama ER:
- Entidades: Representadas por rectángulos (ej. Cliente, Pedido).
- Atributos: Representados por óvalos conectados a las entidades (ej. nombre, fecha).
- Relaciones: Representadas por rombos que conectan entidades, con cardinalidades (1:1, 1:N, N:M).
- Claves:
  - Primaria (PK): Identificador único de cada registro.
  - Foránea (FK): Campo que referencia la PK de otra tabla.
- Cardinalidades:
  - 1:1 (uno a uno): Ej. Un Cliente tiene una Dirección única.
  - 1:N (uno a muchos): Ej. Un Cliente realiza muchos Pedidos.
  - N:M (muchos a muchos): Ej. Productos y Pedidos, resueltos mediante una tabla intermedia (Detalle_Pedido).
Ejemplo de Diagrama ER:
- Entidad Cliente (PK: id_cliente, nombre, email, teléfono).
- Entidad Pedido (PK: id_pedido, id_cliente (FK), fecha, total).
- Entidad Producto (PK: id_producto, nombre, precio, id_categoria (FK)).
- Entidad Categoría (PK: id_categoria, nombre).
- Entidad Detalle_Pedido (PK: id_pedido, id_producto, cantidad).
- Relaciones:
  - Cliente → Pedido (1:N).
  - Pedido → Detalle_Pedido (1:N).
  - Producto → Detalle_Pedido (1:N).
  - Categoría → Producto (1:N).

2.3. Normalización¶

La normalización organiza los datos en tablas para eliminar redundancias y anomalías en inserciones, actualizaciones y eliminaciones. Se divide en varias formas normales:

Primera Forma Normal (1NF):
- Eliminar grupos repetitivos o listas en una sola columna.
- Asegurar que cada atributo contenga valores atómicos.
- Ejemplo: Una columna "teléfonos" con valores como "123456, 789012" se divide en una tabla separada:
```
CREATE TABLE Telefonos (
    id_cliente INT,
    telefono VARCHAR(20),
    PRIMARY KEY (id_cliente, telefono),
    FOREIGN KEY (id_cliente) REFERENCES Clientes(id_cliente)
);
```
Segunda Forma Normal (2NF):
- Cumplir 1NF.
- Asegurar que todos los atributos no clave dependan completamente de la clave primaria.
- Ejemplo: Si una tabla mezcla datos de Pedidos y Clientes (ej. id_pedido, id_cliente, nombre_cliente), separar el nombre_cliente en la tabla Clientes.
Tercera Forma Normal (3NF):
- Cumplir 2NF.
- Eliminar dependencias transitivas (atributos que dependen de otros atributos no clave).
- Ejemplo: Si una tabla Pedidos tiene dirección_cliente, moverla a la tabla Clientes, ya que depende de id_cliente, no de id_pedido.
Forma Normal de Boyce-Codd (BCNF):
- Cumplir 3NF.
- Asegurar que no haya dependencias funcionales donde un atributo no clave determine otro atributo.
- Usada en casos complejos con múltiples claves candidatas.
Cuarta Forma Normal (4NF):
- Eliminar dependencias multivaluadas (relaciones N:M implícitas).
- Ejemplo: Separar una tabla que combine productos, colores y tamaños en tablas relacionadas.

Nota: La normalización excesiva puede afectar el rendimiento. En algunos casos, se desnormaliza intencionalmente para mejorar la velocidad de consultas (ej. agregar columnas calculadas como total_pedido).

2.4. Diseño Lógico¶

El diseño lógico convierte el modelo conceptual en un esquema relacional específico para un SGBD.

Definir tablas:

Cada entidad se convierte en una tabla.
Las relaciones N:M se implementan con tablas intermedias.

Ejemplo:

CREATE TABLE Clientes (
    id_cliente INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    telefono VARCHAR(20),
    direccion VARCHAR(200)
);

CREATE TABLE Categorias (
    id_categoria INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL
);

CREATE TABLE Productos (
    id_producto INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    id_categoria INT,
    FOREIGN KEY (id_categoria) REFERENCES Categorias(id_categoria)
);

CREATE TABLE Pedidos (
    id_pedido INT PRIMARY KEY AUTO_INCREMENT,
    id_cliente INT,
    fecha DATE NOT NULL,
    total DECIMAL(10,2),
    FOREIGN KEY (id_cliente) REFERENCES Clientes(id_cliente)
);

CREATE TABLE Detalle_Pedido (
    id_pedido INT,
    id_producto INT,
    cantidad INT NOT NULL,
    precio_unitario DECIMAL(10,2),
    PRIMARY KEY (id_pedido, id_producto),
    FOREIGN KEY (id_pedido) REFERENCES Pedidos(id_pedido),
    FOREIGN KEY (id_producto) REFERENCES Productos(id_producto)
);

Tipos de datos:
- Elegir tipos adecuados según el SGBD (ej. INT, VARCHAR, DECIMAL, DATE, BOOLEAN).
- Considerar restricciones de longitud (ej. VARCHAR(100) para nombres).
- Usar tipos precisos para ahorrar espacio (ej. TINYINT para valores pequeños).
Restricciones:
- Clave primaria: Garantiza unicidad (ej. AUTO_INCREMENT para IDs).
- Clave foránea: Mantiene la integridad referencial.
- Unicidad: Evita duplicados (ej. email único en Clientes).
- No nulo: Obliga a que ciertos campos siempre tengan valor.
- Valores por defecto: Define valores iniciales (ej. fecha = CURRENT_DATE).
- Chequeos: Restringe valores (ej. precio > 0).

2.5. Diseño Físico¶

El diseño físico optimiza el esquema para el entorno de hardware y el SGBD específico.

Índices:
- Crear índices en columnas usadas frecuentemente en consultas (ej. WHERE, JOIN, ORDER BY).
- Ejemplo:
```
CREATE INDEX idx_email ON Clientes(email);
```
- Tipos: Índices únicos, compuestos, de texto completo (para búsquedas de texto).

Particionamiento:

Dividir tablas grandes por rangos (ej. Pedidos por año) o listas (ej. por región).

Ejemplo:

CREATE TABLE Pedidos (
    id_pedido INT,
    id_cliente INT,
    fecha DATE,
    total DECIMAL(10,2),
    PRIMARY KEY (id_pedido, fecha)
) PARTITION BY RANGE (YEAR(fecha)) (
    PARTITION p0 VALUES LESS THAN (2023),
    PARTITION p1 VALUES LESS THAN (2024),
    PARTITION p2 VALUES LESS THAN (2025)
);

Optimización de almacenamiento:
- Usar motores de almacenamiento adecuados (ej. InnoDB para transacciones, MyISAM para lecturas rápidas en MySQL).
- Comprimir datos si es necesario.
- Ajustar tamaños de columnas para minimizar el uso de espacio.
Seguridad:
- Definir roles y permisos (ej. solo lectura para usuarios, escritura para administradores).
- Encriptar datos sensibles (ej. contraseñas con funciones hash).
- Ejemplo:
```
GRANT SELECT, INSERT ON Clientes TO 'app_user'@'localhost';
```
Respaldo y recuperación:
- Planificar copias de seguridad automáticas.
- Definir estrategias de recuperación ante fallos.

2.6. Pruebas y Validación¶

Datos de prueba: Insertar datos simulados para probar el esquema.

Ejemplo:

INSERT INTO Clientes (nombre, email, telefono, direccion)
VALUES ('Juan Pérez', 'juan@example.com', '123456789', 'Calle 123');
INSERT INTO Productos (nombre, precio, id_categoria)
VALUES ('Laptop', 999.99, 1);

Pruebas de consultas: Ejecutar consultas comunes y medir el rendimiento.
Validar integridad: Verificar que las restricciones (PK, FK, etc.) funcionen correctamente.
Simular carga: Probar con grandes volúmenes de datos para evaluar escalabilidad.

3. Mejores Prácticas¶

Nomenclatura clara:
- Usar nombres descriptivos y consistentes (ej. id_cliente, fecha_pedido).
- Evitar palabras reservadas del SGBD.
- Usar singular para nombres de tablas (ej. Cliente, no Clientes).

Documentación:

Crear un diccionario de datos con:
- Nombre de la tabla.
- Descripción de cada columna (tipo, restricciones, propósito).
- Relaciones y cardinalidades.

Ejemplo:

Tabla	Columna	Tipo	Descripción	Restricciones
Clientes	id_cliente	INT	Identificador único del cliente	PK, AUTO_INCREMENT
Clientes	email	VARCHAR(100)	Correo electrónico del cliente	UNIQUE, NOT NULL
Pedidos	id_pedido	INT	Identificador único del pedido	PK, AUTO_INCREMENT

Evitar sobre-normalización:
- No dividir tablas innecesariamente si afecta el rendimiento.
- Ejemplo: Mantener un campo calculado como total_pedido si las consultas lo usan frecuentemente.
Índices balanceados:
- Crear índices solo en columnas usadas en consultas frecuentes.
- Evitar índices excesivos, ya que ralentizan las operaciones de escritura.
Escalabilidad:
- Diseñar con el crecimiento en mente (ej. particionamiento, sharding).
- Considerar el uso de vistas o tablas materializadas para consultas complejas.
Mantenimiento:
- Monitorear el rendimiento con herramientas del SGBD (ej. EXPLAIN en MySQL).
- Actualizar el esquema según cambien los requisitos.
- Archivar datos antiguos para mejorar el rendimiento.
Seguridad:
- Encriptar datos sensibles (ej. información personal).
- Usar conexiones seguras (SSL/TLS) para acceder a la base de datos.
- Implementar auditorías para rastrear cambios.

4. Ejemplo Práctico: Tienda Online¶

Contexto¶

Una tienda online necesita gestionar clientes, productos, categorías, pedidos y detalles de pedidos. Los requisitos incluyen:

Los clientes pueden realizar múltiples pedidos.
Cada pedido contiene varios productos.
Los productos pertenecen a una categoría.
Se deben registrar la cantidad y el precio unitario en cada pedido.

Diagrama ER¶

Entidades:
- Cliente (id_cliente, nombre, email, teléfono, dirección).
- Categoría (id_categoria, nombre).
- Producto (id_producto, nombre, precio, id_categoria).
- Pedido (id_pedido, id_cliente, fecha, total).
- Detalle_Pedido (id_pedido, id_producto, cantidad, precio_unitario).
Relaciones:
- Cliente → Pedido (1:N).
- Categoría → Producto (1:N).
- Pedido → Detalle_Pedido (1:N).
- Producto → Detalle_Pedido (1:N).

Esquema SQL¶

-- Tabla Clientes
CREATE TABLE Clientes (
    id_cliente INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    telefono VARCHAR(20),
    direccion VARCHAR(200),
    fecha_registro DATE DEFAULT CURRENT_DATE
);

-- Tabla Categorías
CREATE TABLE Categorias (
    id_categoria INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL,
    descripcion TEXT
);

-- Tabla Productos
CREATE TABLE Productos (
    id_producto INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) NOT NULL CHECK (precio >= 0),
    id_categoria INT,
    stock INT NOT NULL CHECK (stock >= 0),
    FOREIGN KEY (id_categoria) REFERENCES Categorias(id_categoria)
);

-- Tabla Pedidos
CREATE TABLE Pedidos (
    id_pedido INT PRIMARY KEY AUTO_INCREMENT,
    id_cliente INT,
    fecha DATE NOT NULL DEFAULT CURRENT_DATE,
    total DECIMAL(10,2) CHECK (total >= 0),
    estado ENUM('pendiente', 'completado', 'cancelado') DEFAULT 'pendiente',
    FOREIGN KEY (id_cliente) REFERENCES Clientes(id_cliente)
);

-- Tabla Detalle_Pedido
CREATE TABLE Detalle_Pedido (
    id_pedido INT,
    id_producto INT,
    cantidad INT NOT NULL CHECK (cantidad > 0),
    precio_unitario DECIMAL(10,2) NOT NULL CHECK (precio_unitario >= 0),
    PRIMARY KEY (id_pedido, id_producto),
    FOREIGN KEY (id_pedido) REFERENCES Pedidos(id_pedido),
    FOREIGN KEY (id_producto) REFERENCES Productos(id_producto)
);

-- Índices para mejorar consultas
CREATE INDEX idx_cliente_email ON Clientes(email);
CREATE INDEX idx_pedido_fecha ON Pedidos(fecha);
CREATE INDEX idx_producto_categoria ON Productos(id_categoria);

Datos de Prueba¶

-- Insertar categorías
INSERT INTO Categorias (nombre, descripcion)
VALUES ('Electrónica', 'Dispositivos electrónicos'), ('Ropa', 'Ropa de moda');

-- Insertar productos
INSERT INTO Productos (nombre, precio, id_categoria, stock)
VALUES ('Laptop', 999.99, 1, 50), ('Camiseta', 19.99, 2, 100);

-- Insertar clientes
INSERT INTO Clientes (nombre, email, telefono, direccion)
VALUES ('Juan Pérez', 'juan@example.com', '123456789', 'Calle 123, Ciudad');

-- Insertar pedido
INSERT INTO Pedidos (id_cliente, fecha, total, estado)
VALUES (1, '2025-08-01', 1019.98, 'pendiente');

-- Insertar detalles del pedido
INSERT INTO Detalle_Pedido (id_pedido, id_producto, cantidad, precio_unitario)
VALUES (1, 1, 1, 999.99), (1, 2, 1, 19.99);

Consultas de Ejemplo¶

Obtener todos los pedidos de un cliente:

SELECT p.id_pedido, p.fecha, p.total, c.nombre
FROM Pedidos p
JOIN Clientes c ON p.id_cliente = c.id_cliente
WHERE c.email = 'juan@example.com';

Listar productos en un pedido:

SELECT dp.id_pedido, pr.nombre, dp.cantidad, dp.precio_unitario
FROM Detalle_Pedido dp
JOIN Productos pr ON dp.id_producto = pr.id_producto
WHERE dp.id_pedido = 1;

Reporte de ventas por categoría:

SELECT cat.nombre, SUM(dp.cantidad * dp.precio_unitario) AS total_ventas
FROM Detalle_Pedido dp
JOIN Productos pr ON dp.id_producto = pr.id_producto
JOIN Categorias cat ON pr.id_categoria = cat.id_categoria
GROUP BY cat.nombre;

5. Herramientas Recomendadas¶

Diseño de Diagramas ER:
- Dbdiagram.io: Herramienta online para crear diagramas ER.
- Lucidchart: Plataforma colaborativa para modelado.
- MySQL Workbench: Software gratuito con herramientas de diseño y gestión.
- Draw.io: Integrado con nubes como Google Drive.
Sistemas Gestores de Bases de Datos (SGBD):
- MySQL: Popular, fácil de usar, ideal para aplicaciones web.
- PostgreSQL: Robusto, con soporte para funciones avanzadas.
- SQL Server: Ideal para entornos empresariales de Microsoft.
- Oracle: Para sistemas de gran escala.
- SQLite: Ligero, para aplicaciones pequeñas o móviles.
Pruebas y Validación:
- SQL Fiddle: Entorno online para probar consultas.
- DB Fiddle: Similar a SQL Fiddle, con soporte para múltiples SGBD.
- DBeaver: Cliente SQL universal para gestionar bases de datos.
Monitoreo y Optimización:
- pgAdmin (PostgreSQL): Interfaz para administrar bases de datos.
- SQL Server Management Studio (SSMS): Para SQL Server.
- Percona Monitoring and Management: Para monitorear MySQL/PostgreSQL.

6. Consideraciones Avanzadas¶

Desnormalización estratégica:
- En sistemas con alta demanda de lecturas, agregar columnas redundantes (ej. total_pedido en Pedidos) para evitar cálculos repetitivos.
- Usar vistas materializadas para almacenar resultados de consultas complejas.
Sharding:
- Dividir la base de datos en fragmentos (shards) para distribuir la carga en múltiples servidores.
- Ejemplo: Almacenar clientes por región geográfica.
Replicación:
- Configurar réplicas de la base de datos para alta disponibilidad y tolerancia a fallos.
- Ejemplo: Una base de datos maestra para escrituras y réplicas para lecturas.

Optimización de consultas:

Usar EXPLAIN o ANALYZE para analizar planes de ejecución.
Reescribir consultas para evitar subconsultas innecesarias.

Ejemplo:

-- En lugar de:
SELECT * FROM Pedidos WHERE id_cliente IN (SELECT id_cliente FROM Clientes WHERE email LIKE '%example.com');
-- Usar JOIN:
SELECT p.* FROM Pedidos p JOIN Clientes c ON p.id_cliente = c.id_cliente WHERE c.email LIKE '%example.com';

Manejo de datos históricos:
- Archivar datos antiguos en tablas separadas (ej. Pedidos_Archivo).
- Usar particionamiento por rangos para datos temporales.

Auditoría:

Crear tablas de auditoría para registrar cambios (ej. quién modificó un registro y cuándo).

Ejemplo:

CREATE TABLE Auditoria (
    id_auditoria INT PRIMARY KEY AUTO_INCREMENT,
    tabla VARCHAR(50),
    accion VARCHAR(50),
    usuario VARCHAR(50),
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    datos_anteriores JSON,
    datos_nuevos JSON
);

Manejo de concurrencia:

Usar transacciones para operaciones críticas:

START TRANSACTION;
UPDATE Productos SET stock = stock - 1 WHERE id_producto = 1;
INSERT INTO Detalle_Pedido (id_pedido, id_producto, cantidad, precio_unitario)
VALUES (1, 1, 1, 999.99);
COMMIT;

Configurar niveles de aislamiento adecuados (ej. READ COMMITTED, SERIALIZABLE).

7. Consideraciones Finales¶

Iterar el diseño: Ajustar el esquema tras pruebas iniciales o cambios en los requisitos.
Monitoreo continuo: Usar herramientas del SGBD para identificar cuellos de botella.
Capacitación del equipo: Asegurar que los desarrolladores comprendan el diseño para evitar errores en consultas o manipulaciones.
Evolución del sistema: Planificar migraciones de datos para cambios estructurales (ej. agregar nuevas tablas o columnas).
Respaldo del diseño: Usar control de versiones para el esquema SQL (ej. Flyway, Liquibase).

Archivos (0)

Actualizado por Anibal Pendas Amador hace 4 días · 1 revisiones

Proyecto

General

Perfil

Sistema CRM (Customer Relationship Management)

Wiki

Guía Diseño de Bases de Datos Relacionales¶

1. Introducción al Diseño de Bases de Datos Relacionales¶

Objetivos del diseño:¶

Beneficios de un buen diseño:¶

2. Pasos para el Diseño de una Base de Datos Relacional¶

2.1. Análisis de Requisitos¶

2.2. Diseño Conceptual¶

2.3. Normalización¶

2.4. Diseño Lógico¶

2.5. Diseño Físico¶

2.6. Pruebas y Validación¶

3. Mejores Prácticas¶

4. Ejemplo Práctico: Tienda Online¶

Contexto¶

Diagrama ER¶

Esquema SQL¶

Datos de Prueba¶

Consultas de Ejemplo¶

5. Herramientas Recomendadas¶

6. Consideraciones Avanzadas¶

7. Consideraciones Finales¶