Pourquoi ne pas avoir une clé étrangère dans une association polymorphe?

Pourquoi ne pas avoir une clé étrangère dans une association polymorphe, telle que celle représentée ci-dessous en tant que modèle Rails?

class Comment  true end class Article  :commentable end class Photo  :commentable #... end class Event  :commentable end 

Une clé étrangère doit référencer une seule table parente. Ceci est fondamental pour la syntaxe SQL et la théorie relationnelle.

Une association polymorphe est lorsqu’une colonne donnée peut faire référence à deux ou plusieurs tables parent. Vous ne pouvez en aucun cas déclarer cette contrainte en SQL.

La conception des associations polymorphes enfreint les règles de conception des bases de données relationnelles. Je ne recommande pas de l’utiliser.

Il existe plusieurs alternatives:

  • Arcs exclusifs: créez plusieurs colonnes de clé étrangère, chacune faisant référence à un parent. Appliquez que l’une de ces clés étrangères peut être non NULL.

  • Inverser la relation: utilisez trois tables plusieurs-à-plusieurs, chacune contenant des commentaires et un parent respectif.

  • Concrete Supertable: Au lieu de la super-classe implicite “commentable”, créez une table réelle à laquelle chacune de vos tables parentes fait référence. Ensuite, liez vos commentaires à cette supertable. Le code de pseudo-rails serait quelque chose comme ce qui suit (je ne suis pas un utilisateur de Rails, alors traitez ceci comme un guide, pas un code littéral):

     class Commentable < ActiveRecord::Base has_many :comments end class Comment < ActiveRecord::Base belongs_to :commentable end class Article < ActiveRecord::Base belongs_to :commentable end class Photo < ActiveRecord::Base belongs_to :commentable end class Event < ActiveRecord::Base belongs_to :commentable end 

Je couvre également les associations polymorphes dans ma présentation Practical Object-Oriented Models en SQL et mon livre SQL Antipatterns: Éviter les pièges de la programmation de bases de données .


Re votre commentaire: Oui, je sais qu’il existe une autre colonne qui indique le nom de la table que la clé étrangère indique. Cette conception n'est pas prise en charge par les clés étrangères dans SQL.

Que se passe-t-il, par exemple, si vous insérez un commentaire et nommez "Vidéo" comme nom de la table parente pour ce Comment ? Aucune table nommée "Vidéo" n'existe. L'insert doit-il être interrompu avec une erreur? Quelle contrainte est violée? Comment le SGBDR sait-il que cette colonne est censée nommer une table existante? Comment gère-t-il les noms de table insensibles à la casse?

De même, si vous supprimez la table Events , mais que vous avez des lignes dans les Comments indiquant les événements comme étant leur parent, quel devrait être le résultat? La table de repository doit-elle être abandonnée? Les lignes dans les Comments doivent-elles être orphelines? Devraient-ils changer pour faire référence à un autre tableau existant tel que Articles ? Est-ce que les valeurs d'ID qui désignaient des Events un sens quand elles pointent vers des Articles ?

Ces dilemmes sont tous dus au fait que les associations polymorphes dépendent de l'utilisation de données (c.-à-d. Une valeur de chaîne) pour faire référence aux métadonnées (un nom de table). Ceci n'est pas supporté par SQL. Les données et métadonnées sont distinctes.


J'ai du mal à comprendre ma proposition "Concrete Supertable".

  • Définir des Commentable tant que table SQL réelle, et pas seulement un adjectif dans votre définition de modèle Rails. Aucune autre colonne n'est nécessaire.

     CREATE TABLE Commentable ( id INT AUTO_INCREMENT PRIMARY KEY ) TYPE=InnoDB; 
  • Définissez les tables Articles , Photos et Events tant que "sous-classes" de Commentable , en faisant de leur clé primaire une référence de clé étrangère.

     CREATE TABLE Articles ( id INT PRIMARY KEY, -- not auto-increment FOREIGN KEY (id) REFERENCES Commentable(id) ) TYPE=InnoDB; -- similar for Photos and Events. 
  • Définissez la table Comments avec une clé étrangère à Commentable .

     CREATE TABLE Comments ( id INT PRIMARY KEY AUTO_INCREMENT, commentable_id INT NOT NULL, FOREIGN KEY (commentable_id) REFERENCES Commentable(id) ) TYPE=InnoDB; 
  • Lorsque vous souhaitez créer un Article (par exemple), vous devez également créer une nouvelle ligne dans Commentable . De même pour les Photos et les Events .

     INSERT INTO Commentable (id) VALUES (DEFAULT); -- generate a new id 1 INSERT INTO Articles (id, ...) VALUES ( LAST_INSERT_ID(), ... ); INSERT INTO Commentable (id) VALUES (DEFAULT); -- generate a new id 2 INSERT INTO Photos (id, ...) VALUES ( LAST_INSERT_ID(), ... ); INSERT INTO Commentable (id) VALUES (DEFAULT); -- generate a new id 3 INSERT INTO Events (id, ...) VALUES ( LAST_INSERT_ID(), ... ); 
  • Lorsque vous souhaitez créer un Comment , utilisez une valeur qui existe dans Commentable .

     INSERT INTO Comments (id, commentable_id, ...) VALUES (DEFAULT, 2, ...); 
  • Lorsque vous souhaitez interroger les commentaires d'une Photo donnée, effectuez des jointures:

     SELECT * FROM Photos p JOIN Commentable t ON (p.id = t.id) LEFT OUTER JOIN Comments c ON (t.id = c.commentable_id) WHERE p.id = 2; 
  • Lorsque vous n'avez que l'id d'un commentaire et que vous souhaitez trouver la ressource commentable, c'est un commentaire. Pour cela, vous trouverez peut-être utile que la table Commentaires puisse désigner la ressource à laquelle elle fait référence.

     SELECT commentable_id, commentable_type FROM Commentable t JOIN Comments c ON (t.id = c.commentable_id) WHERE c.id = 42; 

    Ensuite, vous devrez exécuter une seconde requête pour obtenir les données de la table de ressources correspondante (Photos, Articles, etc.), après avoir découvert commentable_type auquel vous souhaitez vous connecter. Vous ne pouvez pas le faire dans la même requête, car SQL requirejs que les tables soient nommées explicitement; vous ne pouvez pas joindre à une table déterminée par les résultats de données dans la même requête.

Certes, certaines de ces étapes enfreignent les conventions utilisées par Rails. Mais les conventions de Rails sont erronées en ce qui concerne la conception correcte des bases de données relationnelles.

Bill Karwin a raison de dire que les clés étrangères ne peuvent pas être utilisées avec des relations polymorphes en raison du fait que SQL n’a pas vraiment un concept natif de relations polymorphes. Mais si votre objective d’avoir une clé étrangère est d’appliquer l’intégrité référentielle, vous pouvez la simuler via des déclencheurs. Cela permet d’obtenir une firebase database spécifique, mais voici quelques déclencheurs récents que j’ai créés pour simuler le comportement de suppression en cascade d’une clé étrangère dans une relation polymorphe:

 CREATE FUNCTION delete_related_brokerage_subscribers() RETURNS sortinggger AS $$ BEGIN DELETE FROM subscribers WHERE referrer_type = 'Brokerage' AND referrer_id = OLD.id; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER cascade_brokerage_subscriber_delete AFTER DELETE ON brokerages FOR EACH ROW EXECUTE PROCEDURE delete_related_brokerage_subscribers(); CREATE FUNCTION delete_related_agent_subscribers() RETURNS sortinggger AS $$ BEGIN DELETE FROM subscribers WHERE referrer_type = 'Agent' AND referrer_id = OLD.id; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER cascade_agent_subscriber_delete AFTER DELETE ON agents FOR EACH ROW EXECUTE PROCEDURE delete_related_agent_subscribers(); 

Dans mon code, un enregistrement de la table de brokerages ou un enregistrement du tableau des agents peut être associé à un enregistrement de la table des subscribers .