Foreign Keys para enlazar los esquemas de Marketplace

Tienes una base de datos, tienes tablas. Incluso ya rellenaste esas tablas con datos. Ahora ha llegado el momento de preocuparte por la integridad de esos datos.

Al crear tablas dentro de cada esquema, se añadieron automáticamente foreign keys a las tablas de ese mismo esquema. Ahora necesitas enlazar tablas de diferentes esquemas entre sí - añadir foreign keys.

Por ejemplo, la tabla order del esquema order hace referencia a un usuario de la tabla account del esquema user. Tienes que añadir un foreign key para esa tabla. Una consulta así podría verse más o menos así:

ALTER TABLE "order".order
  ADD CONSTRAINT fk_order_user
  FOREIGN KEY (user_id)
  REFERENCES "user".account(id)
  ON DELETE RESTRICT;

Lista de foreign keys

En total tendrás que añadir 40 foreign keys :)

Esquema product:

[product].review.user_id → [user].account.id

Las reseñas de productos deben estar enlazadas con los usuarios que las dejaron.

[product].question.user_id → [user].account.id

Las preguntas sobre productos las hacen usuarios registrados.

[product].answer.user_id → [user].account.id

Las respuestas también pertenecen a usuarios.

Esquema order:

[order].order.user_id → [user].account.id

El usuario que hace el pedido — siempre es un usuario registrado.

[order].order_item.product_id → [product].product.id

Un ítem del pedido siempre hace referencia a un producto concreto.

[order].order_item.variant_id → [product].variant.id

Si se elige una variante del producto — debe existir. Pero la variante puede ser eliminada (entonces el campo será NULL).

[order].cart.user_id → [user].account.id

El carrito pertenece a un usuario concreto.

[order].cart_item.product_id → [product].product.id

El producto en el carrito debe ser un producto válido.

[order].cart_item.variant_id → [product].variant.id

La variante elegida del producto debe existir si está indicada. Si se elimina — el campo se vuelve NULL.

Esquema logistics:

[logistics].inventory.variant_id → [product].variant.id

La variante puede ser eliminada (por ejemplo, retirada de la venta).

[logistics].inventory_movement.product_id → [product].product.id

Cualquier movimiento en el almacén se refiere a un producto real.

[logistics].inventory_movement.variant_id → [product].variant.id

El tipo de movimiento puede ser sobre una variante concreta del producto.

[logistics].stock_level.product_id → [product].product.id

El nivel de stock se establece sobre productos existentes.

[logistics].transfer.product_id → [product].product.id

Las transferencias solo sobre productos realmente existentes.

[logistics].transfer.variant_id → [product].variant.id

La variante puede ser eliminada.

[logistics].package.order_id → [order].order.id

Cada paquete está enlazado a un pedido concreto.

Esquema payment:

[payment].payment_transaction.order_id → [order].order.id

La transacción de pago siempre está relacionada con un pedido.

[payment].invoice.order_id → [order].order.id La factura se emite para un pedido.

[payment].billing_address.user_id → [user].account.id

La dirección de facturación pertenece a un usuario.

[payment].wallet.user_id → [user].account.id

La wallet es única para cada usuario.

Esquema marketing:

[marketing].discount.product_id → [product].product.id

El descuento puede estar vinculado a un producto concreto.

[marketing].discount.category_id → [product].category.id

El descuento puede estar vinculado a una categoría de productos.

[marketing].discount.brand_id → [product].brand.id

Descuento para la marca.

[marketing].promo_usage.user_id → [user].account.id

Los códigos promocionales solo los usan usuarios registrados.

[marketing].featured_product.product_id → [product].product.id

El producto destacado en el escaparate debe existir.

[marketing].referral_use.referrer_id → [user].account.id

Quién invitó — usuario obligatorio.

[marketing].referral_use.referee_id → [user].account.id

A quién invitaron — usuario obligatorio.

Esquema support:

[support].support_ticket.user_id → [user].account.id

Cada ticket lo crea un usuario.

[support].support_ticket.category_id → [support].ticket_category.id

La categoría del ticket puede ser eliminada, el ticket se queda.

[support].ticket_message.sender_id → [user].account.id

Los mensajes los envían usuarios (o agentes, si también están en user.account).

[support].support_agent.user_id → [user].account.id

Un agente de soporte — siempre es un usuario.

[support].ticket_status_log.changed_by → [user].account.id

Registramos quién cambió el estado (usuario o empleado).

Esquema content:

[content].article.author_id → [user].account.id

El artículo lo escribe un usuario registrado.

[content].media.uploaded_by → [user].account.id

Quién subió el archivo multimedia (puede ser eliminado).

[content].content_revision.author_id → [user].account.id

Quién hizo el cambio (puede ser eliminado).

Nota

Donde sea posible, deberías usar ON DELETE SET NULL, para que al eliminar una entidad no se pierda toda la información sobre los enlaces.

Para enlazar entidades usando el tipo "target_type/target_id" (por ejemplo, user.comment), no es posible un foreign key a nivel de SQL, esos enlaces deben controlarse a nivel de aplicación.

Esta lista cubre los principales enlaces entre esquemas para la integridad básica de los datos a nivel SQL.

Archivo con la solución.