Modelado Lógico Relacional: Tablas, Claves y Tipos de Datos

Del modelo conceptual al esquema relacional

El modelado lógico relacional convierte lo que ya definiste a nivel conceptual (entidades, atributos y relaciones) en un diseño implementable en un SGBD relacional: tablas, columnas, claves primarias (PK), claves foráneas (FK) e índices. El objetivo es que el esquema sea consistente, eficiente para consultas comunes y fácil de mantener.

Regla base de traducción

• Entidad → Tabla
• Atributo → Columna
• Identificador → PK (natural o sustituta)
• Relación → FK o tabla puente (según cardinalidad)
• Restricciones → NOT NULL, UNIQUE, CHECK, DEFAULT, FOREIGN KEY

Guía práctica paso a paso

Paso 1: Definir tablas y PK

Para cada entidad, crea una tabla con una PK estable. En la práctica, es común usar PK sustitutas (por ejemplo, customer_id) para evitar cambios por negocio y simplificar FKs.

-- Ejemplo (DDL genérico, estilo PostgreSQL/SQL estándar aproximado)  CREATE TABLE customer (   customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   email VARCHAR(254) NOT NULL,   full_name VARCHAR(120) NOT NULL );

Si existe un identificador natural realmente estable (por ejemplo, un código interno inmutable), puedes usarlo como PK o al menos como UNIQUE.

Paso 2: Convertir atributos a columnas (nulabilidad y defaults)

Para cada atributo, decide: tipo de dato, longitud/precisión, si permite NULL y si tiene DEFAULT. La nulabilidad debe reflejar si el dato es obligatorio al momento de crear el registro (no si “a veces no lo sabemos”).

• Obligatorio desde el alta → NOT NULL
• Opcional → permite NULL (o modela “desconocido/no aplica” con un catálogo si es crítico)
• Valor por defecto → úsalo para estados iniciales coherentes (por ejemplo, status, created_at)

CREATE TABLE orders (   order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   customer_id BIGINT NOT NULL,   order_status VARCHAR(20) NOT NULL DEFAULT 'NEW',   created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,   total_amount NUMERIC(12,2) NOT NULL DEFAULT 0,   CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) );

Paso 3: Mapear relaciones (1:1, 1:N, N:M)

El mapeo de relaciones se materializa con FKs, restricciones UNIQUE y/o tablas puente. A continuación se muestran patrones implementables.

Continúa en nuestra aplicación.

• Escuche el audio con la pantalla apagada.
• Obtenga un certificado al finalizar.
• ¡Más de 5000 cursos para que explores!

O continúa leyendo más abajo...

Descargar la aplicación

Mapeo de relaciones con ejemplos

Relación 1:1 (FK única)

En una relación 1:1, cada fila de A se asocia con como máximo una fila de B y viceversa. En el esquema relacional, se implementa poniendo una FK en una de las tablas y asegurando unicidad con UNIQUE. La elección del lado depende de: obligatoriedad, frecuencia de acceso y si conviene separar columnas opcionales.

Ejemplo: customer y customer_profile (perfil opcional, extendido).

CREATE TABLE customer_profile (   customer_id BIGINT PRIMARY KEY,   birth_date DATE NULL,   phone VARCHAR(30) NULL,   CONSTRAINT fk_profile_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) );

En este patrón, la PK de customer_profile es también FK a customer, garantizando 1:1 (y además evita una columna extra).

Si necesitas que el lado “dependiente” tenga su propia PK, entonces usa FK + UNIQUE:

CREATE TABLE customer_profile (   profile_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   customer_id BIGINT NOT NULL UNIQUE,   phone VARCHAR(30) NULL,   CONSTRAINT fk_profile_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) );

Relación 1:N (FK en el lado N)

En una relación 1:N, una fila del lado 1 se asocia con muchas filas del lado N. Se implementa colocando la FK en la tabla del lado N.

Ejemplo: un customer tiene muchos orders.

CREATE TABLE orders (   order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   customer_id BIGINT NOT NULL,   created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,   CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) );

Si la relación es opcional del lado N (por ejemplo, un registro puede existir sin asociarse aún), la FK podría permitir NULL. Si no, mantenla NOT NULL.

Relación N:M (tabla puente)

En una relación N:M, múltiples filas de A se asocian con múltiples filas de B. Se implementa con una tabla puente (también llamada tabla de unión o intermedia) que contiene al menos dos FKs.

Ejemplo: product y orders (un pedido contiene muchos productos y un producto aparece en muchos pedidos). La tabla puente suele llamarse order_item.

CREATE TABLE product (   product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   sku VARCHAR(40) NOT NULL UNIQUE,   name VARCHAR(120) NOT NULL,   unit_price NUMERIC(12,2) NOT NULL );  CREATE TABLE order_item (   order_id BIGINT NOT NULL,   product_id BIGINT NOT NULL,   quantity INTEGER NOT NULL DEFAULT 1,   unit_price NUMERIC(12,2) NOT NULL,   PRIMARY KEY (order_id, product_id),   CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders(order_id),   CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product(product_id) );

Observa dos puntos clave:

• Atributos de la relación (por ejemplo, quantity, unit_price) viven en la tabla puente.
• La PK compuesta (order_id, product_id) evita duplicados del mismo producto en el mismo pedido. Si necesitas permitir múltiples líneas del mismo producto (por descuentos/lotes), usa una PK sustituta (order_item_id) y agrega una restricción alternativa según la regla (por ejemplo, UNIQUE(order_id, line_number)).

Claves, restricciones e índices básicos

Claves primarias (PK)

• Debe ser única y no nula.
• Preferible inmutable.
• Puede ser sustituta (IDENTITY/SEQUENCE/UUID) o natural (si es estable y corto).

Claves foráneas (FK)

Una FK asegura que el valor exista en la tabla referenciada. Define también el comportamiento ante borrado/actualización según tu política (por ejemplo, RESTRICT, CASCADE, SET NULL). Evita CASCADE por defecto en dominios donde un borrado accidental sería crítico.

ALTER TABLE orders   ADD CONSTRAINT fk_orders_customer   FOREIGN KEY (customer_id) REFERENCES customer(customer_id)   ON DELETE RESTRICT;

Restricciones útiles

• UNIQUE: para claves de negocio (email, sku) o para implementar 1:1.
• CHECK: rangos y reglas simples (cantidades positivas, estados permitidos).
• DEFAULT: valores iniciales consistentes.

ALTER TABLE order_item   ADD CONSTRAINT chk_quantity_positive CHECK (quantity > 0);

Índices básicos (qué crear y por qué)

Índices aceleran lecturas pero encarecen escrituras. Como base:

• PK: normalmente ya crea un índice.
• FKs: suele convenir indexarlas para joins y para validar integridad eficientemente.
• Claves de negocio con UNIQUE: suelen crear índice único.
• Columnas de búsqueda frecuente (por ejemplo, created_at en reportes) pueden requerir índice adicional.

CREATE INDEX idx_orders_customer_id ON orders(customer_id); CREATE INDEX idx_orders_created_at ON orders(created_at);

Criterios para selección de tipos de datos

La selección de tipos de datos impacta precisión, almacenamiento, rendimiento y compatibilidad entre motores. Prioriza tipos estándar y evita “tipos comodín” (por ejemplo, guardar fechas como texto).

Texto: VARCHAR vs CHAR vs TEXT

• VARCHAR(n): para texto con límite razonable (nombres, códigos, emails). El límite documenta expectativas y ayuda a validación.
• CHAR(n): útil para longitudes fijas (por ejemplo, códigos de 2 o 3 caracteres), aunque no siempre necesario.
• TEXT: para descripciones largas cuando no hay un máximo claro. Considera impacto en índices y búsquedas.

Consejo: define longitudes por criterio de negocio y estándares (por ejemplo, email hasta 254). Evita VARCHAR(255) “por costumbre” si puedes justificar otro valor.

Números: INTEGER/BIGINT vs NUMERIC/DECIMAL vs FLOAT

• INTEGER/BIGINT: para contadores, IDs, cantidades enteras.
• NUMERIC(p,s)/DECIMAL(p,s): para dinero y valores que requieren precisión exacta (ej. NUMERIC(12,2)).
• FLOAT/DOUBLE: para mediciones aproximadas (ciencia/telemetría), no recomendado para dinero.

Fechas y tiempo

• DATE: solo fecha.
• TIMESTAMP: fecha y hora. Si tu motor distingue zona horaria, define una convención (por ejemplo, almacenar en UTC).

Booleanos y estados

• BOOLEAN: para flags reales (sí/no).
• Para estados con más de dos valores, usa una columna de estado (texto corto o entero) y refuérzala con CHECK o una tabla catálogo, según necesidad.

ALTER TABLE orders   ADD CONSTRAINT chk_order_status   CHECK (order_status IN ('NEW','PAID','CANCELLED','SHIPPED'));

UUID vs BIGINT para identificadores

• BIGINT (IDENTITY/SEQUENCE): eficiente y compacto, ideal en la mayoría de sistemas centralizados.
• UUID: útil en sistemas distribuidos o cuando necesitas generar IDs fuera de la BD. Considera impacto en índices (más grandes) y fragmentación.

Longitudes, nulabilidad y valores por defecto: checklist

Checklist de decisiones por columna

Compatibilidad entre motores

Si tu diseño debe funcionar en varios SGBD, evita depender de detalles específicos (por ejemplo, tipos propietarios). Prefiere: VARCHAR, NUMERIC, DATE, TIMESTAMP, INTEGER/BIGINT. Cuando uses características específicas (IDENTITY, UUID, JSON), documéntalo en el diccionario de datos.

Convenciones de nombres (consistentes y legibles)

Una convención clara reduce errores y acelera el trabajo en equipo. Elige un estilo y aplícalo en todo el esquema.

Recomendación práctica

• Tablas: singular y en snake_case (ej. customer, order_item).
• PK: <tabla>_id (ej. customer_id).
• FK: mismo nombre que la PK referenciada (ej. customer_id en orders).
• Índices: idx_<tabla>_<columna(s)>.
• Constraints: pk_, fk_, uq_, chk_ + tabla/columna.
• Evitar: abreviaturas ambiguas (desc, num), nombres con espacios, y mezclar idiomas.

-- Ejemplos de nombres de constraints ALTER TABLE customer   ADD CONSTRAINT uq_customer_email UNIQUE (email);  ALTER TABLE orders   ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id);

Diccionario de datos (entregable del capítulo)

El diccionario de datos documenta el esquema para desarrollo, QA, analítica y operación. Debe ser lo bastante detallado para que alguien implemente o consuma la base sin “leer la mente” del diseñador.

Plantilla mínima recomendada

Ejemplo de diccionario de datos (extracto)

Mini-taller: traducir un caso a relacional

Escenario

Necesitas soportar: clientes, pedidos, productos y líneas de pedido. Además, un perfil extendido del cliente (opcional). Se requiere que email y sku sean únicos.

Aplicación de patrones

• Tablas: customer, customer_profile, orders, product, order_item.
• 1:1: customer ↔ customer_profile usando PK=FK.
• 1:N: customer → orders con FK en orders.
• N:M: orders ↔ product con tabla puente order_item y atributos de relación.
• Índices: en FKs y columnas de búsqueda (orders.customer_id, orders.created_at).

DDL consolidado (resumen)

CREATE TABLE customer (   customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   email VARCHAR(254) NOT NULL,   full_name VARCHAR(120) NOT NULL,   CONSTRAINT uq_customer_email UNIQUE (email) );  CREATE TABLE customer_profile (   customer_id BIGINT PRIMARY KEY,   birth_date DATE NULL,   phone VARCHAR(30) NULL,   CONSTRAINT fk_profile_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) );  CREATE TABLE product (   product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   sku VARCHAR(40) NOT NULL,   name VARCHAR(120) NOT NULL,   unit_price NUMERIC(12,2) NOT NULL,   CONSTRAINT uq_product_sku UNIQUE (sku) );  CREATE TABLE orders (   order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   customer_id BIGINT NOT NULL,   order_status VARCHAR(20) NOT NULL DEFAULT 'NEW',   created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,   CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id),   CONSTRAINT chk_order_status CHECK (order_status IN ('NEW','PAID','CANCELLED','SHIPPED')) );  CREATE TABLE order_item (   order_id BIGINT NOT NULL,   product_id BIGINT NOT NULL,   quantity INTEGER NOT NULL DEFAULT 1,   unit_price NUMERIC(12,2) NOT NULL,   PRIMARY KEY (order_id, product_id),   CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders(order_id),   CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product(product_id),   CONSTRAINT chk_quantity_positive CHECK (quantity > 0) );  CREATE INDEX idx_orders_customer_id ON orders(customer_id); CREATE INDEX idx_orders_created_at ON orders(created_at);