création nouvelle BDD et tables intermédiaires

[toufik51]

Bonjour à tous,
Etant en pleine conception de base de données, je me retrouve, avec mon équipe, confronté à un dilemne.
Cette discussion fait suite à la lecture de cette discussion très intéressante !

Je vais donc essayer d'exposer mon problème en version simplifiée et les différentes solutions auxquelles nous avons pensé.
Donc je modélise un schéma de base pour une refonte d'appli gérant les interventions dans des résidences. Les interventions peuvent avoir lieu sur un appartement, un couloir (lot d'appartements), un étage ou un bâtiment. Les résidences peuvent avoir plusieurs bâtiments. Les appartements ont un type ainsi que les couloirs (rien à voir entre les 2 types).

Voilà en gros la partie qui me pose souci, sachant que les appartements, les couloirs et les étages ont des informations communes (superficie...), mais que les bâtiments en ont d'autres (syndic, adresse).

Et les solutions auxquelles nous avons pensé, et attention, il y en a de très mauvaises :

• Solution en place sur l'outil actuel : 4 tables Appartement, Couloir, Etage et Batiment, chacune étant lié à celle "du dessus" en terme d'appartenance avec des clés étrangères. A la création d'un bâtiment, création d'un étage dit "Tous" qui correspond fonctionnellement à tous les étages de ce bâtiment. A la création d'un étage, création d'un couloir dit "Tous" et pareil pour les appartements. Ensuite, ma table Intervention ne pointe que vers Appartement avec une clé étrangère, et si une intervention a lieu pour un étage, on pointe donc vers l'Appartement Tous du couloir Tous de l'étage concerné.
  Avantage : une seule clé étrangère sur Intervention (et encore, pas convaincu)
  Inconvénient : un nombre d'entrées énorme pour les Appartements quand on commence à multiplier les bâtiments/étages/couloir (entre autres)
  
  
• Solution pensée 1 : toujours les 4 tables pour chaque granularité de lieu (Appartement, Couloir, Etage et Batiment), chacune étant toujours liée à celle "du dessus", mais la table Intervention a 4 clés étrangères vers chacune des lieux, et une et une seule n'est renseignée.
  Avantage : le nombre d'enregistrements qu'il faut, pas plus.
  Inconvénient : beaucoup de null dans la table Intervention
  
  
• Solution pensée 2 : après lecture de la discussion en tête de ce message, création d'une table Lieu avec une autre Type_lieu (Appartement, Couloir, Etage, Bâtiment). La table Lieu a 3 clés étrangères pour gérer les id_lieu_couloir, l'id_lieu_etage et l'id_lieu_batiment pères éventuels.
  Avantage : une seule table
  Inconvénient : beaucoup de null là aussi dans les id_pere éventuels
  
  
• Solution pensée 3 : une évolution de la précédente, avec une seule clé étrangère pour gérer le père direct, et pas tous les ascendants. Un appartement ne pointe que vers un couloir, ce couloir pointe vers l'étage...
  Avantage : l'id est null juste pour les bâtiments
  Inconvénients : un peu galère pour afficher, pour une intervention, le lieu complet (les 4 informations).
  
  
• Solution pensée 3bis : une évolution encore de la précédente. Pour l'instant, on considère que la table Lieu contient tous les détails des lieux. Là, on partirait sur une table Détail_Appartement_Couloir_Etage avec les détails communs à ces 3 lieux, et une table Détail_batiment qui contient les détails des batiments uniquement. Ces 2 tables pointeraient vers Lieu.
  Avantage : encore une fois, moins de null dans la base, puisque chaque table a des enregistrements "complets" puisqu'avec des données qui la concerne uniquement.
  Inconvénient : là encore, on multiplie les tables, et la remontée des infos me parait complexe (à première vue). Puis quid des Type_Appartement et Type_Couloir ?

Voilà donc le point sur notre réflexion. Pour info, on part sur du SQL Server ou de l'Oracle (choix laissé aux clients).

Merci par avance à ceux qui pourront éclairer notre lanterne, nous souhaitons quand même partir sur des bases solides

[CinePhil]

Les interventions peuvent avoir lieu sur un appartement, un couloir (lot d'appartements), un étage ou un bâtiment.

Quand tu dis "un couloir (lot d'appartements)", cela veut-il dire que l'intervention se fait :
1) Dans tous les appartements du couloir ?
2) Dans le couloir ?

Dans le cas 1), d'après la description de la situation actuelle, j'ai l'impression qu'il n'est quand même pas indispensable d'enregistrer précisément dans quels appartements à eu lieu une intervention "couloir" mais que ce serait implicitement dans tous les appartements du couloir, ce qui peut se retrouver par interrogation de la BDD, les appartements ne changeant pas de couloir.

Partant de ces considérations, je suggérerais le modèle suivant...

D'abord un héritage de tous les types de lieux pour rassembler leurs attributs communs et associer les interventions à une seule entité Lieu.
Appartement -(1,1)----Etre----0,1- Lieu -0,n----Subir----1,1- Intervention
Couloir -(1.1)----Etre----0,1-------------|
Etage -(1,1)----Etre----0,1---------------|
Batiment -(1.1)----Etre----0,1----------|
Residence -(1,1)----Etre----0,1--------|

Appartement -1,1----Situer----1,n- Couloir -1,1----Situer----1,n- Etage -1,1----Situer----1,n- Batiment -1,1----Appartenir----1,n- Residence

Ce qui donnerait les tables suivantes :
Lieu (l_id, attributs communs à tous les lieux)
Intervention (int_id, int_id_lieu, int_date...)
Residence (rsd_id_lieu...)
Batiment (btm_id_lieu, btm_id_residence...)
Etage (etg_id_lieu, etg_id_batiment...)
Couloir (clr_id_lieu, clr_id_etage...)
Appartement (apt_id_lieu, apt_id_couloir...)

Résultat : disparition du bonhomme NULL !

[toufik51]

Tout d'abord, merci pour ta réponse !

Envoyé par CinePhil

Quand tu dis "un couloir (lot d'appartements)", cela veut-il dire que l'intervention se fait :
1) Dans tous les appartements du couloir ?
2) Dans le couloir ?

Dans tous les appartements du couloir, c'est vraiment juste une granularité plus ou moins fine sur les 4 notions de lieu.

Envoyé par CinePhil

Ce qui donnerait les tables suivantes :
Lieu (l_id, attributs communs à tous les lieux)
Intervention (int_id, int_id_lieu, int_date...)
Residence (rsd_id_lieu...)
Batiment (btm_id_lieu, btm_id_residence...)
Etage (etg_id_lieu, etg_id_batiment...)
Couloir (clr_id_lieu, clr_id_etage...)
Appartement (apt_id_lieu, apt_id_couloir...)

Si je comprends bien, tu me proposes une solution intermédiaire au milieu des miennes ?
Le fait que les données soient communes entre Etage, Couloir et Appartement, cela n'influe pas ? N'y a-t-il pas une redondance à les stocker dans 3 tables différentes ? Cela concerne potentiellement 2 champs qui sont communs à ces 3 entités.

[CinePhil]

Envoyé par toufik51

Si je comprends bien, tu me proposes une solution intermédiaire au milieu des miennes ?
Le fait que les données soient communes entre Etage, Couloir et Appartement, cela n'influe pas ? N'y a-t-il pas une redondance à les stocker dans 3 tables différentes ? Cela concerne potentiellement 2 champs qui sont communs à ces 3 entités.

Tu peux faire un niveau d'héritage intermédiaire :

Appartement -(1,1)----Etre----0,1- Lieu_interieur -(1,1)----Etre----0,1- Lieu -0,n----Subir----1,1- Intervention
Couloir -(1.1)----Etre----0,1-------------------|
Etage -(1,1)----Etre----0,1---------------------|
Batiment -(1.1)----Etre----0,1----------------------------------------------------------|
Residence -(1,1)----Etre----0,1--------------------------------------------------------|

Ce qui donnerait les tables suivantes :
Lieu (l_id, attributs communs à tous les lieux)
Intervention (int_id, int_id_lieu, int_date...)
Lieu_interieur (li_id_lieu, attributs communs aux lieux intérieurs)
Residence (rsd_id_lieu...)
Batiment (btm_id_lieu, btm_id_residence...)
Etage (etg_id_lieu_interieur, etg_id_batiment...)
Couloir (clr_id_lieu_interieur, clr_id_etage...)
Appartement (apt_id_lieu_interieur, apt_id_couloir...)

[toufik51]

Envoyé par CinePhil

Tu peux faire un niveau d'héritage intermédiaire :

Appartement -(1,1)----Etre----0,1- Lieu_interieur -(1,1)----Etre----0,1- Lieu -0,n----Subir----1,1- Intervention
Couloir -(1.1)----Etre----0,1-------------------|
Etage -(1,1)----Etre----0,1---------------------|
Batiment -(1.1)----Etre----0,1----------------------------------------------------------|
Residence -(1,1)----Etre----0,1--------------------------------------------------------|

Ce qui donnerait les tables suivantes :
Lieu (l_id, attributs communs à tous les lieux)
Intervention (int_id, int_id_lieu, int_date...)
Lieu_interieur (li_id_lieu, attributs communs aux lieux intérieurs)
Residence (rsd_id_lieu...)
Batiment (btm_id_lieu, btm_id_residence...)
Etage (etg_id_lieu_interieur, etg_id_batiment...)
Couloir (clr_id_lieu_interieur, clr_id_etage...)
Appartement (apt_id_lieu_interieur, apt_id_couloir...)

Du coup, la table Lieu aurait un id en int auto incrémenté, et toutes les autres tables en découlant aurait des id à la fois PK et FK ? FK pointant vers l'id de la table Lieu ?

EDIT : j'en profite pour rajouter une question : nous avions prévu de typer le lieu, tu ne l'évoques pas. Pour toi, le fait d'avoir les autres tables liées suffit pour savoir le type ?
Et sinon, finalement, il n'y aurait qu'un seul champ commun aux lieux "intérieurs". Cela vaut-il le coup d'avoir cette table intermédiaire puisqu'elle n'aurait qu'un champ ? Ou vaut-il mieux lier directement mes 4 finales à Lieu ?

[CinePhil]

Envoyé par toufik51

Du coup, la table Lieu aurait un id en int auto incrémenté, et toutes les autres tables en découlant aurait des id à la fois PK et FK ? FK pointant vers l'id de la table Lieu ?

Oui.

EDIT : j'en profite pour rajouter une question : nous avions prévu de typer le lieu, tu ne l'évoques pas. Pour toi, le fait d'avoir les autres tables liées suffit pour savoir le type ?

Oui. Tu peux créer des vues pour reconstituer chaque type.

Et sinon, finalement, il n'y aurait qu'un seul champ commun aux lieux "intérieurs". Cela vaut-il le coup d'avoir cette table intermédiaire puisqu'elle n'aurait qu'un champ ? Ou vaut-il mieux lier directement mes 4 finales à Lieu ?

Si il n'y a qu'une colonne, et s'il n'y en aura jamais qu'une, on peut peut-être se passer du sous-type oui.