Comment mettre en œuvre une relation "many-to-many" dans PostgreSQL?

les énoncés SQL DDL (data definition language) pourraient ressembler à ceci:

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
);

j'ai fait quelques ajustements:

• la relation n:m est normalement mise en œuvre par un tableau séparé - bill_product dans ce cas.

• j'ai ajouté serial colonnes en tant que . Je recommande fortement cela, parce que le nom d'un produit est à peine unique. En outre, l'application de l'unicité et la référence de la colonne dans les clés étrangères est beaucoup moins cher avec un 4-octet integer qu'avec une chaîne stockée comme text ou varchar .
  
  Dans Postgres 10 ou plus tard envisager une IDENTITY colonne à la place. Details:

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

• N'utilisez pas de noms de types de données de base comme date comme identificateurs . Bien que cela soit possible, c'est un mauvais style et conduit à des erreurs confuses et des messages d'erreur. Utiliser juridique, bas de casse, les identificateurs non cotées . N'utilisez jamais mots réservés et évitez les doubles citations cas mixte identifiants si vous le pouvez.

• name n'est pas un bon nom. J'ai renommé la colonne name de la table product pour être product . C'est une meilleure Convention d'appellation . Autrement, lorsque vous rejoignez quelques tables dans une requête - ce que vous faites beaucoup dans une base de données relationnelle - vous vous retrouvez avec plusieurs colonnes nommées name et devez utiliser des alias de colonne pour trier le désordre. Ce n'est pas utile. Une autre anti-motif répandu serait simplement id comme nom de colonne.
  
  Je ne suis pas sûr du nom d'un bill . Peut-être bill_id peut être le nom dans ce cas.

• price est de type de données numeric pour stocker des nombres fractionnaires c'est justement qu'il est entré (arbitraire type de précision au lieu du type à virgule flottante). Si vous traitez avec des nombres entiers exclusivement, faites que integer . Par exemple, vous pouvez économiser prix en Cents .

• le amount ( "Products" dans votre question) va dans le tableau de liaison bill_product et est de type numeric ainsi. Encore une fois, integer si vous traitez avec des nombres entiers exclusivement.

• vous voyez le clés étrangères dans bill_product ? J'ai créé les deux pour les changements en cascade ( ON UPDATE CASCADE ): si un product_id ou bill_id doit changer, le changement est en cascade à toutes les entrées en fonction dans bill_product et rien ne casse.
  
  J'ai aussi utilisé ON DELETE CASCADE pour bill_id : si vous supprimez une facture, les détails sont supprimés avec elle.
  
  Pas tellement pour les produits: Vous ne voulez pas supprimer un produit utilisé dans un projet de loi. Postgres lancera une erreur si vous tentez cela. Vous ajouteriez une autre colonne à product pour marquer les lignes obsolètes à la place.

• toutes les colonnes de cet exemple de base finissent par être NOT NULL , ainsi, les valeurs NULL ne sont pas autorisées. (Oui, toutes les colonnes-les colonnes utilisées dans une clé primaire sont définies UNIQUE NOT NULL automatiquement.) C'est parce que NULL valeurs ça n'aurait de sens dans aucune des colonnes. Il fait un débutant la vie plus facile. Mais vous ne vous en sortirez pas si facilement, vous devez comprendre NULL manipulation de toute façon. Des colonnes supplémentaires pourraient permettre des valeurs NULL , des fonctions et des jointures peuvent introduire des valeurs NULL dans les requêtes, etc.

• lire le chapitre sur CREATE TABLE dans le manuel .

• les clés primaires sont implémentées avec un index unique sur les colonnes clés, qui rend les requêtes avec des conditions sur la(Les) colonne (s) PK rapide (s). Cependant, la séquence des colonnes de clés est pertinente dans les clés à plusieurs colonnes. Puisque le PK sur bill_product est sur (bill_id, product_id) dans mon exemple, vous pouvez vouloir ajouter un autre index sur juste product_id ou (product_id, bill_id) si vous avez des requêtes recherchant donné un product_id et non bill_id . Details:

  • PostgreSQL clé primaire composite
  • est-ce qu'un index composite est également bon pour les requêtes sur le premier champ?
  • travail des indices en PostgreSQL

• lire le chapitre sur les index dans le manuel .