Dans quel cas append un champ ID à une table many-to-many peut-être une bonne idée?

Disons qu'il y a deux entités – Product et Image avec une relation plusieurs-à-plusieurs entre elles. L'ordre des images associées à chaque produit est important.

 Product ------------------------------------ ProductID (primary key) ProductName ... Image ------------------------------------ ImageID (primary key) Url Size ... 

Quels sont les inconvénients et les avantages des trois approches «table de pont» plusieurs-à-plusieurs suivantes pour résoudre ce problème?

 ProductImage ------------------------------------ ProductImageID (primary key, identity) ProductID (foreign key) FullImageID (foreign key) ThumbImageID (foreign key) OrderNumber 

ou

 ProductImage ------------------------------------ ProductID (primary key, foreign key) IndexNumber (primary key) FullImageID (foreign key) ThumbImageID (foreign key) 

ou

 ProductImage ------------------------------------ ProductID (primary key, foreign key) FullImageID (primary key, foreign key) ThumbImageID (foreign key) OrderNumber (index) 

Il n'y a aucun but (que j'ai jamais trouvé) en ajoutant une clef de substitution (c.-à-d. Le champ IDENTITY ) à une table de «pont» de many-to-many (ou peu importe comment vous l'appelez). Cependant, aucun de vos schémas proposés n'est correct.

Pour get la configuration idéale, vous devez d'abord déterminer l'étendue / le context de l'exigence suivante:

L'ordre des images associées à chaque produit est important.

La command des images doit-elle être la même, les unes par rapport aux autres, quels que soient les produits auxquels elles sont associées? Cela signifie que les images A, B, C et D sont toujours dans l'ordre alphabétique, quelle que soit la combinaison de ces produits.

Ou, la command peut-elle changer en fonction du produit auquel l'image est associée?

Si la command des images doit restr cohérente entre les produits, le champ OrderNumber de command doit être OrderNumber dans la table Image. Sinon, si la command peut changer par produit, le champ OrderNumber de command va dans cette table de pont / relation.

Dans tous les cas:

  • le PK est la combinaison des FK:

    Une key primaire unique et, espérons-le, fiable (c'est-à-dire qui ne change pas), identifie chaque ligne. Et si possible, cela devrait être significatif. L'utilisation de la combinaison des deux champs FK donne exactement cela tout en renforçant cette unicité (de sorte qu'un produit ne peut pas recevoir la même image plusieurs fois, et vice versa). Même si ces deux champs n'ont pas été choisis comme PK, ils doivent toujours être regroupés en UNIQUE INDEX ou UNIQUE CONSTRAINT pour renforcer l'intégrité de ces données (ce qui en fait une «key alternative»). Mais puisque ces identifiants ne changeront pas (seulement insérés et supprimés) ils sont bien adaptés pour être le PK. Et si vous utilisez SQL Server (et peut-être d'autres) et décidez d'utiliser cette PK comme index clusterisé, vous aurez l'avantage d'avoir à la fois ProductID et ImageID dans tous les index non clusterisés. Donc, quand vous avez besoin de sortinger par [OrderNumber] , l'Index Non Clustered de ce champ sera automatiquement un index de couverture car les deux seuls champs de données dont vous avez besoin sont déjà là.

    D'autre part, placer le champ [OrderNumber] dans le PK a quelques inconvénients:

    • Cela peut changer, ce qui n'est pas idéal pour les PK.
    • Il supprime la possibilité d'appliquer un ProductID et un ImageID uniquement les uns aux autres. Par conséquent aurait besoin de cette UNIQUE INDEX ou CONSTRAIN UNIQUE supplémentaire afin de maintenir l'intégrité des données. Sinon, même si vous incluez les 3 champs dans le PK, cela permet toujours à la combinaison ProductID + ImageID d'être présente plusieurs fois pour différentes valeurs de IndexID.
  • il n'y a pas besoin d'un champ IDENTITY:

    Avec les informations ci-dessus à l'esprit, toutes les exigences d'un PK ont déjà été satisfaites. L'ajout d'un champ de key de substitution / auto-incrémentation n'ajoute aucune valeur, mais prend de l'espace supplémentaire.

    Et pour répondre à la réponse typique à la déclaration ci-dessus concernant la key de substitution ne ajoutant aucune valeur, certains diront qu'il rend les JOINs plus faciles si cette combinaison de ProductID + ImageID doit être Foreign Keyed à une table enfant. Peut-être que chaque combinaison peut avoir des attributes qui ne sont pas singuliers comme [OrderNum] . Un exemple pourrait être "tags" (bien que ceux-ci seraient très probablement associés à juste ImageID, mais cela fonctionne comme un exemple de base). Certaines personnes préfèrent ne placer qu'un seul champ ID dans la table enfant car c'est "plus facile". Eh bien, ce n'est pas plus facile. En plaçant à la fois les champs ImageID et ProductID dans la table enfant et en faisant le FK à la fois sur cette PK, vous avez maintenant des valeurs significatives dans la table enfant et vous n'aurez pas besoin de JOIN à cette table [ProductImage] tout le time cette information (qui sera probablement nécessaire dans la plupart des requêtes qui ne listnt pas simplement ou ne mettent pas à jour ces attributes pour une combinaison ProductID + ImageID particulière). Et si cela n'est pas clair, l'ajout d'une key de substitution nécessite toujours un INDEX UNIQUE ou une CONTRAINTE UNIQUE pour renforcer l'intégrité des données des combinaisons ProductID + ImageID uniques (comme indiqué ci-dessus dans le premier point).

    Et placer les deux champs ID dans la table enfant est une autre raison de restr loin des champs qui peuvent changer lors du choix d'un PK: si vous avez des FK définis, vous devez définir le FK sur ON UPDATE CASCADE afin que la nouvelle valeur du PK se propage à toutes les tables enfants, sinon le UPDATE échouera.

 ProductImage ------------------------------------ ProductID (primary key, foreign key to Product table) FullImageID (primary key, foreign key to Image table) ThumbImageID (foreign key; shouldn't this field be in the Image table?) OrderNumber TINYINT (only here if ordering is per Product, else is in Image table) 

La seule raison que je peux voir pour append une key de substitution dans cette situation est s'il y a une exigence d'un autre logiciel. Des choses telles que la réplication SQL Server (ou était-ce Service Broker?) Et / ou Entity Framework et / ou la search de text intégral. Je ne sais pas si ces exemples l'exigent, mais j'ai certainement vu 1 ou 2 "caractéristiques" qui nécessitent un PK à un seul champ.

La meilleure façon d'y parvenir est d'avoir trois tables, une pour les produits, une pour les images et une pour leur relation.

 products -------- + product_id (pk) - product_name - product_description - ... images ------ + image_id (pk) - image_title - ... product_images -------------- + product_id (fk) + image_id (fk) 

Pourquoi avez-vous des tables séparées pour fullImage et thumbImage? Table1 est meilleur car il vous permet d'identifier des lignes individuelles à l'intérieur de la table.

Table2, je suis sûr que vous ne pouvez pas avoir deux keys primaires. Il pourrait être préférable d'avoir une table d'image comme suit.

 ImageId (primary) FullImage [actual value/FK] ThumbNail [actual value/FK] 

et alors,

 ProductImageID (primary) ProductID [FK] ImageID [FK] 

Comment cela aide, Cordialement, Rainy