Héritage dans une base de données Access

Etant donné que tous les champs sont présents dans la table, il ne faut pas perdre de vue que suivant le rôle de l'intervenant, certaines informations ne seront pas définies et donc non stockées. Ainsi, pour un salarié, les champs Commentaires et DatePremierContrat resteront vides. De même, dans le cas d'un indépendant, les champs DateEmbauche, Grade et Service qui ne pourront être complétés.

Dans l'extrait ci-dessus 9 cellules sur 32 ne sont pas remplies soit près de 30% des champs. Sans compter la taille véritablement réservée par les champs en fonction de leur type, on peut estimer le taux de remplissage de la table à 60%, ce qui est assez faible. Il faut savoir cependant que ce calcul est théorique : l'incidence est moindre sur les champs texte puisque le moteur Jet ne réserve pas d'avantage de place que nécessaire.

Quelques palliatifs existent face à cette perte d'octets. La principale solution consiste à donner un double sens (voire plus) à un même champ en fonction du rôle de l'enregistrement. Toutefois, il ne faut pas tomber dans l'excès et tenter de regrouper des données de type différent. Ici, par exemple, il ne serait pas trop incohérent de transformer les champs DateEmbauche et DatePremierContrat en un unique champ DateArrivee. Dans le cas d'une telle structure, il est alors possible de faire abstraction de ce regroupement dans les requêtes rqtSalarie et rqtIndependant en utilisant un alias sur le champ DateArrivee.

SELECT 
  tblIntervenant.Id, 
  tblIntervenant.Nom, 
  tblIntervenant.Prenom, 
  tblIntervenant.Adresse, 
  tblIntervenant.Ville, 
  tblIntervenant.CP, 
  tblIntervenant.DateArrivee AS DateEmbauche,
  tblIntervenant.Grade, 
  tblIntervenant.Service
FROM tblIntervenant
WHERE Role=1;

Dans le cas de regroupement moins explicite où il y a une réelle perte du sens des champs utilisés, il est nécessaire de commenter le projet de telle sorte à faciliter les éventuelles maintenances . Pour cela, la colonne Description de l'éditeur de table est idéale.

En créant plusieurs requêtes, il est possible de reproduire le mécanisme de "vue" des SGBD plus évolués tels que SQL Server. Ainsi, l'utilisateur (ou le développeur) a à sa disposition des objets tels qu'ils sont représentés dans la vie réelle et ce, quelque soit la structure physique de la base de données. Dans le cas présent, il s'agit de donner l'illusion de deux tables tblSalarie et tblIndependant distinctes.

L'exercice se décompose en deux étapes :

• Détermination des champs à conserver
• Restriction des enregistrements au rôle concerné

Si la première étape ne pose pas de difficulté, la seconde nécessite de définir à quoi correspondent les valeurs du champ rôle. Dans la majorité des cas, le champ rôle sera de type numérique et ses valeurs (1,2,3, etc.) correspondront à l'ordre d'apparition des entités dans le MCD. Dans le modèle conceptuel vu plus haut et en considérant un sens de lecture de gauche à droite, il se dégage les prédicats suivants :

• Salarié : rôle=1
• Indépendant : rôle=2

Code SQL de la requête rqtSalarie :

SELECT 
  tblIntervenant.Id, 
  tblIntervenant.Nom, 
  tblIntervenant.Prenom, 
  tblIntervenant.Adresse, 
  tblIntervenant.Ville, 
  tblIntervenant.CP, 
  tblIntervenant.DateEmbauche, 
  tblIntervenant.Grade, 
  tblIntervenant.Service
FROM tblIntervenant
WHERE Role=1;

Code de la requête rqtIndependant :

SELECT 
  tblIntervenant.Id, 
  tblIntervenant.Nom, 
  tblIntervenant.Prenom, 
  tblIntervenant.Adresse, 
  tblIntervenant.Ville, 
  tblIntervenant.CP, 
  tblIntervenant.DatePremierContrat, 
  tblIntervenant.Commentaires
FROM tblIntervenant
WHERE tblIntervenant.role=2

Au-delà de la perte d'espace disque, c'est, je pense, le principal défaut de cette méthode de conception. En effet, si la structure de la table paraît très simple, il n'en reste pas moins que d'autres mécanismes doivent intervenir pour garantir l'intégrité et la cohérence des données.

Tout d'abord, reprenons la requête rqtSalarie :

SELECT 
  tblIntervenant.Id, 
  tblIntervenant.Nom, 
  tblIntervenant.Prenom, 
  tblIntervenant.Adresse, 
  tblIntervenant.Ville, 
  tblIntervenant.CP, 
  tblIntervenant.DateEmbauche, 
  tblIntervenant.Grade, 
  tblIntervenant.Service
FROM tblIntervenant
WHERE Role=1;

Si le résultat attendu (c'est-à-dire l'affichage des salariés exclusivement) ne fait aucun doute, il n'en est pas de même pour les mises à jour et les insertions depuis cette requête. En effet, comment garantir que le rôle sera effectivement saisi et, qui plus est, qu'il sera égal à la valeur 1 ? Il est absolument inconcevable que cette requête puisse donner lieu à la création d'un indépendant dont ses champs propres (DatePremierContrat et Commentaires) ne seraient pas définis, et pourtant dans l'état actuel, le risque est de 100%.

A cette question, il n'y a pas beaucoup de réponses, si ce n'est qu'en fixant la propriété Null Interdit du champ Role, le risque de voir apparaître des intervenants sans aucun rôle est éliminé. Il est aussi possible de définir la propriété ValideSi de telle sorte que le champ Role puisse admettre uniquement les valeurs 1 et 2. Mais ce n'est pas suffisant : ici seule la valeur 1 devrait être autorisée, mais il est impossible de placer une clause ValideSi sur un champ d'une requête. Dès lors, la seule façon d'éliminer les saisies involontaires est de les vérifier dans les formulaires avec du code VBA ou en définissant la propriété Valeur par défaut du contrôle associé au champ Role de telle sorte que la valeur soit forcée lors de l'enregistrement des données. Pour parfaire la sécurité, ce champ sera verrouillé et/ou rendu invisible.

De toute évidence, il faudra abandonner toute idée d'une éventuelle saisie directement dans la table ou dans les requêtes, et garder en tête que toutes modifications de la base depuis une application tierce représentent un risque majeur.

Outre ce "détail", un autre problème peut être rencontré :

Comment rendre le champ DateEmbauche obligatoire pour les salariés ?

Si la table n'avait contenu que des salariés, la réponse aurait été toute simple : définir la propriété Null Interdit à Oui. Ici, il n'est pas possible de réagir ainsi sans quoi la saisie des indépendants sera refusée puisqu'ils ne sont pas concernés par cette information. Pour rendre cette règle de gestion applicable, il faut se rendre dans les propriétés de la table tblIntervenant et définir sa propriété ValideSi.

L'expression suivante permet d'enregistrer les données dans la table si l'intervenant est indépendant (rôle=2) ou bien si la date d'embauche est saisie.

[dateembauche] IS NOT NULL OR  [Role]=2

Pour terminer sur la cohérence et l'intégrité, il reste le problème des doublons. A vrai dire, cette notion n'est pas l'exclusivité de la généralisation. Dans toute table client, contact, fournisseur, etc. il revient toujours la même crainte : les doublons.

Dans notre exemple, il est impossible qu'un enregistrement puisse posséder à la fois le rôle 1 et le rôle 2 étant donné que le champ Rôle ne peut prendre qu'une et une seule valeur. Cependant, rien n'empêche l'utilisateur de saisir un nouvel intervenant avec le même nom et la même adresse afin de lui associer un rôle différent.

Malheureusement, l'indexation sans doublons sur le tuple (nom, prenom, adresse) ne peut pas suffire étant donné que le même intervenant peut avoir déménagé entre les deux insertions. Là encore, ce sera à l'application, et notamment via du code VBA, d'alerter l'utilisateur en cas d'homonyme sans toutefois bloquer à coup sûr l'insertion. Il restera à la charge de l'utilisateur de distinguer s'il s'agit de la même personne ou non pour peu qu'on l'en ait averti.

Les associations affectant l'entité mère ne pose aucun problème de transposition vers le modèle relationnel. Il suffit de suivre les règles habituelles définies par la méthodologie MERISE.

Par exemple dans le cas de l'association Participer suivante :

Cela se traduira par la table tblParticiper :

En ce qui concerne les associations portées par les entités filles, et si on souhaite garder une totale cohérence des données, la tâche est un peu moins simple.

Dans un premier temps, il est nécessaire d'appliquer les mêmes règles que pour n'importe quelle association. Cela détermine d'une part s'il est nécessaire d'avoir recours à une table supplémentaire et d'autre part les positions et le nombre de clés étrangères.

Dans le cas où la clé étrangère est présente dans la table tblIntervenant, il faut appliquer la même méthode que pour la restriction qui a été appliquée au champ DateEmbauche un peu plus haut. Prenons l'exemple de l'association Appartenir :

Le champ Service de la table tblIntervenant devient une clé étrangère reliée au champ Id de la table tblService. En définissant sa propriété ValideSi à :

([Service] IS NULL AND [Role]=2) OR  ([Role]=1 AND [Service] IS NOT NULL)

Le moteur de base de données garantit que :

• Un indépendant ne peut pas appartenir à un service,
• Un salarié doit appartenir obligatoirement à un service,
• Un salarié appartient au maximum à un service.

En revanche, lorsque l'association conduit à une clé étrangère liée à l'identifiant de la table tblIntervenant, la contrainte à mettre en oeuvre ne peut pas se résumer à la simple définition de la propriété ValideSi. Il faut dans ce cas faire appel à la clause SQL CONSTRAINT. Si vous ne connaissez pas encore cette approche, je vous invite à consulter le document suivant : Tutoriel sur les contraintes avancées en VBA

L'exemple ci-dessus conduit à une nouvelle table tblContrat dont la structure est la suivante :

Afin de garantir que l'intervenant signataire du contrat est bien un indépendant, il est nécessaire de définir une contrainte sur le champ IDIntervenant de la table tblContrat.

ALTER TABLE tblContrat 
  ADD CONSTRAINT Independant 
  CHECK 
   (
    (
      SELECT Role 
      FROM tblIntervenant
      WHERE ID=tblContrat.IDIntervenant
    )=2
   )

La syntaxe VBA à exécuter est donc :

Sub Contrainte()
Dim strSQL As String
strSQL = "ALTER TABLE tblContrat ADD CONSTRAINT Independant " & _
            "CHECK ((SELECT Role FROM tblIntervenant WHERE ID=tblContrat.IDIntervenant)=2)"
Application.CurrentProject.Connection.Execute strSQL
End Sub

Que va-t-il se passer si, demain, la structure des sous-types change ? C'est à cette question que doit tenter de répondre cette section qui va aussi revenir dans les deux prochains chapitres.

Lorsque des modifications aux MCD font apparaître une entité fille supplémentaire, la répercussion au modèle relationnel se décompose en deux étapes.

• Ajout du nom de la nouvelle entité dans la plage admissible du champ Rôle
• Ajout des champs qui traduisent les attributs de la nouvelle entité dans la table tblInterevant

En cas de modification d'un sous-type existant, il faut considérer d'une part les atteintes aux attributs de l'entité et d'autre part son simple changement de nom. Dans la première hypothèse, toute opération à un attribut se traduit par similitude aux champs concernés dans la table :

• Création d'un nouvel attribut => création d'un nouveau champ
• Changement du nom d'un attribut => changement du nom du champ
• Changement du type d'un attribut => changement du type du champ
• Suppression d'un attribut => suppression du champ

Dans la seconde hypothèse, lorsque l'entité fille doit être renommée (car le nom ne reflète plus vraiment la réalité), il est alors nécessaire de renommer la valeur admissible par le champ Role, et de reporter cette modification à l'ensemble des enregistrements présents, via une requête UPDATE par exemple. Si les données existantes ne doivent pas être modifiées, cela signifie que la contrainte de gestion a été mal traduite au sein du MCD et qu'il ne s'agit pas de la modification de l'entité fille mais de la création d'une nouvelle entité fille.

Enfin, en cas de suppression d'une entité fille, il faut commencer par opérer à l'inverse de l'insertion :

• Suppression des champs concernant les attributs de l'entité supprimée
• Retrait de la valeur admissible dans la plage de valeurs du champ Role

Puis pour terminer, il est nécessaire de fixer le champ Role à NULL pour tous les enregistrements qui faisaient partie du sous-type. Si le modèle interdit la valeur NULL pour ce champ, cela signifie qu'il faut soit renoncer à la suppression de l'entité fille, soit supprimer les enregistrements.

Bien entendu, toute modification va aussi se répercuter aux requêtes et aux contraintes de manière à maintenir l'intégrité des données.

Je vous propose un exemple simple d'interface mettant en oeuvre la saisie d'un nouvel intervenant dans la base de données. Attention, il n'est pas question d'être exhaustif sur l'ensemble des possibilités (création, visualisation, modification, etc) mais de donner un aperçu simple de l'incidence du choix de transposition de l'héritage sur la construction de l'IHM.

Cet exemple est composé de 3 formulaires. Le premier est un formulaire indépendant. Il va servir de conteneur aux deux autres dont un sera dédié aux salariés, l'autre aux indépendants.

La zone de liste modifiable nommé cmbType a pour but de changer le sous-formulaire affiché dans le contrôle Sform.

Propriétés de la zone de liste :

• Type de contenu : Liste de valeurs
• Contenu : Salarié ;1 ;Indépendant ;2
• Colonne liée : 2
• Nombre de colonnes : 2
• Largeur des colonnes : 3 cm; 0 cm

Code associé à la zone de liste :

Private Sub cmbType_Change()
' Affiche le sous-formulaire en fonction
' de la valeur de la zone de liste
Select Case cmbType.Value
    Case 1:
        Me.sform.SourceObject = "frmSalarie"
    Case 2:
        Me.sform.SourceObject = "frmIndependant"
End Select
End Sub

Les deux sous-formulaires sont basés sur la table tblIntervenant et affichent les champs nécessaires.

FrmSalarie :

FrmIntdependant :

Pour que les formulaires soient réservés à la saisie, leur propriété Entrée Données est fixée à Oui. De plus, afin que chaque formulaire insère le bon intervenant, la zone de texte Role possède la valeur par défaut adéquate dans chaque sous-formulaire (1 pour frmSalarie et 2 pour frmIndependant). Cette zone est rendue invisible pour éviter toute modification de l'utilisateur.

Résultat :

Certains qualifieront cette solution de brouillon, d'autres l'utiliseront à toutes les sauces. En effet, il s'agit à priori de la solution de facilité. Même si, à première vue, elle s'avère la plus simple à mettre en oeuvre, les différents éléments exposés ci-dessus révèlent en fait qu'il est nécessaire d'avoir une certaine connaissance des mécanismes mis à disposition par Access afin de garantir l'intégrité des données qui, rappelons le, est un des points critiques de tout système d'informations. Destinée à des bases à faible volume, la généralisation offre une identification solide des enregistrements. Dans le cas d'un champ ID NuméroAuto, les salariés et les indépendants partagent la même numérotation et il n'y a pas de risque de conflits entre eux.

En termes d'évolutivité et de maintenance, Access offrant la possibilité de dupliquer des objets facilement, il est relativement simple et rapide de livrer une nouvelle version de la base de données prenant en charge une entité fille supplémentaire.

Mais attention, elle n'est pas adaptée lorsque les attributs spécifiques des entités filles sont trop nombreux, faute de quoi, le taux de remplissage n'est vraiment pas optimum.

Ici, il n'y a pas de perte de place résultant de l'absence d'information pour certains champs. Ils seront à priori tous remplis. En revanche dans le cas où une entité sur-type peut à la fois être de plusieurs sous-type (cas typique du contact qui peut être à la fois fournisseur et client), cette solution va conduire à la duplication des données communes aux deux tables. Dans ce genre d'héritage, nommé héritage avec totalité et symbolisé dans le MCD par la lettre T, la perte de volume peut devenir colossale si beaucoup d'éléments exercent plusieurs rôles. Dans une telle situation, la spécialisation totale ne sera pas pertinente, et ce sans compter la difficulté de maintenir les informations des tables cohérentes entre elles.

Malgré cet inconvénient, la réunion de l'ensemble des données permet des traitements très rapides sur les entités filles.

L'avantage indéniable de cette solution est d'offrir la totalité des informations au sein d'une même structure. Toutefois, il peut être utile de les regrouper pour offrir une vision globale des intervenants.

Dans ce cas, la requête suivante affiche la "généralisation" des deux tables.

(
SELECT
  ID,
  Nom,
  Prenom,
  Adresse,
  Ville,
  Cp,
  DateEmbauche,
  Grade,
  Service,
  NULL AS DatePremierContrat,
  NULL AS Commentaires
FROM
  tblSalarie
)
UNION
(
SELECT
  ID,
  Nom,
  Prenom,
  Adresse,
  Ville,
  Cp,
  NULL,
  NULL,
  NULL,
  DatePremierContrat,
  Commentaires
FROM
  tblIndependant
)

Du fait qu'il s'agisse d'une requête UNION, il y est impossible d'ajouter, supprimer ou mettre à jour les données. Elle est utilisable uniquement pour des présentations comme par exemple des états.

Si l'intégrité des données au sein des tables ne peut être remise en cause, les mécanismes mis en jeu dans la définition de la clé primaire ne respectent pas forcément la même logique que dans le modèle conceptuel des données. En effet, si nous reprenons le modèle conceptuel des données :

on s'aperçoit qu'à priori les Indépendants et les salariés possèdent la même nomenclature en ce qui concerne leur identifiant. Or, si on considère des champs de type NumAuto, dans le cas de deux tables distinctes, les champs ID de chacune d'elles vont avoir des valeurs en commun, à commencer par la première ID=1. Plusieurs solutions sont possibles pour résoudre ce problème.

Dans un premier temps, il est possible de considérer deux plages de numérotation pour les champs tblIntervenant.Id et tblSalarie.Id. Il est par exemple possible de définir la numérotation des intervenants sur la plage 1-999999 et celle des salariés sur la plage 1000000-1999999. L'ajout d'un troisième sous-type donnera lieu à l'ouverture d'une nouvelle plage. Bien entendu, cela oblige à se borner à un nombre maximal d'enregistrements à transiter dans la base de données. Dans le cadre d'un suivi RH sous Access, cette solution reste toutefois très pertinente au niveau du sens : 999999 salariés c'est déjà beaucoup !

La seconde possibilité consiste à dénuer de sens la clé primaire. Il s'agit là d'un débat qui agite bon nombre d'analystes et il n'y a pas lieu de le relancer ici. Toutefois, en limitant la clé primaire à sa définition d'identifiant unique au sein de sa table, le problème des doublons de numéro auto entre les deux tables ne se posent plus. Pourquoi ? Tout simplement parce qu'il n'y a plus lieu de les comparer entre chaque table. En contrepartie, cela oblige à créer son propre mécanisme de numérotation inter-table dans de nouveaux champs MatriculeSalarie et MatriculeIndependant, souvent avec du code VBA. Cette méthode est valable pour une base de données autonome qui ne sera jamais traitée par un autre applicatif mais peut s'avérer très vite critique dès que la base est attaquée par une application tierce (le VBA n'étant pas géré directement par le moteur Jet, la numérotation n'aura pas lieu).

Enfin, une dernière possibilité consiste à créer une table à un seul champ de type NuméroAuto et de transformer les ID des tables tblIndependant et tblSalarie en clés étrangères liées à ce nouveau champ.

Cependant, si cette solution paraît séduisante, elle possède plusieurs freins à ne pas oublier :

• Il est nécessaire de créer des contraintes dans les tables tblIntervenant et tblSalarie de telle sorte que le même ID ne puisse pas être utilisé dans les deux tables.
• Il est obligatoire de créer la ligne dans la table tblIDIntervenant avant de tenter d'insérer un nouvel indépendant ou un nouveau salarié.
• Lors de la suppression d'un salarié ou d'un indépendant, il est impératif de supprimer l'enregistrement correspondant dans la table tblIDIntervenant, sans quoi il est impossible de garantir la non-réutilisabilité d'un numéro abandonné. Etant donné le sens de la relation, il est impossible de mettre en place une suppression en cascade depuis les tables « filles ». Il faudra donc opérer le Delete directement sur la table tblIDIntervenant et la suppression se propagera dans dans la table tblSalarie ou tblIndependant concernée. Dans le cas où l'utilisation du mécanisme de suppression en cascade n'est pas souhaité, le recours à une transaction auprès du moteur de base de données semble un passage obligé.

Sub SuppressionID(strTable As String, intID As Long)
On Error GoTo err
    Dim oDB As DAO.Database
    
    Set oDB = CurrentDb
    
    'Démarre une nouvelle transaction
    DBEngine.BeginTrans
    'Supprime l'id dans la table strTable
    oDB.Execute "DELETE FROM " & strTable & _
                " WHERE " & BuildCriteria("ID", dbInteger, intID), dbFailOnError
                
    
    'Supprime l'id dans la table tblIDIntervenant
    oDB.Execute "DELETE FROM tblIDIntervenant" & _
                " WHERE " & BuildCriteria("ID", dbInteger, intID), dbFailOnError
    
    
    'Valide les traitements
    DBEngine.CommitTrans

fin:
    Exit Sub
    
err:
    'Annule les traitements en cas d'erreur
    DBEngine.Rollback

End Sub

Dans le cas de champs communs dans les deux tables, il peut être nécessaire d'interdire la saisie d'une même information dans chacune d'elles. Une nouvelle fois, l'utilisation de CONSTRAINT en SQL va permettre de définir une telle règle de gestion.

L'exemple suivant empêche la saisie d'un couple (Nom,Prenom) dans la table tblIndependant si celui-ci est déjà présent dans la table tblSalarie.

ALTER TABLE tblIndependant 
  ADD CONSTRAINT NomPrenom 
  CHECK 
    (
     (
      SELECT Count(*) 
      FROM tblSalarie 
      WHERE 
         tblSalarie.Nom=tblIndependant.Nom 
         AND 
         tblSalarie.Prenom=tblIndependant.Prenom
     )=0
    )

Bien entendu, pour une fiabilité parfaite, une syntaxe similaire doit être appliquée à la table tblSalarie.

ALTER TABLE tblSalarie
  ADD CONSTRAINT NomPrenom 
  CHECK 
    (
     (
      SELECT Count(*) 
      FROM tblIndependant
      WHERE 
         tblIndependant.Nom= tblSalarie.Nom 
         AND 
         tblIndependant.Prenom= tblSalarie.Prenom
     )=0
    )

Contrairement au cas précédent, les associations portées par les entités filles ne posent pas de difficulté.

D'après le schéma ci-dessus, seuls les indépendants peuvent participer à la signature d'un contrat. Les règles de gestion sont parfaitement respectées.

En revanche, l'association Participer liée à l'entité mère ne passe pas aussi simplement du MCD au modèle relationnel.

Le moyen le plus normalisé de faire participer les salariés et les intervenants à un projet consiste à dupliquer la relation.

Evidemment, ce genre de structure alourdit considérablement les requêtes chargées des statistiques sur les projets. Le recours à l'opérateur SQL UNION devient alors récurrent et les performances de la bases tendent à s'effondrer. Ainsi, par exemple, la liste des participants à un projet est obtenue par la requête suivante :

SELECT 
  ID,NOM,PRENOM 
  FROM tblSalarieParticiper 
    INNER JOIN tblSalarie 
     ON tblSalarie.ID=tblSalarieParticiper.IDSalarie
  WHERE IDProjet=2

UNION

SELECT 
  ID,NOM,PRENOM 
  FROM tblIndependantParticiper 
    INNER JOIN tblIndependant 
     ON tblIndependant.ID=tblIndependantParticiper.IDIndependant
  WHERE IDProjet=2

En fonction des performances il peut être utile de recourir une structure un peu moins évidente :

Tout le sérieux du modèle ci-dessus repose sur la mise en place des contraintes. Sans aucune contrainte au niveau de la table ou des champs, il n'est pas viable dans une base de données attaquée depuis une application externe. Voici la liste des éléments à ne pas oublier :

• IDParticipation doit être une clé primaire, interdisant à ce titre les valeurs NULL. Le choix d'un type NuméroAuto est plus que justifié du fait que cette donnée ne représente absolument rien dans le domaine de gestion.
• Le champ IDProjet ne peut pas admettre de valeur NULL.
• Le couple (IDSalarie, IdIndependant) ne peut être égal au couple (NULL, NULL).
• Le couple (IDProjet,IDSalarie) ne peut admettre de doublon.
• Le couple (IDProjet,IDIndependant) ne peut, lui non plus, admettre de doublon.
• Le couple (IDSalarie,IdIndependant) ne peut avoir ses deux valeurs affectées.

Les restrictions relatives aux doublons des couples interdisant qu'un salarié ou un indépendant soit affecté plusieurs fois au même projet peuvent être décrites à l'aide de deux index sur la table tblParticiper2 :

• Index 1 : ProjetSalarie(IdProjet,IDSalarie), Unique : Oui, Ignorer Null : Oui
• Index 2: ProjetIndependant(IdProjet,IDIndependant), Unique : Oui, Ignorer Null : Oui

Pour empêcher les couples NULL et la saisie simultanée d'un salarié et d'un indépendant, la propriété ValideSi de la table est particulièrement adaptée :

NOT (([IDSalarie] IS NULL AND [IDIndependant] IS NULL) OR ([IDSalarie] IS NOT NULL AND [IDIndependant] IS NOT NULL))

Etant donné que chaque table regroupe à la fois les données du sur-type et des sous-types, l'ajout d'une nouvelle entité fille se traduit par la création d'une nouvelle table. Comme les autres, elle reprendra aussi les caractéristiques de l'entité mère.

En ce qui concerne la modification d'une entité fille, là aussi le principe est très simple : les modifications se traduisent à l'identique dans la table correspondante.

En fait, tout paraît idéal tant qu'il n'est pas question de supprimer une entité fille. En effet, la suppression d'une d'entre-elles se traduisant par la suppression de l'entité correspondante, il va y avoir une perte irrémédiable des informations provenant de l'entité mère puisque celles-ci ne sont pas distinguées. Dans notre exemple, si nous considèrons que le sous-type Indépendant doit être supprimé, nous n'aurons plus aucune référence à l'intervenant n°4, Mr NORD, dans la base de données (y compris dans ses participations aux projets... adieu les statistiques sur le nombre moyen d'intervenants par projet, du projet le plus gourmand en ressources humaines, etc.)

L'interface est très semblable à celle mise en place pour la généralisation à la différence près que cette fois-ci, chaque sous formulaire est basé sur sa propre table. Il n'y a plus besoin de définir une valeur par défaut et de cacher un champ.

Le code de la zone de liste cmbType reste inchangé :

Private Sub cmbType_Change()
' Affiche le sous-formulaire en fonction
' de la valeur de la zone de liste
Select Case cmbType.Value
    Case 1:
        Me.sform.SourceObject = "frmSalarie"
    Case 2:
        Me.sform.SourceObject = "frmIndependant"
End Select
End Sub

Résultat :

La spécialisation totale n'est guère plus compliquée que la généralisation vue précédemment. Toutefois, pour être exempte de tout bug, elle demande, à mon avis, un peu plus de rigueur, notamment au niveau des associations liées à l'entité mère. Elle est difficilement compatible avec un héritage T (avec totalité) et de plus, les performances sont directement fonction des entités mises en jeu dans les calculs. Si la majorité des traitements est effectuée sur l'entité mère, les performances ne seront pas au rendez-vous du fait de l'appel récurrent à l'opérateur UNION. En revanche si les sous-entités ne sont pas mises en commun lors des requêtes, les performances seront alors inégalables.

Du fait de la présence d'un champ supplémentaire dans les tables filles, cette solution occupe un peu plus d'espace disque que la précédente. Toutefois, il faut relativiser, une donnée de type Numérique (Entier Long) occupe seulement 4 octets. Sachant que les données de l'entité mère sont directement accessibles depuis la table mère et ce quelque soit le sous-type, le gain de performance viendra largement masquer la perte minime d'octets de stockage.

Voici quelques requêtes permettant d'offrir une structure faisant abstraction de celle de l'héritage :

rqtSalarie :

SELECT 
  tblIntervenant.ID, 
  tblIntervenant.Nom, 
  tblIntervenant.Prenom, 
  tblIntervenant.Adresse, 
  tblIntervenant.Ville, 
  tblIntervenant.CP, 
  tblSalarie.DateEmbauche, 
  tblSalarie.Service, 
  tblSalarie.Grade
FROM 
  tblIntervenant 
   INNER JOIN tblSalarie 
   ON tblIntervenant.ID = tblSalarie.IDIntervenant;

rqtIndependant :

SELECT 
  tblIntervenant.ID, 
  tblIntervenant.Nom, 
  tblIntervenant.Prenom, 
  tblIntervenant.Adresse, 
  tblIntervenant.Ville, 
  tblIntervenant.CP, 
  tblIndependant.DatePremierContrat, 
  tblIndependant.Commentaires
FROM 
  tblIntervenant 
    INNER JOIN tblIndependant 
    ON tblIntervenant.ID = tblIndependant.IDIntervenant;

rqtIntervenant :

SELECT 
  tblIntervenant.ID, 
  tblIntervenant.Nom, 
  tblIntervenant.Prenom, 
  tblIntervenant.Adresse, 
  tblIntervenant.Ville, 
  tblIntervenant.CP, 
  tblSalarie.DateEmbauche, 
  tblSalarie.Service, 
  tblSalarie.Grade, 
  tblIndependant.DatePremierContrat, 
  tblIndependant.Commentaires
FROM 
  (
     tblIntervenant 
       LEFT JOIN tblIndependant 
       ON tblIntervenant.ID = tblIndependant.IDIntervenant
  )
     LEFT JOIN tblSalarie 
     ON tblIntervenant.ID = tblSalarie.IDIntervenant;

rqtRoleIndependant :

La requête suivante retourne les données de la table mère ainsi que le "rôle" de chaque Intervenant.

SELECT 
  tblIntervenant.ID, 
  tblIntervenant.Nom, 
  tblIntervenant.Prenom, 
  tblIntervenant.Adresse, 
  tblIntervenant.Ville, 
  tblIntervenant.CP,
  SWITCH
      (
         tblSalarie.IdIntervenant IS NOT NULL     ,"Salarié,
         tblIndependant.IdIntervenant IS NOT NULL ,"Indépendant" 

      )
FROM 
  (
    tblIntervenant 
      LEFT JOIN tblIndependant 
      ON tblIntervenant.ID = tblIndependant.IDIntervenant
  ) 
    LEFT JOIN tblSalarie 
    ON tblIntervenant.ID = tblSalarie.IDIntervenant;

Je profite de ce passage abordant les requêtes pour ajouter un mot sur l'opérateur SQL NOT IN qui permet de tester si un enregistrement est présent dans une autre table. Dans bien des cas, le recours à une jointure externe (Right ou Left) avec un test de valeur NULL sur la clé étrangère est bien plus performant.

Exemple pour les intervenants qui ne sont pas des indépendants (encore que dans cet exemple, il serait plus judicieux d'interroger la table tblSalarie)

rqtNonIndependant :

SELECT 
  tblIntervenant.ID, 
  tblIntervenant.Nom, 
  tblIntervenant.Prenom, 
  tblIntervenant.Adresse, 
  tblIntervenant.Ville, 
  tblIntervenant.CP
FROM 
  tblIntervenant 
  LEFT JOIN tblIndependant 
  ON tblIntervenant.ID = tblIndependant.IDIntervenant
WHERE 
  tblIndependant.IDIntervenant IS NULL

Inutile de cacher que cette solution est celle qui garantit le mieux l'intégrité des données. En effet, aucun problème de redondance de l'information entre les tables, pas de valeurs NULL, pas de valeur à renseigner pour affecter explicitement un rôle, etc. De plus, le mécanisme d'intégrité référentielle entre la table mère et les sous-tables est mis en place facilement contrairement aux contraintes SQL des précédentes solutions.

Soit parce que le formulaire de saisie est indépendant ou soit parce que la création d'enregistrement est automatisée, il est nécessaire de recourir à du code Visual Basic pour insérer les données dans la base de données. Ici, quelques pièges sont à éviter pour rendre le processus stable, et plus particulièrement lorsque des clés primaires de type NuméroAuto sont utilisées.

Prenons le cas de l'ajout d'un nouveau salarié. La première étape consiste naturellement à créer l'enregistrement dans la table tblIntervenant où seront stockées les informations relatives au nom, prénom, adresse, etc. La seconde étape procède à la création de l'enregistrement lié dans la table tblSalarié, cependant, celle-ci ne peut avoir lieu qu'à l'unique condition que nous ayons pris soin de récupérer la valeur de l'identifiant qui a été affecté à l'intervenant précédemment. Le plus sûr est d'utiliser des Recordsets DAO qui permettent de récupérer la valeur d'un NuméroAuto entre les appels AddNew et Update. (Tutoriel sur les Recordset DAO)

Dim oDb As DAO.Database
Dim oRstSalarie As DAO.Recordset
Dim oRstIntervenant As DAO.Recordset
Dim intID As Integer
Set oDb = CurrentDb

'Ouvre le recordset sur la table tblIntervenant
Set oRstIntervenant = oDb.OpenRecordset("tblIntervenant")
'Ajoute l'intervenant
With oRstIntervenant
    .AddNew
    .Fields("Nom") = "LUCIOLLE"
    .Fields("Prenom") = "Marc"
    
    'Récupère l'identifiant
    intID = .Fields("ID")
    
    .Update
    
End With


'Ouvre le recordset sur la table tblSalarie
Set oRstSalarie = oDb.OpenRecordset("tblSalarie")
'Ajoute les données propres au salarié
With oRstSalarie
    .AddNew
    .Fields("IDIntervenant") = intID
    .Fields("DateEmbauche") = Now()
    .Update
    
End With

Avec le code ci-dessus, le lien entre la table mère et la table fille est garanti contrairement à une requête qui aurait recherché la plus grande valeur du NuméroAuto en espérant qu'entre-temps personne n'ait ajouté un autre enregistrement.

Pour parfaire le tout, il peut être judicieux de recourir à une transaction afin de pouvoir annuler les modifications apportées à la table tblIntervenant dans le cas où l'insertion dans la table tblSalarie soit impossible (erreur, non validité des contraintes, etc.)

Sub CreationSalarie()
On Error GoTo err
    Dim oDb As DAO.Database
    Dim oRstSalarie As DAO.Recordset
    Dim oRstIntervenant As DAO.Recordset
    Dim intID As Integer
    
    'Ouvre une nouvelle transaction
    DBEngine.BeginTrans
    
    Set oDb = CurrentDb
    
    'Ouvre le recordset sur la table tblIntervenant
    Set oRstIntervenant = oDb.OpenRecordset("tblIntervenant")
    'Ajoute l'intervenant
    With oRstIntervenant
        .AddNew
        .Fields("Nom") = "LUCIOLLE"
        .Fields("Prenom") = "Marc"
        
        'Récupère l'identifiant
        intID = .Fields("ID")
        
        .Update
        
    End With
    
    
    'Ouvre le recordset sur la table tblSalarie
    Set oRstSalarie = oDb.OpenRecordset("tblSalarie")
    'Ajoute les données propres au salarié
    With oRstSalarie
        .AddNew
        .Fields("IDIntervenant") = intID
        .Fields("DateEmbauche") = Now()
        .Update
        
    End With
    
    'Valide la transaction
    DBEngine.CommitTrans
fin:
    
    Exit Sub
 
err:
    'Invalide les transactions
    DBEngine.Rollback
    'Avertit
    MsgBox "Erreur, modification annulée", vbCritical
    Resume fin:
End Sub

A noter que dans l'exemple ci-dessus, la gestion d'erreur a volontairement été épurée pour des raisons de lisibilité. Inutile de préciser qu'elle doit être enrichie suivant les habitudes de chacun.

A chaque entité correspondant une table, les associations sont donc transférées facilement du MCD au modèle relationnel : les associations de l'entité mère affectent la table mère, les associations des entités filles affectent les tables filles correspondantes.

Cas de l'association Participer :

Dans la base de données :

La requête suivante permet de déterminer le nombre de salariés et d'indépendants ayant participé aux projets.

SELECT 
  Count(tblParticiper.IdIntervenant) AS NBPARTICIPANTS,
  Sum(Not IsNull(tblSalarie.IdIntervenant))*-1 AS NBSALARIES,
  Sum(Not IsNull(tblIndependant.IdIntervenant))*-1 AS NBINDEPENDANTS

FROM 
  (
    tblParticiper 
    LEFT JOIN tblSalarie 
    ON tblParticiper.IdIntervenant = tblSalarie.IDIntervenant
  ) 
  LEFT JOIN tblIndependant 
  ON tblParticiper.IdIntervenant = tblIndependant.IDIntervenant

GROUP BY tblParticiper.IdProjet

Cas de l'association Signer :

En appliquant le mécanisme d'intégrité référentielle à la relation, il n'y a aucun risque de faire signer un intervenant qui ne serait pas indépendant.

De toute évidence, il s'agit du modèle par excellence en ce qui concerne les évolutions futures de la base de données. Qu'il s'agisse de suppressions, de modifications, ou de créations de nouvelles entités, le modèle relationnel se manipule par analogie sur les tables. Le gros avantage : en cas de suppression d'un sous-type, les occurrences qui y étaient présentes apparaissent toujours dans la table mère. La globalité de la base n'est donc pas impactée par les changements au niveau des sous-types. Ainsi, par exemple, les statistiques relatives au coût des projets en ressources humaines restent cohérentes, ce qui n'est pas le cas lorsque la spécialisation est totale.

Comme pour les précédents chapitres, l'interface d'ajout d'un nouvel intervenant est composée de trois formulaires : frmIntervenant, frmIndependant et frmSalarie. Les deux derniers sont utilisés en tant que sous-formulaires.

Le formulaire frmIntervenant est basé sur la table tblIntervenant et comporte tous les champs de la table. La zone de liste modifiable permet de sélectionner le sous-formulaire à afficher, son code reste identique au deux autres versions.

Private Sub cmbType_Change()
' Affiche le sous-formulaire en fonction
' de la valeur de la zone de liste
Select Case cmbType.Value
    Case 1:
        Me.sform.SourceObject = "frmSalarie"
    Case 2:
        Me.sform.SourceObject = "frmIndependant"
End Select
End Sub

Le cadre de sous-formulaire utilise une liaison champ père / champ fils basée respectivement sur les champs ID et IDIntervenant. Le but étant de synchroniser les données affichées depuis la table mère avec celles des tables filles.

Les zones de texte correspondant aux champs ID et IDIntervenant permettent de s'assurer de la bonne synchronisation des sous-formulaires. Elles seront masquées une fois les tests terminés.

Résultat :

Bien qu'elle complique les requêtes et la création de l'interface de l'application, cette méthode semble idéale dans bien des cas, notamment lorsque les entités filles et mère participent à des associations, et / ou que le nombre d'attributs varie d'une entité à l'autre.

De plus, et cela va être détaillé dans le prochain chapitre, elle permet de mettre en place les différents types d'héritage avec facilité.

Merise distingue trois types d'héritage au niveau des entités, ceux-ci étant communément recensés avec des lettres :

• T : Héritage avec totalité. Une même occurrence peut appartenir à plusieurs entités filles. Dans l'exemple de ce document, il s'agirait de considérer qu'un Intervenant ait été par le passé Indépendant avant de devenir salarié mais que l'on souhaite conserver l'ensemble des données.
• X : Héritage avec exclusion. Une occurrence peut appartenir à une entité fille au maximum ou à aucune. Ici, il s'agirait d'admettre par exemple qu'un intervenant puisse être stagiaire, sans toutefois que l'on ait besoin de stocker des informations spécifiques à ce statut.
• XT : Héritage avec exclusion et totalité. Une occurrence appartient à une et une seule entité fille. C'est cet héritage que nous avons utilisé jusque-là : un intervenant est soit un salarié, soit un indépendant.

La méthode de spécialisation totale n'étant pas compatible avec les héritages autres que XT (étant donné qu'une occurrence est stockée dans une et unique table), elle sera ignorée dans ce chapitre.

Dans le cas de la généralisation, pour qu'un intervenant puisse avoir plusieurs rôles, il suffit de créer une table supplémentaire tblIntervenantRole de telle sorte à pouvoir stocker des valeurs multiples en lieu et place du champ Role. A ce titre, bien qu'Access 2007 offre la possibilité du stockage de valeurs multiples dans un seul champ, je vous déconseille d'utiliser cette fonctionnalité afin d'éviter toute dénormalisation de la structure de la base de données.

Si l'intégrité des données au niveau des sous-types est bien conservée, la cohérence des données stockées dans les champs DateEmbauche, Grade, Service, DatePremierContrat, Commentaire n'est pas assurée. En effet rien ne garantit par exemple que la date d'embauche doit être saisie si l'intervenant est salarié. Pour cela, il faudrait définir des contraintes au niveau de la table tblIntervenant comme par exemple ci-dessous :

ALTER 
  TABLE tblIntervenant 
  ADD CONSTRAINT CstDateEmbauche 
  CHECK 
   (
    (
     (
      SELECT Count(*) 
      FROM tblIntervenantRole 
      WHERE IDRole=1 AND IDIntervenant=tblIntervenant.Id
     )=1 
    AND 
     DateEmbauche IS NOT NULL
    ) 
    OR 
    (
     (
      SELECT Count(*) 
      FROM tblIntervenantRole 
      WHERE IDRole=1 AND IDIntervenant=tblIntervenant.Id
     )=0 
    AND 
     DateEmbauche IS  NULL
    )
   )

Mais pire encore, l'absence de trigger (déclencheur) sous Access impose de dupliquer les contraintes sur la table tblIntervenantRole.

Il ne fait aucun doute que la généralisation au sein de la base de données n'est pas faite pour l'héritage avec totalité. Pour l'héritage XT, c'est beaucoup plus simple puisqu'il suffit de reprendre le premier chapitre et d'autoriser les valeurs NULL au champ rôle. Toutefois, dans le cas où beaucoup d'occurrences ne sont d'aucun sous-type, la quantité de champs laissés à vide dans la table tblIntervenant va vite rendre cette solution inadaptée. C'est pourquoi, dès que l'héritage est autre que XT il est conseillé d'utiliser la troisième méthode de conversion, à savoir celle que nous avons nommé ici : "spécialisation sur le modèle du MCD"

Dans un tel schéma, la spécificité de chaque type d'héritage résulte en une définition de contraintes appliquées à chaque table fille, avec toutefois un bémol : il n'est pas possible de mettre en place une contrainte dans la base de données définissant qu'un enregistrement qui vient d'être créé appartienne à au moins un sous-type.

La raison est simple : à la création, l'accès est réalisé sur la table tblIntervenant. Si, dans la foulée, l'enregistrement dans la table fille est créé, il est évidemment possible de tester les contraintes de cette table. En revanche, si aucune table fille n'est affectée, aucune contrainte ne pourra être vérifiée.

Dans le cas d'un héritage XT qui signifie qu'un Intervenant doit être salarié ou indépendant mais pas les deux, les contraintes sont les suivantes :

Contraintes sur tblSalarie :

ALTER TABLE 
   tblSalarie 
   ADD CONSTRAINT Unicite 
   CHECK 
     (
      (
        SELECT Count(*) 
        FROM tblSalarie T1 
        WHERE T1.IDIntervenant=tblSalarie.IDIntervenant
      )
      +
      (
        SELECT Count(*) 
        FROM tblIndependant T2 
        WHERE T2.IDIntervenant=tblSalarie.IDIntervenant
      )
      =1
     )

Contraintes sur tblIndependant.IDIntervenant :

ALTER TABLE 
   tblIndependant
   ADD CONSTRAINT Unicite 
   CHECK 
     (
      (
        SELECT Count(*) 
        FROM tblSalarie T1 
        WHERE T1.IDIntervenant=tblIndependant.IDIntervenant
      )
      +
      (
        SELECT Count(*) 
        FROM tblIndependant T2 
        WHERE T2.IDIntervenant=tblIndependant.IDIntervenant
      )
      =1
     )

Dans le cas d'un héritage avec totalité, il suffit de faire varier la valeur à droite de l'opérateur "=" pour définir le nombre maximal de sous-type auquel un enregistrement peut appartenir. Il est même possible en adaptant le SQL de définir des héritages répondant à la clause suivante :

Le contact peut être un salarié, un fournisseur, un directeur ou client. Sachant qu'un salarié peut être client ou directeur mais pas fournisseur, etc