Diseño de bases de datos

Antes de escribir un solo CREATE TABLE hay que decidir qué tablas hay, qué columnas tienen y cómo se relacionan. Un buen diseño hace que las consultas sean sencillas y los datos no se contradigan; uno malo te persigue durante años. En esta lección verás el proceso completo con un ejemplo: la base de datos de un instituto.

Del problema al modelo

El diseño empieza lejos del ordenador, hablando con quien usará la aplicación. La secretaría del instituto te cuenta:

• Hay alumnos, cada uno con su nombre, que pertenecen a un grupo (1DAW-A, 1DAW-B…).
• Cada grupo tiene un tutor, que es un profesor. Un profesor es tutor de un grupo como mucho.
• Los alumnos se matriculan en varias asignaturas y en cada una sacan una nota.

Una pista útil: los sustantivos suelen ser entidades o atributos, y los verbos (“pertenece”, “se matricula”), relaciones.

El modelo entidad-relación

El modelo entidad-relación (E-R) es un dibujo del problema, independiente de MySQL o de cualquier otro sistema. Tiene tres elementos:

• Entidad: una “cosa” de la que guardas datos. Alumno, Grupo, Profesor, Asignatura.
• Atributo: un dato de una entidad. El nombre del alumno, el email del profesor.
• Relación: cómo se conectan dos entidades. Un alumno pertenece a un grupo.

Cardinalidad

La cardinalidad indica cuántos elementos de un lado se relacionan con cuántos del otro. Hay tres tipos:

1:1 (uno a uno). Un profesor es tutor de un grupo como mucho, y cada grupo tiene un único tutor.

PROFESOR  1 ──── tutoriza ──── 1  GRUPO

1:N (uno a muchos). Un grupo tiene muchos alumnos, pero cada alumno pertenece a un solo grupo. Es la más habitual: en la tienda del curso, un cliente tiene muchos pedidos.

GRUPO  1 ──── pertenece ──── N  ALUMNO

N:M (muchos a muchos). Un alumno cursa muchas asignaturas y cada asignatura la cursan muchos alumnos.

ALUMNO  N ──── se matricula (nota) ──── M  ASIGNATURA

Fíjate en que la nota no es de la entidad Alumno (tiene varias) ni de Asignatura (tiene muchas): es un atributo de la relación. Ana tiene un 8 en Bases de Datos.

El diagrama completo del instituto quedaría así:

┌──────────┐ 1      1 ┌─────────┐ 1      N ┌──────────┐ N      M ┌────────────┐
│ PROFESOR │──────────│  GRUPO  │──────────│  ALUMNO  │──────────│ ASIGNATURA │
│ nombre   │ tutoriza │ codigo  │ pertenece│ nombre   │  matric. │ codigo     │
│ email    │          │         │          │          │  (nota)  │ nombre     │
└──────────┘          └─────────┘          └──────────┘          └────────────┘

Del modelo E-R a las tablas

El siguiente paso es el modelo relacional: convertir el dibujo en tablas. Las reglas son pocas:

1. Cada entidad es una tabla, y cada atributo, una columna.
2. Cada tabla necesita una clave primaria que identifique cada fila.
3. Relación 1:N: la clave foránea va en la tabla del lado N. El alumno guarda el código de su grupo (alumnos.grupo_codigo), igual que el pedido guarda el cliente_id.
4. Relación 1:1: la clave foránea va en cualquiera de las dos tablas, con UNIQUE para que no se repita. Aquí, grupos.tutor_id.
5. Relación N:M: se crea una tabla intermedia con las claves de las dos entidades y los atributos de la relación. Aquí, matriculas(alumno_id, asignatura_codigo, nota).

La regla 5 es la que más cuesta: no puedes poner una columna asignaturas en alumnos (¿cuántas pondrías?). La tabla intermedia tiene una fila por cada pareja alumno-asignatura.

Claves

• Clave primaria natural: un dato real que ya es único, como el código de asignatura BD.
• Clave primaria artificial (o sustituta): un número sin significado, como id, normalmente con AUTO_INCREMENT. Es la más habitual porque nunca cambia. El DNI parece único, pero puede estar mal escrito o no existir.
• Clave primaria compuesta: varias columnas. En matriculas, la pareja (alumno_id, asignatura_codigo) identifica cada fila e impide matricular dos veces al mismo alumno en la misma asignatura.

Normalización: arreglar una tabla mal diseñada

La normalización es un conjunto de reglas (las formas normales) para eliminar datos repetidos y evitar contradicciones. Lo mejor para entenderla es partir de una tabla mal hecha, como la hoja de cálculo que te pasaría secretaría:

alumno_id	alumno	grupo	tutor	asignaturas
1	Ana García	1DAW-A	Carmen Sanz	BD (8), PROG (7)
2	Luis Pérez	1DAW-A	Carmen Sanz	BD (6)
3	Marta Ruiz	1DAW-B	Jorge Gil	BD (9), LM (7)

Primera forma normal (1FN): un valor por celda

Una tabla está en 1FN si cada celda contiene un solo valor y no hay grupos repetidos. La columna asignaturas la incumple: ¿cómo buscarías a los alumnos con más de un 7 en BD? Tendrías que trocear el texto.

La solución es una fila por cada alumno y asignatura:

alumno_id	alumno	grupo	tutor	asignatura_cod	asignatura	nota
1	Ana García	1DAW-A	Carmen Sanz	BD	Bases de Datos	8
1	Ana García	1DAW-A	Carmen Sanz	PROG	Programación	7
2	Luis Pérez	1DAW-A	Carmen Sanz	BD	Bases de Datos	6
3	Marta Ruiz	1DAW-B	Jorge Gil	BD	Bases de Datos	9
3	Marta Ruiz	1DAW-B	Jorge Gil	LM	Lenguajes de Marcas	7

La clave primaria es ahora (alumno_id, asignatura_cod). Ya se puede consultar, pero mira cuánto se repite: “Carmen Sanz” aparece tres veces y “Bases de Datos”, tres. Eso provoca las llamadas anomalías:

• De modificación: si Carmen se cambia el apellido, hay que cambiar varias filas. Si se te olvida una, la base de datos dice dos cosas distintas.
• De inserción: no puedes dar de alta una asignatura nueva hasta que alguien se matricule en ella, porque falta el alumno_id de la clave.
• De borrado: si Marta deja Lenguajes de Marcas, desaparece también el único sitio donde constaba el nombre de esa asignatura.

Segunda forma normal (2FN): depender de toda la clave

Una tabla está en 2FN si está en 1FN y cada columna que no es clave depende de toda la clave primaria, no solo de una parte. Solo afecta a tablas con clave compuesta.

Pregúntate de qué depende cada columna:

• alumno, grupo y tutor dependen solo de alumno_id.
• asignatura depende solo de asignatura_cod.
• nota depende de las dos: es la nota de ese alumno en esa asignatura.

Se separa cada grupo de columnas en su tabla:

alumnos     (alumno_id, alumno, grupo, tutor)
asignaturas (asignatura_cod, asignatura)
matriculas  (alumno_id, asignatura_cod, nota)

Ahora cada asignatura aparece una sola vez y puedes crearla aunque no tenga alumnos.

Tercera forma normal (3FN): nada de dependencias en cadena

Una tabla está en 3FN si está en 2FN y ninguna columna no clave depende de otra columna no clave. Mira alumnos: el tutor no depende del alumno, sino del grupo: todos los de 1DAW-A tienen a Carmen. Es una dependencia en cadena (transitiva): alumno_id → grupo → tutor. Si cambia el tutor de 1DAW-A, habría que actualizar a todos sus alumnos.

Se saca el grupo a su propia tabla, y ya que estamos, los profesores también, porque tienen más datos (el email):

profesores  (id, nombre, email)
grupos      (codigo, tutor_id)
alumnos     (id, nombre, grupo_codigo)
asignaturas (codigo, nombre)
matriculas  (alumno_id, asignatura_codigo, nota)

Una forma fácil de recordar las tres: cada columna no clave depende de la clave (1FN), de toda la clave (2FN) y de nada más que la clave (3FN).

Nota: existen formas normales superiores (FNBC, 4FN, 5FN), pero en la práctica una base de datos en 3FN está bien diseñada para casi cualquier aplicación.

El resultado en SQL

Así quedan las tablas del instituto, con sus claves primarias y foráneas:

CREATE TABLE profesores (
  id INT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE grupos (
  codigo VARCHAR(10) PRIMARY KEY,
  tutor_id INT UNIQUE,  -- 1:1 (UNIQUE: un profesor, un grupo como mucho)
  FOREIGN KEY (tutor_id) REFERENCES profesores(id)
);

CREATE TABLE alumnos (
  id INT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  grupo_codigo VARCHAR(10) NOT NULL,  -- 1:N
  FOREIGN KEY (grupo_codigo) REFERENCES grupos(codigo)
);

CREATE TABLE asignaturas (
  codigo VARCHAR(10) PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL
);

CREATE TABLE matriculas (  -- tabla intermedia N:M
  alumno_id INT,
  asignatura_codigo VARCHAR(10),
  nota DECIMAL(4, 2),
  PRIMARY KEY (alumno_id, asignatura_codigo),
  FOREIGN KEY (alumno_id) REFERENCES alumnos(id),
  FOREIGN KEY (asignatura_codigo) REFERENCES asignaturas(codigo)
);

Nota: las claves foráneas se escriben aquí al final de la tabla, con FOREIGN KEY, porque así funcionan en todos los sistemas. MySQL 8 acepta sin error las que se escriben junto a la columna (tutor_id INT REFERENCES profesores(id)), pero las ignora.

Con los datos cargados, las restricciones protegen el diseño:

INSERT INTO grupos VALUES ('2DAW-A', 1);      -- Error: tutor_id duplicado
INSERT INTO matriculas VALUES (4, 'BD', 5);   -- Error: el alumno 4 no existe

Y la tabla original se reconstruye cuando la necesites con varios JOIN:

SELECT a.nombre AS alumno, g.codigo AS grupo, p.nombre AS tutor,
       s.nombre AS asignatura, m.nota
FROM matriculas m
JOIN alumnos a ON a.id = m.alumno_id
JOIN asignaturas s ON s.codigo = m.asignatura_codigo
JOIN grupos g ON g.codigo = a.grupo_codigo
JOIN profesores p ON p.id = g.tutor_id
ORDER BY a.id, s.nombre;

alumno	grupo	tutor	asignatura	nota
Ana García	1DAW-A	Carmen Sanz	Bases de Datos	8.00
Ana García	1DAW-A	Carmen Sanz	Programación	7.00
Luis Pérez	1DAW-A	Carmen Sanz	Bases de Datos	6.00
Marta Ruiz	1DAW-B	Jorge Gil	Bases de Datos	9.00
Marta Ruiz	1DAW-B	Jorge Gil	Lenguajes de Marcas	7.00

Los datos no se repiten al guardarlos, pero puedes verlos juntos al consultarlos. Si esta consulta se usa mucho, guárdala en una vista.

Cuándo desnormalizar

Normalizar reduce las repeticiones, pero multiplica los JOIN. En sistemas con mucho tráfico a veces se desnormaliza a propósito: se repite un dato para no calcularlo cada vez. Ejemplos:

• Guardar el total de un pedido, aunque se pueda calcular sumando sus líneas.
• Guardar el precio en el momento de la compra en la línea del pedido: si el producto sube mañana, la factura de hoy no debe cambiar.
• Tablas de informes que se rellenan cada noche con datos ya agrupados.

La regla es: diseña primero en 3FN y desnormaliza solo cuando tengas un motivo medido, sabiendo que te toca mantener la copia al día.

Convenciones de nombres

No hay una norma única; lo importante es ser coherente:

• Todo en minúsculas, con palabras separadas por guiones bajos: fecha_alta, grupo_codigo.
• Sin tildes, ñ ni espacios: anio, no año.
• Tablas en plural (alumnos, pedidos), como en este curso, o en singular, pero sin mezclar.
• Clave primaria id; claves foráneas como cliente_id, alumno_id.
• Tablas intermedias con el nombre de la relación (matriculas) o de las dos tablas (alumnos_asignaturas).
• Evita palabras reservadas como nombres: order, group, user, date.

Herramientas

Dibujar el modelo antes de escribir SQL ahorra muchos errores:

• MySQL Workbench: dibuja el diagrama y genera el CREATE TABLE, o al revés.
• draw.io (diagrams.net): pizarra en el navegador con formas para diagramas E-R.
• dbdiagram.io: describes las tablas en texto, dibuja el diagrama y exporta a SQL.

Errores frecuentes

• Listas dentro de una celda ("BD, PROG, LM"). Incumple la 1FN: crea una tabla intermedia.
• Columnas numeradas (telefono1, telefono2…). Es la misma lista disfrazada: van en otra tabla.
• Guardar la edad, que cambia cada año. Guarda la fecha de nacimiento y calcúlala.
• Olvidar la tabla intermedia en una relación N:M e intentar resolverla con una clave foránea.
• Usar como clave primaria un dato que puede cambiar, como el email o el nombre.

Resumen

Paso o concepto	Idea clave
Modelo E-R	Entidades, atributos y relaciones, sin pensar aún en SQL
1:N	Clave foránea en la tabla del lado N
1:1	Clave foránea con UNIQUE en una de las dos tablas
N:M	Tabla intermedia con las dos claves
1FN	Un solo valor por celda
2FN	Cada columna depende de toda la clave
3FN	Ninguna columna depende de otra que no sea clave
Desnormalizar	Repetir datos a propósito, solo con motivo

Con el diseño hecho, el último paso es decidir quién puede hacer qué en la base de datos: usuarios y permisos.

| alumno_id | nombre     | telefonos              |
| 1         | Ana García | 600111222, 600333444   |