Comment implémenter une relation many-to-many dans PostgreSQL ?

Question

Je crois que le titre est explicite. Comment créer la structure de la table dans PostgreSQL pour établir une relation many-to-many.

Mon exemple :

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

Answer 1

Les instructions SQL DDL (langage de définition des données) 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 :

• En relation n:m est normalement mis en œuvre par une table séparée - bill_product dans ce cas.

• J'ai ajouté serial colonnes comme clés primaires de substitution . Dans Postgres 10 ou plus, considérez qu'un IDENTITY colonne à la place. Voir :

  • Renommer en toute sécurité des tables en utilisant des colonnes de clé primaire en série
  • Incrémentation automatique des colonnes du tableau
  • https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/

  Je le recommande vivement, car le nom d'un produit est difficilement unique (ce n'est pas une bonne "clé naturelle"). De plus, l'application de l'unicité et la référence à la colonne dans les clés étrangères sont généralement moins coûteuses avec une clé de 4 octets. integer (ou même un fichier de 8 octets bigint ) qu'avec une chaîne de caractères stockée en tant que text o varchar .

• N'utilisez pas les noms des types de données de base comme date como identifiants . Bien que cela soit possible, il s'agit d'un mauvais style qui entraîne des erreurs et des messages d'erreur déroutants. Utilisez identifiants légaux, en minuscules, non cités . Ne jamais utiliser mots réservés et évitez, si possible, les identifiants à double guillemet et à casse mixte.

• "nom" n'est pas un bon nom. J'ai renommé la colonne de la table product à être product (ou product_name ou similaire). C'est une meilleure convention d'appellation . Sinon, lorsque vous joignez deux 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 "nom" et devez utiliser des alias de colonne pour faire le tri. Ce n'est pas très utile. Un autre anti-modèle très répandu serait d'utiliser simplement "id" comme nom de colonne.
  Je ne suis pas sûr du nom d'un bill serait. bill_id suffira probablement dans ce cas.

• price est de type de données numeric pour stocker des nombres fractionnaires précisément comme indiqué (type de précision arbitraire au lieu du type à virgule flottante). Si vous traitez exclusivement avec des nombres entiers, faites en sorte que integer . Par exemple, vous pouvez enregistrer prix en centimes .

• En amount ( "Products" dans votre question) va dans la table de liaison bill_product et est de type numeric également. Encore une fois, integer si vous traitez exclusivement des nombres entiers.

• Vous voyez le clés étrangères en bill_product ? J'ai créé les deux pour cascader les changements : ON UPDATE CASCADE . Si un product_id o bill_id doit être modifiée, la modification est répercutée en cascade sur toutes les entrées dépendantes de la base de données. bill_product et rien ne se casse. Ce sont juste des références sans signification propre.
  J'ai aussi utilisé ON DELETE CASCADE pour bill_id : Si une facture est supprimée, ses détails meurent avec elle.
  Ce n'est pas le cas pour les produits : Vous ne voulez pas supprimer un produit qui est utilisé dans une facture. Postgres émettra une erreur si vous tentez de le faire. Vous devez ajouter une autre colonne à product pour marquer les lignes obsolètes ("soft-delete") à la place.

• Toutes les colonnes de cet exemple de base finissent par être NOT NULL donc NULL ne sont pas autorisées. (Oui, tous columns - les colonnes de clé primaire sont définies UNIQUE NOT NULL automatiquement.) C'est parce que NULL Les valeurs n'auraient aucun sens dans aucune des colonnes. Cela facilite la vie d'un débutant. Mais vous ne vous en sortirez pas si facilement, vous devez comprendre NULL manipulation de toute façon. Des colonnes supplémentaires pourraient permettre NULL les valeurs, les fonctions et les jointures peuvent introduire NULL les valeurs dans les requêtes, etc.

• Lisez le chapitre sur CREATE TABLE dans le manuel .

• Les clés primaires sont implémentées avec une clé unique indice sur les colonnes clés, ce qui rend les requêtes avec des conditions sur la ou les colonnes PK rapides. Cependant, la séquence des colonnes clés est importante pour les clés à plusieurs colonnes. Puisque la PK sur bill_product est sur (bill_id, product_id) dans mon exemple, vous pourriez vouloir ajouter un autre index sur seulement product_id o (product_id, bill_id) si vous avez des requêtes recherchant un product_id et non bill_id . Voir :

  • Clé primaire composite PostgreSQL
  • Un index composite est-il également bon pour les requêtes sur le premier champ ?
  • Fonctionnement des index dans PostgreSQL

• Lire l'article chapitre sur les index dans le manuel .