Clé étrangère référençant plusieurs tables

Vous pouvez envisager un model de données Type / SubType. Cela ressemble beaucoup à la class / sous-class en programmation orientée object, mais beaucoup plus difficile à implémenter, et aucun SGBDR (dont je suis conscient) ne les supporte nativement. L'idée générale est:

• Vous définissez un type (bâtiment), créez une table pour elle, donnez-lui une key primaire
• Vous définissez deux ou plusieurs sous-types (ici, Hôpital, Clinique, École, Université), créez des tables pour chacun d'entre eux, faites des keys primaires … mais les keys primaires sont aussi des foreign keys qui referencent la table de Construction
• Votre table avec une colonne "ObjectType" peut maintenant être construite avec une key étrangère sur la table de construction. Vous devrez joindre quelques tables pour déterminer le type de bâtiment, mais vous devrez le faire de toute façon. Cela, ou stocker des données redondantes.

Vous avez remarqué le problème avec ce model, non? Qu'est-ce qui empêche un Building d'avoir des inputs dans deux ou plusieurs tables de sous-types? Content que tu aies demandé:

1. Ajoutez une colonne, peut-être "BuildingType", à Building, par exemple char (1) avec les valeurs autorisées de {H, C, S, U} indiquant (duh) le type de bâtiment.
2. Construire une contrainte unique sur BuildingID + BuildingType
3. Avoir la colonne BulidingType dans les sous-tables. Mettez une contrainte de vérification sur celle-ci afin qu'elle ne puisse être définie que sur la valeur (H pour la table Hôpitaux, etc.). En théorie, cela pourrait être une colonne calculée; en pratique, cela ne fonctionnera pas à cause de l'étape suivante:
4. Construire la key étrangère pour relier les tables en utilisant les deux colonnes

Voila: Étant donné une ligne BUILDING définie avec le type H, une input dans la table SCHOOL (avec le type S) ne peut pas être définie pour referencer le bâtiment.

Vous vous callbacklerez que j'ai dit que c'était difficile à mettre en œuvre.

En fait, la grande question est: est-ce que ça vaut le coup? S'il est logique d'implémenter les quatre types de construction (ou plus, comme le time passe) comme type / sous-type (autres avantages de normalisation: un location pour l'adresse et d'autres attributes communs à chaque bâtiment, avec attributes spécifiques au bâtiment stockés dans les sous-tables) cela pourrait bien valoir l'effort supplémentaire de build et de maintenir. Si non, vous revenez à la case départ: un model logique difficile à implémenter dans le SGBDR moderne.

Commençons au niveau conceptuel. Si nous considérons les hôpitaux, les cliniques, les écoles et les universités comme des classs d'entités, y a-t-il une superclass qui les généralise tous? Il y a probablement. Je ne vais pas essayer de vous dire ce que c'est, parce que je ne comprends pas votre sujet aussi bien que vous. Mais je vais procéder comme si nous pouvions appeler tous les «Institutions», et traiter chacune des quatre comme des sous-classs d'Institutions.

Comme d'autres répondants l'ont noté, l'extension de class / sous-class et l'inheritance ne sont pas embeddeds dans la plupart des systèmes de bases de données relationnelles. Mais il y a beaucoup d'aide, si vous connaissez les bons mots à la mode. Ce qui suit est destiné à vous apprendre les mots à la mode, dans le jargon de la database. Voici un résumé des mots à la mode: «Généralisation ER», «Spécialisation ER», «Héritage de table unique», «Héritage de table de class», «Clé primaire partagée».

Rester au niveau conceptuel, la modélisation ER est un bon moyen de comprendre datatables à un niveau conceptuel. Dans la modélisation ER, il y a un concept, «ER Généralisation», et un concept de contrepartie «Spécialisation ER» qui est parallèle au process de reflection que je viens de présenter ci-dessus comme «superclass / sous-class». La Spécialisation ER vous explique comment schématiser les sous-classs, mais elle ne vous dit pas comment les implémenter.

Ensuite, nous passons du niveau conceptuel au niveau logique. Nous exprimons datatables en termes de relations ou, si vous voulez, de tables SQL. Il y a quelques techniques pour implémenter des sous-classs. L'un est appelé "Single Table Inheritance". L'autre est appelé "Class Table Inheritance". En ce qui concerne l'inheritance de table de classs, il existe une autre technique qui porte le nom "Clé primaire partagée".

Dans votre cas avec l'inheritance de table de classs, nous concevons d'abord une table appelée "Institutions", avec un champ Id, un champ de nom, et tous les champs qui appartiennent aux institutions, peu importe lequel des quatre types ils sont. Des choses comme les champs d'adresse postale, par exemple. Encore une fois, vous comprenez mieux vos données que moi et vous pouvez find les champs qui se trouvent dans les quatre tables existantes. Nous remplissons le champ id de la manière habituelle.

Ensuite, nous concevons quatre tables appelées «Hôpitaux», «Cliniques», «Écoles» et «Universités». Ceux-ci contiendront un champ d'identification, plus tous les champs de données qui concernent uniquement ce type d'institution. Par exemple, un hôpital pourrait avoir une «capacité de lit». Encore une fois, vous comprenez mieux vos données que moi, et vous pouvez les comprendre à partir des champs de vos tables existantes qui ne sont pas inputs dans la table Institutions.

C'est là qu'intervient la «key primaire partagée». Lorsqu'une nouvelle input est faite dans «Institutions», nous devons faire une nouvelle input parallèle dans l'une des quatre tables de sous-classs spécialisées. Mais nous n'utilisons pas une sorte de fonction autonumber pour remplir le champ id. Au lieu de cela, nous mettons une copy du champ id de la table "Institutions" dans le champ id de la table de sous-class.

C'est un peu de travail, mais les avantages en valent bien la peine. La key primaire partagée renforce la nature bi-univoque de la relation entre les inputs de sous-class et les inputs de superclass. Il permet de joindre des données de super-class et des sous-classs de manière simple, facile et rapide. Il élimine le besoin d'un champ spécial pour vous indiquer dans quelle sous-class appartient une institution donnée.

Et, dans votre cas, il fournit une réponse pratique à votre question initiale. La key étrangère à laquelle vous avez demandé à l'origine est maintenant toujours une key étrangère de la table Institutions. Et, en raison de la magie de shared-primary-key, la key étrangère reference également l'input dans la table de sous-class appropriée, sans travail supplémentaire.

Vous pouvez créer quatre vues qui combinent datatables d'institution avec chacune des quatre tables de sous-classs, pour plus de commodité.

Recherchez "Spécialisation ER", "Héritage de table de class", "Clé primaire partagée" et peut-être "Héritage de table unique" sur le Web, et ici dans SO. Il existe des balises pour la plupart de ces concepts ou techniques ici dans SO.

Vous pouvez mettre un triggersur sur la table et appliquer l'intégrité référentielle à cet endroit. Je ne pense pas qu'il existe vraiment une fonctionnalité prête à l'emploi pour mettre en œuvre cette exigence.