Inconvénients de lutilisation dune clé étrangère Nullable au lieu de créer une table dintersection

Les deux modèles représentent des relations différentes.

En utilisant une table de jointure , vous modélisez une relation plusieurs-à-plusieurs.

En utilisant une simple clé étrangère, vous modélisez une relation un-à-plusieurs.

Linconvénient dun étranger nullable key est incapable de modéliser la relation plusieurs-à-plusieurs, si cest ce que vous essayez daccomplir.

En fonction de votre modification de la question, vous divisez effectivement la table des élèves en deux tables avec la même clé. Je vois généralement cela sur des tables qui ont beaucoup trop de champs, donc quelquun les divise en deux pour être plus gérable (jappelle cela mettre du rouge à lèvres sur un cochon).

En divisant la table des étudiants, vous faites la deuxième table est facultative car un enregistrement na pas besoin dexister dans la deuxième table. Ce qui est très similaire à un champ qui na pas besoin dêtre défini car il peut être nul.

Si vous voulez une relation un-à-plusieurs, vous feriez bien mieux dutiliser une seule table et dautoriser lID de lécole pour être nul dans la table des étudiants. Il ny a aucune raison déviter les valeurs nulles dans les champs, même pour une clé étrangère. Cela signifie que la relation étrangère est facultative: les développeurs et les administrateurs de base de données le comprennent clairement, et le moteur de base de données sous-jacent devrait certainement fonctionner correctement.

Si vous êtes préoccupé par les jointures, ne vous inquiétez pas. Il existe une sémantique bien définie sur le fonctionnement des jointures avec des champs nuls. En utilisant une seule table, vous pouvez joindre deux tables au lieu de trois.

Commentaires

• Donc, si je modélise une relation un-à-plusieurs (avec participation facultative du côté: 1), je devrais utiliser une clé étrangère malgré le fait quelle puisse avoir des valeurs NULL?
• @Tom oui, que est exactement comment le modéliser. Bien quil soit techniquement possible dutiliser une table de jointure, le modèle de données autorise plusieurs à plusieurs, vous aurez donc besoin de déclencheurs et dune logique de base de données pour éviter cela. Vous feriez mieux de restreindre la relation de manière à ce quil soit impossible dajouter des données incorrectes.
• Jai modifié ma question.Jai seulement fait de student_id une clé primaire dans la table School_has_Student, qui a gardé la relation un-à-plusieurs. Quels sont les inconvénients de cette méthode par rapport à lutilisation dune clé étrangère?
• @Tom Jai modifié ma réponse.

Vous avez écrit dans un commentaire ci-dessus:

le livre « Fundamentals of Database Systems » […] dit [.. .] quil est recommandé dutiliser une table dintersection sil y a beaucoup de valeurs NULL dans la colonne de clé étrangère (par exemple: si 98% des employés ne gèrent pas de service)

Lorsquil y a beaucoup de valeurs NULL dans la colonne de clé étrangère, vos programmes devront gérer cette colonne presque vide pour chaque enregistrement quils traitent. La colonne occupera probablement un peu despace disque même si dans 98% des cas, il est vide, interroger la relation signifie interroger cette colonne qui vous donne plus de trafic réseau, et si vous utilisez un ORM qui génère vos classes à partir de vos tables, vos programmes auront également besoin de plus despace chez le client que nécessaire. À laide dun inters ection table évite cela, il ny aura que des enregistrements de lien nécessaires où la clé étrangère équivalente ne serait pas NULL autrement.

À lopposé, si vous navez pas seulement quelques valeurs NULL, disons 50% ou plus les relations ne sont pas NULL, lutilisation dune table dintersection vous donne leffet inverse – plus despace disque, une complexité plus élevée résultant en plus de trafic réseau, etc.

Donc, lutilisation dune table dintersection est juste une forme doptimisation, uniquement sensible pour un cas particulier, et surtout de nos jours, où lespace disque et la mémoire sont devenus moins chers, beaucoup moins fréquemment utilisés. Notez que « Fundamentals of Database Systems » a été écrit il y a plus de 20 ans (jai trouvé une référence à la deuxième édition de 1994), et je suppose que cette recommandation était déjà là à ce moment-là. Avant 1994, loptimisation de lespace était probablement beaucoup plus importante quaujourdhui, car le stockage de masse était encore plus cher et les ordinateurs et les réseaux étaient beaucoup plus lents quaujourdhui.

En guise de remarque à un commentaire pointilleux: le La déclaration ci-dessus essaie simplement danticiper ce que lauteur de « Fundamentals of Database Systems » avait en tête avec sa recommandation, je suppose quil faisait une déclaration approximative et générale, valable pour la plupart des systèmes. Dans certaines bases de données, il existe dautres optimisations possibles comme les « colonnes éparses » qui rendent lutilisation dune table dintersection encore plus obsolète.

Alors ne vous méprenez pas sur cette recommandation. Le livre ne le dit pas vous préférez les tables dintersection pour les relations {0,1}:n en général, ou – comme vous lavez écrit – que cest la « bonne manière ». Utilisez des optimisations comme celle-ci qui ne compliqueront vos programmes que lorsque vous en avez vraiment besoin.

Commentaires

• Vous ‘ en supposant beaucoup sur l’implémentation du base de données, en particulier si lon considère que lOP na ‘ t mentionner une base de données spécifique. Il est ‘ que la base de données est suffisamment intelligente pour seulement une petite quantité despace pour les colonnes clairsemées.
• @gardenhead: ce qui vous fait croire que cest  » plus que probable « ?
• Le fait que les bases de données aient existent depuis des décennies et sont hautement optimisés car ils sont un composant critique de la plupart des infrastructures.
• @gardenhead: il me semble que vous faites beaucoup plus dhypothèses injustifiées que moi. Néanmoins, consultez ma modification.

Le modèle conceptuel ressemblera à ceci, qui est très peu orthodoxe pour en dire moins:

Le modèle physique ressemblera à ceci, ce qui est déroutant pour en dire moins (les gens penseront que cest M: M à moins quils ne voient de près):

Ma suggestion:

Si vous avez comme, de nombreuses colonnes (FK ou autre), qui ne sappliquent pas à la plupart des élèves, séparez les tables en tables de rôles avec des rels 1: 1. Mais ce nest pas parce quelles sont FK, cest parce que les colonnes ne sappliquent pas à la plupart des lignes.

Sinon , Nullable FK est une partie normale dune base de données et les tables de jointure sont généralement pour M: M rels.

Les utilisations courantes des rels 1: 1 sont pour les tables de rôles ayant des colonnes qui sappliquent uniquement si lentité est dun certain type, et lextraction des colonnes BLOB pour des considérations de performances ou de stockage. Avoding des valeurs nulles dans les FK nest pas une utilisation courante pour cela.

En plus dautres réponses, je voudrais souligner quune valeur nulle pour la clé étrangère est ambiguë. Cela signifie-t-il:

1) Lécole de lélève (le cas échéant) est inconnue (cest la signification standard de « null » – la valeur est inconnue)

2) Cest savoir si lélève a une école ou non et quil nen a aucune

Si vous utilisez la signification standard de null, comment représenteriez-vous « lélève na pas décole » dans votre modèle de clé étrangère. Dans ce cas, vous « auriez probablement à créer une entrée » no school « , avec son propre identifiant dans la table de lécole. (Pas idéal)

Commentaires

• Le livre  » Fundamentals of Database Systems  » mentionne quil existe 3 interprétations pour NULL, cela peut signifier: 1) Valeur inconnue. 2) Valeur indisponible ou non publiée. 3) Attribut non applicable (je pense que cette interprétation signifie que vous pouvez spécifier un NULL pour une clé étrangère).
• Cette ‘ est une liste utile mais la sémantique de null (ou de toute valeur réellement) est définissable par lutilisateur. Cest-à-direpeut signifier tout ce que le concepteur dit que cela signifie, sans se limiter à cette liste. Le problème est de savoir comment distinguer différentes significations lorsque plusieurs peuvent être nécessaires (ou même enregistrées involontairement)
• Alors suggérez-vous que je devrais créer une table dintersection au lieu dutiliser une clé étrangère Nullable?
• @Tom Oui, je pense que cest mieux dans ce cas
• @BradThomas – pour éviter la même ambiguïté lors de lutilisation dune table dintersection, représenteriez-vous le cas 2 (on sait que lélève a no school) par un enregistrement dans la table dintersection avec un NULL School_ID?

Les tables de base de données ont ceci belle chose appelée contraintes. Il est donc très facile de créer un tableau dintersection qui permet à seulement 1 de chaque élève dapparaître dans le tableau, mais de nombreuses écoles dans ce tableau. En fait, vous donner une

Théorie, cest bien mais au final vous êtes va modéliser votre base de données après les questions que vous « posez.

Si vous voulez poser des questions souvent avec la question: » quels élèves sont dans mon école « voulez-vous vraiment interroger lensemble de la table des élèves ou avoir un tableau dintersection facile.

Dans les bases de données: optimisez pour les questions que vous posez.

Là est un cas dutilisation où lutilisation dune troisième table peut en fait avoir du sens. Lexemple peut sembler purement hypothétique, mais jespère quil illustre bien mon propos. Supposons que vous ajoutiez plus de colonnes à la table students et qu’à un moment donné, vous décidiez d’imposer l’unicité des enregistrements via un index composite sur plusieurs colonnes. Il est très probable que vous « Vous devrez également inclure la colonne school_id, et ici les choses commencent à devenir compliquées. En raison de la façon dont SQL a été conçu, insérer plusieurs enregistrements identiques où school_id est NULL possible. Cela est parfaitement logique dun point de vue technique, mais est contre-intuitif et peut conduire à des résultats inattendus. Dautre part, imposer lunicité sur le table dintersection est facile.

Jai récemment dû modéliser une telle relation « facultative », où lexigence dune contrainte dunicité était due à une colonne dhorodatage. Laisser la clé étrangère Nullable dans la table a soudainement conduit à la possibilité dinsérer des enregistrements avec le même horodatage (supposons quil sagit dun enregistrement par défaut, défini sur des enregistrements qui nont pas été audités / approuvés oved encore) – et le seul moyen de sortir était de supprimer la colonne nullable.

Donc, comme vous pouvez le voir, cest un cas assez spécifique, et comme dautres lont noté, la plupart du temps, vous seriez parfaitement daccord avec tout les valeurs de NULL. Cela dépend vraiment des exigences spécifiques de votre modèle.

En plus des nombreuses bonnes suggestions déjà soumises, personnellement je « Je ne suis pas fan des clés étrangères à moins quelles ne soient vraiment nécessaires. Tout dabord, il y a la relation M: M que vous référencez. De plus, appeler une clé étrangère, et ainsi extraire ces données de table dans vos requêtes, introduit plus de complexité et dépend de taille de la table, performances plus lentes. Comme dautres lont dit, les champs FK nullables peuvent ne pas être pris en charge et peuvent créer des problèmes dintégrité des données.

Si vous définissez un état dans lequel lécole étudiante est inconnue ou vide, la valeur NULL ne différenciera pas ces conditions (encore une fois, nous « revenons à lintégrité des données.) La suggestion de table des rôles par Tulains est élégante et permet des valeurs nulles proprement.