Discussion :

[lucho27]

Bonjour,

ayant des lacunes en conception de base de données, je cherche un éclairage pour résoudre une question simple d'intégrité référentielle :

J'ai 3 tables : "salarié", "entreprise", "localisation"
Chaque "salarié" a 1 "localisation"
Chaque "entreprise" a 1 "localisation"

Pour l'instant pour faire la relation entre "salarié" et "localisation", j'ai copié la clé primaire de "localisation" dans "salarié".
Idem pour "entreprise" et "localisation".

Mais je suis embêté pour la gestion des suppressions. J'aimerais que la localisation d'un salarié soit supprimée lorsque je supprime le salarié.
Idem pour les entreprises.

Pour faire bien, il me faudrait corriger mes lacunes en reprenant les fondamentaux Merise par exemple mais faute de temps...
Pourriez-vous éclairer ma lanterne ?

Merci du coup de main
Luc

[punkoff]

Bonjour,
Pour l'instant pour faire la relation entre "salarié" et "localisation", j'ai copié la clé primaire de "localisation" dans "salarié".
Idem pour "entreprise" et "localisation".

Mais je suis embêté pour la gestion des suppressions. J'aimerai que la localisation d'un salarié soit supprimée lorsque je supprime le salarié.
Idem pour les entreprises.

Bonjour,

Du fait de votre conception (qui me semble bonne) :
salarie-(1,1)----se situ-----(0,n)-location
entreprise-(1,1)-----se situ----(0,n)-location


Une même location peut-être partagée par une entreprise et un salarié.


Que voulez-vous faire dans ce cas là ?

edit : quel est votre SGBD ?

[lucho27]

Bonjour,

je me rends compte que mon descriptif était confus.

Un "salarié" peut avoir 1 ou aucune "localisation".
Du coup dans ma table "salarié", j'ai le champ id_localisation qui vaut NULL ou bien une valeur.
Je cherche à comprendre :
- si c'est la manière correcte d'implémenter ce type de relation.
- comment on peut gérer la suppression en cascade sans trigger

J'ai la même chose à gérer pour ma table "entreprise", sauf qu'une "entreprise" a obligatoirement 1 et une seule "localisation".

Une "localisation" n'est jamais partagée par une "entreprise" et un "salarié".
Une "localisation" donnée appartient soit à un "salarié" (et un seul), soit à "une entreprise".

J'aurais donc décrit mon contexte de cette manière:
salarié(0,1) ---se situ--- (0,1)localisation
entreprise(0,1)---se situ--- (1,1)localisation.

N'hésitez pas à me dire si je me trompe.

J'ai d'autres tables qui ont aussi ce type de relation avec la table "localisation".

J'espère que j'ai exprimé mon contexte plus correctement.

J'utilise Mysql.

merci de votre aide

edit : je vous livre les 2 solutions que j'entrevoie :

1/ rester sur ce que j'ai décrit à savoir, mettre la clé primaire de "localisation" dans la table "salarié" et dans la table "entreprise". Mais lorsque je veux supprimer un "salarié" il faut que je fasse d'abord un SELECT pour récuperer l'id_localisation pour pouvoir supprimer la localisation. Donc à cause de ce SELECT ça me fait un accès supplémentaire en bdd à chaque DELETE.

2/ je fais l'inverse. Je mets les clés primaires de "salarié" et de "entreprise" dans la table "localisation" avec contrainte d'intégrité (en ajoutant CHECK dans la contrainte pour dire qu'une des 2 clés ne doit pas être NULL). Du coup la "localisation" sera automatiquement supprimée à chaque fois que je supprime un "salarié" ou une "entreprise". Problème, vu que j'ai d'autres tables qui sont en relation avec la table "localisation", je vais me retrouver avec peut être beaucoup de clés étrangères potentiellement NULL. Je ne sais pas si c'est propre...

je cherche la solution la plus propre et pérenne...

[CinePhil]

Un "salarié" peut avoir 1 ou aucune "localisation".

MCD :
salarie -0,1----avoir----0,n- localisation

Du coup dans ma table "salarié", j'ai le champ id_localisation qui vaut NULL ou bien une valeur.
Je cherche à comprendre :
- si c'est la manière correcte d'implémenter ce type de relation.

En toute rigueur : non !
Voir mon blog pour plus d'explication (cas 03).

- comment on peut gérer la suppression en cascade sans trigger

Tout bon SGBD dispose de l'instruction ON DELETE CASCADE à ajouter à la création d'une FOREIGN KEY.

J'ai la même chose à gérer pour ma table "entreprise", sauf qu'une "entreprise" a obligatoirement 1 et une seule "localisation".

MCD :
entreprise -1,1----avoir----0,n- localisation

Une "localisation" n'est jamais partagée par une "entreprise" et un "salarié".
Une "localisation" donnée appartient soit à un "salarié" (et un seul), soit à "une entreprise".

Alors mes MCD changent !

J'aurais donc décrit mon contexte de cette manière:
salarié(0,1) ---se situ--- (0,1)localisation
entreprise(0,1)---se situ--- (1,1)localisation.

Non !
salarie -0,1----situer----0,1- localisation
entreprise -1,1----situer----0,1---|

Et il faut en plus une contrainte d'exclusion entre les deux associations qui se gérera par un trigger vu que MySQL qui ne connaît toujours pas la contrainte CHECK ! ).

En vertu de ce que j'ai écrit dans mon article, les tables issues de ce MCD sont les suivantes :
salarie (slr_id, slr_nom...)
localisation (lcl_id, lcl_nom...)
entreprise (ent_id, ent_id_localisation, ent_nom...)
slr_situer_lcl (ssl_id_salarie, ssl_id_localisation)

[lucho27]

Bonjour,
merci pour vos éclairages !!

Mais je ne vois pas bien comment utiliser la clause "ON DELETE CASCADE" dans mon contexte...
Cette clause n'est utilisable que dans une table "fille" (donc "localisation" pour moi) non ?

En reprenant votre descriptif :
salarie (slr_id, slr_nom...)
localisation (lcl_id, lcl_nom...)
entreprise (ent_id, ent_id_localisation, ent_nom...)
slr_situer_lcl (ssl_id_salarie, ssl_id_localisation)

Comment la suppression d'une "entreprise" entrainera celle de sa "localisation"? Si je mets un "ON DELETE CASCADE" sur la colonne "entreprise.ent_id_localisation", c'est la suppression d'une "localisation" qui entrainera celle de "l'entreprise", non ? Or c'est l'inverse que je cherche à faire.

Pour le "salarie". Si je mets un "ON DELETE CASCADE" sur "slr_situer_lcl.ssl_id_salarie", la suppression d'un "salarie" entrainera bien la suppression de l'enregistrement de "slr_situer_lcl" mais pas la "localisation". Je me trompe encore ?

Du coup je ne vois pas comment faire pour gérer les suppressions autrement qu'à travers les 2 solutions décrites dans mon premier post ou bien en passant pas des triggers, chose malheureusement impossible sur mon hébergement actuel.

j'espère vous lire. merci de votre temps.

Luc

[punkoff]

Bonjour,

un peu de lecture :
http://dev.mysql.com/doc/refman/5.5/...nstraints.html

Et effectivement ca sera le delete de la table mère qui va delete les enregistremetns des tables filles.

[CinePhil]

Mais je ne vois pas bien comment utiliser la clause "ON DELETE CASCADE" dans mon contexte...
Cette clause n'est utilisable que dans une table "fille" (donc "localisation" pour moi) non ?

En reprenant votre descriptif :
salarie (slr_id, slr_nom...)
localisation (lcl_id, lcl_nom...)
entreprise (ent_id, ent_id_localisation, ent_nom...)
slr_situer_lcl (ssl_id_salarie, ssl_id_localisation)

Comment la suppression d'une "entreprise" entrainera celle de sa "localisation"? Si je mets un "ON DELETE CASCADE" sur la colonne "entreprise.ent_id_localisation", c'est la suppression d'une "localisation" qui entrainera celle de "l'entreprise", non ?

Oui, dans le modèle décrit, c'est la suppression d'une localisation qui entraînera celle de l'entreprise.

Or c'est l'inverse que je cherche à faire.

Et bien il faut alors un trigger.
Ceci dit, je sui sun peu étonné qu'une localisation n'appartienne qu'à une seule entreprise ou un seul salarié.
Que contient la localisation ?

Pour le "salarie". Si je mets un "ON DELETE CASCADE" sur "slr_situer_lcl.ssl_id_salarie", la suppression d'un "salarie" entrainera bien la suppression de l'enregistrement de "slr_situer_lcl" mais pas la "localisation". Je me trompe encore ?

C'est bien ça oui.

Du coup je ne vois pas comment faire pour gérer les suppressions autrement qu'à travers les 2 solutions décrites dans mon premier post ou bien en passant pas des triggers, chose malheureusement impossible sur mon hébergement actuel.

À faire par le programme qui gère les données alors. Il doit d'abord supprimer les lignes relatives au salarié dans les autres tables puis la ligne dans la table salarié.

Mais attention quand même de ne pas supprimer à tout va !
Ne pas supprimer des lignes comptables relatives à un salarié par exemple ! Et je crois qu'une entreprise est tenue de conserver les informations sur ses salariés à vie alors la suppression d'un salarié n'est peut-être pas une bonne idée non plus !

Tu peux en dire un peu plus sur le besoin concret ?

[lucho27]

Bonjour et merci pour vos réponses,

Les tables que j'avais prises étaient des exemples sur lesquels je fais des tests.
Exemples surement mal choisis car en effet la relation sémantique salariés entreprises ajoutait de la confusion.

Bon n'ayant pas de recours possible aux triggers, je gérerais tout ça dans mon code en faisant donc les SELECT puis DELETE dans les filles avant le DELETE dans ma table mère.
Je voulais m'assurer que je ne passais pas à coté d'une utilisation ah doc du mécanisme "ON DELETE CASCADE" pour ce contexte.

Merci Cinephil pour toutes ces précisions et confirmations.

Luc