Modelado de Entidad-Relación

Q: ¿Cómo modelo una relación uno a uno opcional?

Pon una FK UNIQUE anulable en un lado: El UNIQUE en person_id garantiza como máximo un pasaporte por persona.

Q: ¿Cómo documento la cardinalidad para lectores no técnicos?

Usa una leyenda simple de ERD en tu repositorio (1 ----< N) y repítela en los ADR. Mantén la verdad ejecutable en el DDL, no solo en los diagramas.

Q: ¿Pueden dos tablas compartir la misma clave primaria para 1:1?

Sí, común para tablas de extensión:

Q: ¿Cuál es la diferencia entre entidades débiles y fuertes en la práctica?

Una entidad "débil" (líneas de artículo de pedido) no puede existir sin su padre (pedido). Se aplica con FK NOT NULL más ON DELETE CASCADE o RESTRICT según las reglas de negocio.

Q: ¿Cuántas claves foráneas son demasiadas en una tabla?

No hay un límite estricto, pero las tablas anchas con muchas FK anulables a menudo indican una tabla de unión o de máquina de estados faltante. Divide cuando se acumulen más de un puñado de relaciones opcionales.

Q: ¿Deben las tablas de unión tener su propia PK surrogate?

Una PK compuesta en las dos FK es suficiente cuando el par es único. Añade un id surrogate solo si los ORM o las API requieren un identificador de una sola columna para la fila de enlace en sí.

Q: ¿Cómo afectan las opcionalidades al comportamiento de DELETE?

ON DELETE SET NULL se empareja con FK opcionales. Las FK requeridas suelen usar RESTRICT o CASCADE; elige según si las filas secundarias deben sobrevivir a la eliminación del padre.

La cardinalidad, las opcionalidades y las convenciones de nomenclatura convierten los sustantivos del dominio en tablas de PostgreSQL que se mantienen correctas bajo carga.

Receta

Tarjeta de referencia rápida - lista para copiar y pegar.

-- Uno a muchos: FK en la tabla "muchos"
CREATE TABLE posts (
  post_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id  bigint NOT NULL REFERENCES authors (author_id)
);
 
-- Relación opcional: FK anulable
CREATE TABLE posts (
  post_id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  editor_id     bigint REFERENCES editors (editor_id)  -- NULL = no asignado
);
 
-- Muchos a muchos: tabla de unión con dos FK
CREATE TABLE tag_assignments (
  post_id  bigint NOT NULL REFERENCES posts (post_id) ON DELETE CASCADE,
  tag_id   bigint NOT NULL REFERENCES tags (tag_id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Cuándo usar esto:

Diseño de esquemas desde cero antes de la primera migración.
Refactorización de un fan-out join causado por una tabla de unión faltante.
Revisar si una FK anulable o una tabla de enlace separada es la estructura correcta.
Nombrar tablas y columnas para que las rutas JOIN se lean como el dominio.

Ejemplo de Trabajo

BEGIN;
 
CREATE TABLE authors (
  author_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);
 
CREATE TABLE editors (
  editor_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);
 
CREATE TABLE posts (
  post_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id   bigint NOT NULL REFERENCES authors (author_id),
  editor_id   bigint REFERENCES editors (editor_id),
  title       text NOT NULL,
  published   boolean NOT NULL DEFAULT false
);
 
CREATE TABLE tags (
  tag_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label   text NOT NULL UNIQUE
);
 
CREATE TABLE post_tags (
  post_id  bigint NOT NULL REFERENCES posts (post_id) ON DELETE CASCADE,
  tag_id   bigint NOT NULL REFERENCES tags (tag_id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);
 
CREATE INDEX posts_author_id_idx ON posts (author_id);
CREATE INDEX posts_editor_id_idx ON posts (editor_id) WHERE editor_id IS NOT NULL;
CREATE INDEX post_tags_tag_id_idx ON post_tags (tag_id);
 
COMMIT;

Lo que esto demuestra:

Relaciones requeridas vs. opcionales a través de columnas FK NOT NULL vs. anulables.
M:N resuelto con post_tags en lugar de duplicar filas de etiquetas en posts.
Índice parcial en editor_id opcional mantiene el tamaño del índice pequeño cuando la mayoría de las publicaciones no tienen editor.
ON DELETE CASCADE en filas de unión cuando se eliminan publicaciones o etiquetas principales.

Profundización

Cardinalidad de un Vistazo

Relación	Forma en PostgreSQL	Ubicación de la FK
1:1	FK única o PK compartida	Cualquiera de las tablas; se requiere restricción única
1:N	FK en el hijo	Lado "muchos"
M:N	Tabla de unión	Ambas FK en la tabla de enlace
Opcional	FK anulable	El hijo permite NULL
Requerido	FK `NOT NULL`	El hijo siempre tiene un padre

Convenciones de Nomenclatura

Tablas: sustantivos en plural (orders, line_items) o singular si tu organización se estandariza en singular; elige uno y aplícale linting.
Columnas PK: <entidad>_id (order_id) con bigint GENERATED ALWAYS AS IDENTITY.
Columnas FK: coinciden con el nombre de la PK referenciada (customer_id referencia customers.customer_id).
Tablas de unión: <izquierda>_<derecha> o <izquierda>_<derecha>_map (order_items, user_roles).

Reglas de Opcionalidad

-- Cada pedido debe tener un cliente (requerido)
customer_id bigint NOT NULL REFERENCES customers (customer_id)
 
-- El envío puede no existir todavía (opcional)
shipment_id bigint REFERENCES shipments (shipment_id)

Relaciones requeridas: NOT NULL + FK.
Relaciones opcionales: FK anulable; usa índices parciales para búsquedas selectivas.
No modeles la opcionalidad solo en el código de la aplicación; la base de datos debe rechazar estados imposibles.

Notas SQL

-- Detectar filas huérfanas si las FK se añadieron tarde (debería devolver 0)
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
 
-- 1:1 forzado con UNIQUE en la columna FK
CREATE TABLE user_profiles (
  user_id    bigint PRIMARY KEY REFERENCES users (user_id),
  bio        text
);

Errores Comunes

M:N modelado como columnas duplicadas - almacenar tag1, tag2, tag3 en posts rompe la normalización y bloquea la indexación. Solución: tabla de unión con (post_id, tag_id).
FK en el lado equivocado - poner order_id en customers para una lista de pedidos 1:N. Solución: FK en orders.customer_id.
FK anulable tratada como requerida en consultas - los INNER JOIN descartan filas con editores no asignados. Solución: LEFT JOIN cuando la relación es opcional.
Índice faltante en columnas FK - los nested loop joins se degradan en tablas grandes. Solución: CREATE INDEX ON child (parent_id).
Unión simétrica sin unicidad - pares duplicados de (post_id, tag_id). Solución: PRIMARY KEY compuesto o restricción UNIQUE.

Alternativas

Alternativa	Usar Cuando	No Usar Cuando
`ENUM` de PostgreSQL para estado	Conjuntos de valores pequeños y estables	Los valores cambian semanalmente (dolor de migración)
`jsonb` para atributos anidados	El esquema varía por fila, lectura intensiva	Necesitas integridad de FK en IDs anidados
Claves naturales compuestas	Existen identificadores de negocio fuertes globalmente	Los IDs son compuestos entre inquilinos o regiones
Herencia de tabla única	Pocos subtipos, columnas compartidas	Los subtipos divergen con muchas columnas anulables

Preguntas Frecuentes

¿Deben las claves primarias ser siempre columnas de identidad surrogate bigint?

Las claves surrogate son el valor predeterminado para OLTP porque son estrechas, estables y amigables para la indexación. Usa claves naturales (email, SKU) como restricciones UNIQUE cuando el negocio garantice la unicidad global.

¿Cómo modelo una relación uno a uno opcional?

Pon una FK UNIQUE anulable en un lado:

CREATE TABLE passports (
  passport_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  person_id    bigint NOT NULL UNIQUE REFERENCES persons (person_id),
  number       text NOT NULL
);

El UNIQUE en person_id garantiza como máximo un pasaporte por persona.

¿Cuándo es una tabla de unión excesiva?

Cuando la asociación no tiene atributos y cada lado aparece como máximo una vez: un 1:1 puede usar una sola FK. Si el enlace lleva metadatos (assigned_at, role), usa una tabla de unión o enlace incluso para baja cardinalidad.

¿Debo nombrar explícitamente las restricciones FK?

Sí, para la operabilidad:

CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)

Los nombres explícitos hacen que las diferencias de migración y los mensajes de error sean legibles en los registros de producción.

¿Cómo documento la cardinalidad para lectores no técnicos?

Usa una leyenda simple de ERD en tu repositorio (1 --< N, N >--< N) y repítela en los ADR. Mantén la verdad ejecutable en el DDL, no solo en los diagramas.

¿Pueden dos tablas compartir la misma clave primaria para 1:1?

Sí, común para tablas de extensión:

CREATE TABLE users (user_id bigint PRIMARY KEY, email text NOT NULL);
CREATE TABLE user_settings (
  user_id bigint PRIMARY KEY REFERENCES users (user_id),
  theme   text NOT NULL DEFAULT 'light'
);

¿Cuál es la diferencia entre entidades débiles y fuertes en la práctica?

Una entidad "débil" (líneas de artículo de pedido) no puede existir sin su padre (pedido). Se aplica con FK NOT NULL más ON DELETE CASCADE o RESTRICT según las reglas de negocio.

¿Cuántas claves foráneas son demasiadas en una tabla?

No hay un límite estricto, pero las tablas anchas con muchas FK anulables a menudo indican una tabla de unión o de máquina de estados faltante. Divide cuando se acumulen más de un puñado de relaciones opcionales.

¿Deben las tablas de unión tener su propia PK surrogate?

Una PK compuesta en las dos FK es suficiente cuando el par es único. Añade un id surrogate solo si los ORM o las API requieren un identificador de una sola columna para la fila de enlace en sí.

¿Cómo afectan las opcionalidades al comportamiento de DELETE?

ON DELETE SET NULL se empareja con FK opcionales. Las FK requeridas suelen usar RESTRICT o CASCADE; elige según si las filas secundarias deben sobrevivir a la eliminación del padre.

Relacionados

Conceptos Básicos de Modelado de Datos - flujo de conceptual a físico
Límites del Esquema Orientado al Dominio - agrupación de entidades por contexto
Conceptos Básicos de Normalización - eliminación de anomalías de actualización
Claves Foráneas Faltantes - fallos de integridad sin FK
Explosión de Fan-Out Join - errores de cardinalidad en consultas

Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, y PostGIS 3.5+.

Modelado de Entidad-Relación

La cardinalidad, las opcionalidades y las convenciones de nomenclatura convierten los sustantivos del dominio en tablas de PostgreSQL que se mantienen correctas bajo carga.

Receta

Tarjeta de referencia rápida - lista para copiar y pegar.

-- Uno a muchos: FK en la tabla "muchos"
CREATE TABLE posts (
  post_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id  bigint NOT NULL REFERENCES authors (author_id)
);
 
-- Relación opcional: FK anulable
CREATE TABLE posts (
  post_id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  editor_id     bigint REFERENCES editors (editor_id)  -- NULL = no asignado
);
 
-- Muchos a muchos: tabla de unión con dos FK
CREATE TABLE tag_assignments (
  post_id  bigint NOT NULL REFERENCES posts (post_id) ON DELETE CASCADE,
  tag_id   bigint NOT NULL REFERENCES tags (tag_id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Cuándo usar esto:

Diseño de esquemas desde cero antes de la primera migración.
Refactorización de un fan-out join causado por una tabla de unión faltante.
Revisar si una FK anulable o una tabla de enlace separada es la estructura correcta.
Nombrar tablas y columnas para que las rutas JOIN se lean como el dominio.

Ejemplo de Trabajo

BEGIN;
 
CREATE TABLE authors (
  author_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);
 
CREATE TABLE editors (
  editor_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);
 
CREATE TABLE posts (
  post_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id   bigint NOT NULL REFERENCES authors (author_id),
  editor_id   bigint REFERENCES editors (editor_id),
  title       text NOT NULL,
  published   boolean NOT NULL DEFAULT false
);
 
CREATE TABLE tags (
  tag_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label   text NOT NULL UNIQUE
);
 
CREATE TABLE post_tags (
  post_id  bigint NOT NULL REFERENCES posts (post_id) ON DELETE CASCADE,
  tag_id   bigint NOT NULL REFERENCES tags (tag_id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);
 
CREATE INDEX posts_author_id_idx ON posts (author_id);
CREATE INDEX posts_editor_id_idx ON posts (editor_id) WHERE editor_id IS NOT NULL;
CREATE INDEX post_tags_tag_id_idx ON post_tags (tag_id);
 
COMMIT;

Lo que esto demuestra:

Relaciones requeridas vs. opcionales a través de columnas FK NOT NULL vs. anulables.
M:N resuelto con post_tags en lugar de duplicar filas de etiquetas en posts.
Índice parcial en editor_id opcional mantiene el tamaño del índice pequeño cuando la mayoría de las publicaciones no tienen editor.
ON DELETE CASCADE en filas de unión cuando se eliminan publicaciones o etiquetas principales.

Profundización

Cardinalidad de un Vistazo

Relación	Forma en PostgreSQL	Ubicación de la FK
1:1	FK única o PK compartida	Cualquiera de las tablas; se requiere restricción única
1:N	FK en el hijo	Lado "muchos"
M:N	Tabla de unión	Ambas FK en la tabla de enlace
Opcional	FK anulable	El hijo permite NULL
Requerido	FK `NOT NULL`	El hijo siempre tiene un padre

Convenciones de Nomenclatura

Tablas: sustantivos en plural (orders, line_items) o singular si tu organización se estandariza en singular; elige uno y aplícale linting.
Columnas PK: <entidad>_id (order_id) con bigint GENERATED ALWAYS AS IDENTITY.
Columnas FK: coinciden con el nombre de la PK referenciada (customer_id referencia customers.customer_id).
Tablas de unión: <izquierda>_<derecha> o <izquierda>_<derecha>_map (order_items, user_roles).

Reglas de Opcionalidad

-- Cada pedido debe tener un cliente (requerido)
customer_id bigint NOT NULL REFERENCES customers (customer_id)
 
-- El envío puede no existir todavía (opcional)
shipment_id bigint REFERENCES shipments (shipment_id)

Relaciones requeridas: NOT NULL + FK.
Relaciones opcionales: FK anulable; usa índices parciales para búsquedas selectivas.
No modeles la opcionalidad solo en el código de la aplicación; la base de datos debe rechazar estados imposibles.

Notas SQL

-- Detectar filas huérfanas si las FK se añadieron tarde (debería devolver 0)
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
 
-- 1:1 forzado con UNIQUE en la columna FK
CREATE TABLE user_profiles (
  user_id    bigint PRIMARY KEY REFERENCES users (user_id),
  bio        text
);

Errores Comunes

M:N modelado como columnas duplicadas - almacenar tag1, tag2, tag3 en posts rompe la normalización y bloquea la indexación. Solución: tabla de unión con (post_id, tag_id).
FK en el lado equivocado - poner order_id en customers para una lista de pedidos 1:N. Solución: FK en orders.customer_id.
FK anulable tratada como requerida en consultas - los INNER JOIN descartan filas con editores no asignados. Solución: LEFT JOIN cuando la relación es opcional.
Índice faltante en columnas FK - los nested loop joins se degradan en tablas grandes. Solución: CREATE INDEX ON child (parent_id).
Unión simétrica sin unicidad - pares duplicados de (post_id, tag_id). Solución: PRIMARY KEY compuesto o restricción UNIQUE.

Alternativas

Alternativa	Usar Cuando	No Usar Cuando
`ENUM` de PostgreSQL para estado	Conjuntos de valores pequeños y estables	Los valores cambian semanalmente (dolor de migración)
`jsonb` para atributos anidados	El esquema varía por fila, lectura intensiva	Necesitas integridad de FK en IDs anidados
Claves naturales compuestas	Existen identificadores de negocio fuertes globalmente	Los IDs son compuestos entre inquilinos o regiones
Herencia de tabla única	Pocos subtipos, columnas compartidas	Los subtipos divergen con muchas columnas anulables

Preguntas Frecuentes

¿Deben las claves primarias ser siempre columnas de identidad surrogate bigint?

¿Cómo modelo una relación uno a uno opcional?

Pon una FK UNIQUE anulable en un lado:

CREATE TABLE passports (
  passport_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  person_id    bigint NOT NULL UNIQUE REFERENCES persons (person_id),
  number       text NOT NULL
);

El UNIQUE en person_id garantiza como máximo un pasaporte por persona.

¿Cuándo es una tabla de unión excesiva?

¿Debo nombrar explícitamente las restricciones FK?

Sí, para la operabilidad:

CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)

Los nombres explícitos hacen que las diferencias de migración y los mensajes de error sean legibles en los registros de producción.

¿Cómo documento la cardinalidad para lectores no técnicos?

Usa una leyenda simple de ERD en tu repositorio (1 --< N, N >--< N) y repítela en los ADR. Mantén la verdad ejecutable en el DDL, no solo en los diagramas.

¿Pueden dos tablas compartir la misma clave primaria para 1:1?

Sí, común para tablas de extensión:

CREATE TABLE users (user_id bigint PRIMARY KEY, email text NOT NULL);
CREATE TABLE user_settings (
  user_id bigint PRIMARY KEY REFERENCES users (user_id),
  theme   text NOT NULL DEFAULT 'light'
);

¿Cuál es la diferencia entre entidades débiles y fuertes en la práctica?

Una entidad "débil" (líneas de artículo de pedido) no puede existir sin su padre (pedido). Se aplica con FK NOT NULL más ON DELETE CASCADE o RESTRICT según las reglas de negocio.

¿Cuántas claves foráneas son demasiadas en una tabla?

¿Deben las tablas de unión tener su propia PK surrogate?

Una PK compuesta en las dos FK es suficiente cuando el par es único. Añade un id surrogate solo si los ORM o las API requieren un identificador de una sola columna para la fila de enlace en sí.

¿Cómo afectan las opcionalidades al comportamiento de DELETE?

ON DELETE SET NULL se empareja con FK opcionales. Las FK requeridas suelen usar RESTRICT o CASCADE; elige según si las filas secundarias deben sobrevivir a la eliminación del padre.

Relacionados

Conceptos Básicos de Modelado de Datos - flujo de conceptual a físico
Límites del Esquema Orientado al Dominio - agrupación de entidades por contexto
Conceptos Básicos de Normalización - eliminación de anomalías de actualización
Claves Foráneas Faltantes - fallos de integridad sin FK
Explosión de Fan-Out Join - errores de cardinalidad en consultas

Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, y PostGIS 3.5+.

Modelado de Entidad-Relación

Buscar en la documentación

Modelado de Entidad-Relación