sql - una - solucion relacion muchos a muchos

¿Cómo implementar una relación de muchos a muchos en PostgreSQL?

Question

Creo que el título es autoexplicativo. ¿Cómo se crea la estructura de la tabla en PostgreSQL para hacer una relación de muchos a muchos?

Mi ejemplo:

Product(name, price);
Bill(name, date, Products);

Answer 1

Las sentencias SQL DDL (lenguaje de definición de datos) podrían verse así:

CREATE TABLE product (
  product_id serial PRIMARY KEY  -- implicit primary key constraint
, product    text NOT NULL
, price      numeric NOT NULL DEFAULT 0
);

CREATE TABLE bill (
  bill_id  serial PRIMARY KEY
, bill     text NOT NULL
, billdate date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE bill_product (
  bill_id    int REFERENCES bill (bill_id) ON UPDATE CASCADE ON DELETE CASCADE
, product_id int REFERENCES product (product_id) ON UPDATE CASCADE
, amount     numeric NOT NULL DEFAULT 1
, CONSTRAINT bill_product_pkey PRIMARY KEY (bill_id, product_id)  -- explicit pk
);

Hice algunos ajustes:

• La relación n: m normalmente se implementa mediante una tabla separada - bill_product en este caso.

• serial columnas en serial como claves primarias sustitutas . Lo recomiendo mucho, porque el nombre de un producto no es único. Además, aplicar la exclusividad y hacer referencia a la columna en claves externas es mucho más barato con un integer de 4 bytes que con una cadena almacenada como text o varchar .
  En Postgres 10 o posterior, considere una columna de IDENTITY . Detalles:

  • https://blog.2ndquadrant.com/postgresql-10-identity-columns/

• No use nombres de tipos de datos básicos como la date como identificadores . Si bien esto es posible, es de mal estilo y conduce a errores confusos y mensajes de error. Use identificadores legales, minúsculas, sin comillas . Nunca use palabras reservadas y evite los identificadores de caso mixtos de doble cita si puede.

• name no es un buen nombre Cambié el name columna del product de la tabla para ser product . Esa es una mejor convención de nombres . De lo contrario, cuando te unes a un par de tablas en una consulta, lo cual haces mucho en una base de datos relacional, terminas con varias columnas llamadas name y tienes que usar alias de columna para resolver el problema. Eso no es útil. Otro anti-patrón generalizado sería solo id como nombre de columna.
  No estoy seguro de cuál sería el nombre de una bill . Tal vez bill_id puede ser el nombre en este caso.

• price es del tipo de datos numeric para almacenar los números fraccionarios con precisión tal como se ingresaron (tipo de precisión arbitraria en lugar de tipo de punto flotante). Si manejas números enteros exclusivamente, haz ese integer . Por ejemplo, puede guardar precios como centavos .

• La amount ( "Products" en su pregunta) va a la tabla de vinculación bill_product y también es de tipo numeric . Nuevamente, integer si maneja números enteros exclusivamente.

• ¿Ves las claves foráneas en bill_product ? bill_id ambos para realizar cambios en cascada ( ON UPDATE CASCADE ): si un product_id o bill_id debe cambiar, el cambio se bill_product en cascada a todas las entradas dependientes en bill_product y nada se rompe.
  También utilicé ON DELETE CASCADE para bill_id : si borras una factura, los detalles se eliminan con ella.
  No ocurre lo mismo con los productos: no desea eliminar un producto que se usa en una factura. Postgres lanzará un error si intentas esto. Debería agregar otra columna al product para marcar filas obsoletas.

• Todas las columnas de este ejemplo básico terminan siendo NOT NULL , por lo que los valores NULL no están permitidos. (Sí, todas las columnas: las columnas utilizadas en una clave principal se definen UNIQUE NOT NULL automáticamente). Esto se debe a que los valores NULL no tienen sentido en ninguna de las columnas. Hace la vida de un principiante más fácil. Pero no se saldrá tan fácilmente, necesita entender el manejo NULL todos modos. Las columnas adicionales pueden permitir valores NULL , las funciones y las uniones pueden introducir valores NULL en las consultas, etc.

• Lea el capítulo sobre CREATE TABLE en el manual .

• Las claves primarias se implementan con un índice único en las columnas clave, lo que hace que las consultas con condiciones en la (s) columna (s) PK sean rápidas. Sin embargo, la secuencia de columnas clave es relevante en claves de varias columnas. Dado que el PK en bill_product está bill_product (bill_id, product_id) en mi ejemplo, es posible que desee agregar otro índice solo en product_id o (product_id, bill_id) si tiene consultas en busca de un product_id y no bill_id . Detalles:

  • Clave principal compuesta de PostgreSQL
  • ¿Un índice compuesto también es bueno para consultas en el primer campo?
  • Trabajo de índices en PostgreSQL

• Lea el capítulo sobre índices en el manual .