Diseño de un esquema físico completo

Diseñar un esquema físico consiste en convertir entidades y relaciones lógicas en tablas, columnas, claves y restricciones concretas para un SGBD. No es una traducción mecánica: cada decisión afecta a la integridad, el rendimiento y la facilidad de evolución.

1. Del modelo lógico al físico #

Antes de crear tablas, identifica:

• Las entidades que necesitan identidad propia.
• Las relaciones y su cardinalidad: 1:1, 1:N o N:M.
• Los atributos obligatorios y opcionales.
• Las reglas que debe garantizar la base de datos.
• Las consultas y escrituras más frecuentes.

Por ejemplo, un sistema de pedidos puede partir de las entidades Cliente, Pedido y Producto. La relación N:M entre pedidos y productos requiere una tabla puente, porque un pedido contiene varios productos y un producto puede aparecer en muchos pedidos.

2. Claves naturales y claves sustitutas #

Una clave natural procede del dominio, como un ISBN o un código fiscal. Una clave sustituta se crea exclusivamente para identificar filas, como un entero autogenerado o un UUID.

Cuándo usar cada una #

• Usa una clave natural cuando sea realmente única, estable, pequeña y no sensible.
• Usa una clave sustituta cuando la clave del negocio pueda cambiar, sea larga o esté compuesta por muchas columnas.
• Conserva una restricción UNIQUE sobre la clave natural aunque la clave primaria sea sustituta. Así no se pierden las reglas del negocio.

1CREATE TABLE productos (
2    id BIGINT PRIMARY KEY,
3    sku VARCHAR(40) NOT NULL UNIQUE,
4    nombre VARCHAR(150) NOT NULL
5);

Enteros frente a UUID #

• Los enteros son compactos, se ordenan bien y suelen producir índices pequeños.
• Los UUID pueden generarse sin coordinación central y son útiles en sistemas distribuidos.
• Un UUID aleatorio ocupa más espacio y puede fragmentar índices ordenados. Algunos motores ofrecen UUID ordenables o secuenciales.
• Un identificador difícil de adivinar no sustituye a la autorización.

3. Relaciones entre tablas #

Relación 1:N #

La clave foránea se coloca normalmente en el lado N. Un cliente puede tener muchos pedidos, pero cada pedido pertenece a un cliente:

1CREATE TABLE clientes (
2    id BIGINT PRIMARY KEY,
3    email VARCHAR(254) NOT NULL UNIQUE
4);
5
6CREATE TABLE pedidos (
7    id BIGINT PRIMARY KEY,
8    cliente_id BIGINT NOT NULL,
9    fecha TIMESTAMP NOT NULL,
10    CONSTRAINT fk_pedidos_cliente
11        FOREIGN KEY (cliente_id) REFERENCES clientes(id)
12);

Relación 1:1 #

Se implementa con una clave foránea UNIQUE. Si el registro dependiente no puede existir sin el principal, su clave primaria puede ser también la clave foránea.

1CREATE TABLE perfiles_cliente (
2    cliente_id BIGINT PRIMARY KEY,
3    biografia VARCHAR(500),
4    CONSTRAINT fk_perfil_cliente
5        FOREIGN KEY (cliente_id) REFERENCES clientes(id)
6);

Relación N:M #

Se crea una tabla puente. Su clave puede ser compuesta cuando la pareja identifica de forma natural cada fila:

1CREATE TABLE pedido_productos (
2    pedido_id BIGINT NOT NULL,
3    producto_id BIGINT NOT NULL,
4    cantidad INTEGER NOT NULL CHECK (cantidad > 0),
5    precio_unitario DECIMAL(12, 2) NOT NULL CHECK (precio_unitario >= 0),
6    PRIMARY KEY (pedido_id, producto_id),
7    FOREIGN KEY (pedido_id) REFERENCES pedidos(id),
8    FOREIGN KEY (producto_id) REFERENCES productos(id)
9);

precio_unitario pertenece a la línea del pedido, no al producto: conserva el precio aplicado cuando se realizó la compra.

4. Obligatoriedad, valores predeterminados y estados #

Usa NOT NULL cuando la ausencia del dato no tenga significado válido. Un valor predeterminado debe representar una regla real, no ocultar que la aplicación olvidó enviar un dato.

1CREATE TABLE pedidos (
2    id BIGINT PRIMARY KEY,
3    cliente_id BIGINT NOT NULL,
4    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
5    estado VARCHAR(20) NOT NULL DEFAULT 'pendiente',
6    CHECK (estado IN ('pendiente', 'pagado', 'enviado', 'cancelado')),
7    FOREIGN KEY (cliente_id) REFERENCES clientes(id)
8);

No uses una cadena vacía, 0 o una fecha ficticia para representar información desconocida. Es preferible modelar explícitamente el estado o permitir NULL cuando corresponda.

5. Convenciones y decisiones documentadas #

Mantén una convención consistente para:

• Nombres en singular o plural.
• Uso de snake_case o la convención acordada.
• Claves primarias y foráneas.
• Nombres de restricciones e índices.
• Fechas de creación y actualización.

Documenta también las unidades y significados. Una columna llamada duracion es ambigua; duracion_segundos expresa mejor el contrato.

6. Lista de comprobación #

Antes de aprobar el esquema, verifica:

1. Cada tabla tiene una clave primaria estable.
2. Las claves naturales conservan una restricción UNIQUE cuando procede.
3. Todas las relaciones están representadas mediante claves foráneas.
4. Las tablas puente impiden duplicar la misma relación.
5. Los tipos tienen precisión y tamaño adecuados.
6. NULL, DEFAULT y CHECK reflejan las reglas del dominio.
7. Las consultas principales pueden apoyarse en índices razonables.
8. Las decisiones específicas del motor están documentadas.