PostgreSQL
Relaciones y cardinalidad
Modelado de relaciones uno a uno, uno a muchos y muchos a muchos mediante claves foráneas, tablas asociativas y restricciones de cardinalidad.
- Última actualización
- Actualizada
- Nivel
- Fundamentos
PostgreSQL
Modelado de relaciones uno a uno, uno a muchos y muchos a muchos mediante claves foráneas, tablas asociativas y restricciones de cardinalidad.
Una relación entre entidades solo queda protegida cuando cardinalidad, opcionalidad y ownership se expresan con claves, NOT NULL, UNIQUE, foreign keys y reglas de eliminación. Dibujarla en un ERD no obliga a PostgreSQL a respetarla.
Las relaciones responden preguntas como:
¿Puede una fila existir sin la otra?
¿Cuántas filas pueden relacionarse?
¿Quién posee el lifecycle?
¿Qué ocurre al eliminar o cambiar la referencia?La respuesta determina dónde va la foreign key, si debe aceptar NULL, si necesita UNIQUE y qué acción referencial tiene sentido.
Un diseño sin cardinalidad protegida permite:
Las relaciones no son solo navegación; son reglas sobre estados posibles.
Requisito:
Un business administra muchas branches; cada branch pertenece a un business.
CREATE TABLE businesses (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE branches (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
business_id bigint NOT NULL,
name text NOT NULL,
CONSTRAINT branches_business_fk
FOREIGN KEY (business_id)
REFERENCES businesses(id)
ON DELETE RESTRICT
);La FK vive en el lado “muchos”. NOT NULL hace obligatoria la relación.
ON DELETE RESTRICT expresa que un business con branches no puede borrarse directamente. Si el lifecycle indicara que las branches no tienen sentido independiente, CASCADE podría ser válido, pero su impacto debe medirse.
Requisito:
Cada business tiene como máximo una configuración.
CREATE TABLE business_settings (
business_id bigint PRIMARY KEY,
currency_code char(3) NOT NULL,
time_zone text NOT NULL,
CONSTRAINT business_settings_business_fk
FOREIGN KEY (business_id)
REFERENCES businesses(id)
ON DELETE CASCADE
);La columna es simultáneamente PK y FK. Esto impide dos settings para el mismo business.
Otra variante:
id bigint PRIMARY KEY,
business_id bigint NOT NULL UNIQUE REFERENCES businesses(id)Añade identidad propia. Solo tiene sentido si settings necesita ser referenciada como entidad independiente.
Una relación opcional uno-a-uno:
profile_id bigint UNIQUE REFERENCES profiles(id)Sin NOT NULL, la referencia puede faltar. Debido a la semántica de NULL, el unique permite varias filas sin profile; eso suele ser correcto.
Requisito:
Un user puede pertenecer a muchas branches y una branch tiene muchos users.
CREATE TABLE user_branches (
user_id bigint NOT NULL REFERENCES users(id),
branch_id bigint NOT NULL REFERENCES branches(id),
role text NOT NULL,
assigned_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (user_id, branch_id)
);La tabla intermedia no es ruido. Representa la relación y puede contener atributos propios.
Flujo:
role pertenece a la membership, no necesariamente al user global.Una relación se convierte en entidad asociativa cuando tiene identidad, estado o comportamiento propio:
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders(id),
product_id bigint NOT NULL REFERENCES products(id),
quantity numeric(12,3) NOT NULL CHECK (quantity > 0),
unit_price numeric(14,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);order_items no solo conecta order y product; conserva cantidad y precio histórico.
La nullability debe corresponder al dominio.
Obligatoria:
order.branch_id NOT NULLOpcional:
order.delivery_staff_id NULLPero NULL debe tener semántica clara: “todavía no asignado”, no “dato perdido”. Si existen varios estados de asignación, puede necesitar una entidad o estado explícito.
Úsalo cuando el hijo forma parte del agregado y no tiene valor independiente.
Ejemplo razonable:
order → order_itemsEjemplo peligroso:
customer → paymentsLos pagos pueden necesitar conservarse por auditoría aunque el customer se anonimize.
Protege historial y obliga a resolver dependencias explícitamente.
Conserva el hijo sin referencia. Solo es válido si “sin parent” es un estado coherente.
CREATE TABLE categories (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
parent_id bigint REFERENCES categories(id),
name text NOT NULL,
CHECK (parent_id IS DISTINCT FROM id)
);Esto evita auto-referencia directa, pero no ciclos largos:
A → B → C → ADetectar ciclos puede requerir:
Consultar jerarquía:
WITH RECURSIVE tree AS (
SELECT id, parent_id, name, 0 AS depth
FROM categories
WHERE id = $1
UNION ALL
SELECT c.id, c.parent_id, c.name, t.depth + 1
FROM categories c
JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;Necesita control de profundidad/ciclos en datos no confiables.
parent_id. Simple de escribir; consultas profundas usan recursion.
Guarda ruta. Lecturas de subtree pueden ser rápidas; mover nodos reescribe descendientes.
Lecturas jerárquicas eficientes, escrituras complejas.
Guarda todos los pares ancestor/descendant. Lecturas flexibles, más almacenamiento y mantenimiento.
No elijas un modelo avanzado si la jerarquía es pequeña y las escrituras simples.
Patrón:
commentable_type text,
commentable_id bigintProblema: una FK convencional no puede apuntar a orders o products según un string. Se pierde integridad.
Alternativas:
order_comments, product_comments.commentable_entities referenciada por todas.La flexibilidad de type+id se paga con referencias inválidas y joins difíciles.
Requisito:
Una order solo puede referenciar una branch del mismo tenant.
CREATE TABLE branches (
tenant_id bigint NOT NULL,
id bigint NOT NULL,
PRIMARY KEY (tenant_id, id)
);
CREATE TABLE orders (
tenant_id bigint NOT NULL,
id bigint NOT NULL,
branch_id bigint NOT NULL,
PRIMARY KEY (tenant_id, id),
FOREIGN KEY (tenant_id, branch_id)
REFERENCES branches(tenant_id, id)
);La FK compuesta hace imposible una referencia cross-tenant en esa relación.
A veces la relación cambia en el tiempo:
CREATE TABLE employee_branch_assignments (
employee_id bigint NOT NULL,
branch_id bigint NOT NULL,
valid_during tstzrange NOT NULL,
EXCLUDE USING gist (
employee_id WITH =,
valid_during WITH &&
)
);Esto impide asignaciones solapadas para un employee si el dominio exige una branch a la vez.
Una FK al producto actual no conserva todo el estado histórico. Un item puede necesitar snapshot:
product_name text NOT NULL,
unit_price numeric(14,2) NOT NULLNo es una duplicación accidental si representa el hecho al momento de la orden.
La FK conserva identidad; el snapshot conserva historia.
Una fila con deleted_at sigue existiendo para FKs.
Consecuencias:
Soft delete es un lifecycle adicional, no un borrado gratis.
Indexa columnas FK cuando:
Ejemplo:
CREATE INDEX orders_branch_id_idx ON orders(branch_id);Para tenant:
CREATE INDEX orders_tenant_branch_idx
ON orders(tenant_id, branch_id);El orden de columnas debe alinearse con queries.
Requisito DomiSys:
CREATE TABLE inventory (
branch_id bigint NOT NULL REFERENCES branches(id),
product_id bigint NOT NULL REFERENCES products(id),
quantity numeric(14,3) NOT NULL CHECK (quantity >= 0),
PRIMARY KEY (branch_id, product_id)
);La PK compuesta:
Un update de FK puede cambiar ownership. Debe autorizarse y quizá conservar historial.
CASCADE puede mantener una transacción enorme, locks y WAL. Puede requerir batch archival.
Dos tablas con FKs obligatorias mutuas complican inserción. Evalúa deferrable, nullable temporal o rediseño.
Una FK a surrogate ID no impide dos parents semánticamente iguales. Protege natural uniqueness.
Un customer con 3 orders y cada order con 4 items produce 12 filas. Comprende cardinalidad antes de agregar.
Pierde FK por elemento y atributos de relación.
Lo convierte en uno-a-muchos.
Puede eliminar historial no recuperable.
Pierde integridad y dificulta consultas.
Puede permitir referencias cruzadas.
Permite relaciones duplicadas.
(type, id) polimórfico?Normalización y desnormalización analiza cuándo separar hechos, cuándo conservar snapshots y cómo introducir redundancia sin perder consistencia.