Discussion :

[sylvain257]

Hello,

Je voudrais savoir s'il est possible de créer une clé étrangère conditionnelle.

Tous les pays du monde n'ont pas la même structure administrative. Certains ont 5 échelons comme la France, d'autres 2 comme Andorre ou encore d'autres qui ont plusieurs structures administratives pour le même pays (Suisse).

J'ai donc 5 tables avec les différents échelons : pour faire court, l'échelon 2 sont les pays, l'échelon 4 les régions, l'échelon 6 les départements, l'échelon 7 sont les paroisses andorranes, et l'échelon 8 les communes.

Il y a des cardinalités évidentes entre les différents échelons et les pays (existence pays). Seulement, il n'existe pas d'échelon supérieur aux cantons luxembourgeois (échelon 6) hormis le pays lui-même (échelon 2), du coup si je suis amené à créer un canton (ce qui ne devrait pas arriver), je ne souhaite pas que la clé de contrainte étrangère (existence échelon 4) s'applique pour le Luxembourg.

Ma question est donc : est il possible de créer une clé de contrainte étrangère qui ne s'applique qu'en fonction de la valeur d'un champ ?

Je vous remercie pour vos retours

Sylvain

[mathieu]

du point du vue théorique, je pense qu'il devrait y avoir une table "structure_administrative" et des "sous-tables" pour chaque structure.
par exemple si on simplifie avec la France qui a des régions et des cantons et le Luxembourg qui a des cantons, ça donnerai quelque chose comme cela :

structure_administrative

• id_struct_admin (clé primaire)
• nom_struct_admin
• id_representant (clé étrangère vers une table qui liste des personnes)
• ...

canton_luxembourg

• id_canton_luxembourg (clé primaire)
• nom_canton_luxembourg
• id_struct_admin (clé étrangère)

region_france

• id_region_france (clé primaire)
• nom_region_france
• id_struct_admin (clé étrangère)

canton_france

• id_canton_france (clé primaire)
• nom_canton_france
• id_struct_admin (clé étrangère)
• id_region_france (clé étrangère)

si les tables particulières se ressemblent beaucoup, on décide souvent de dénormaliser pour gagner du temps et dans ce cas on peut faire une seule table "structure_administrative" avec un champ "identifiant structure parente" qui serait facultatif.
et comme on sort des théories de la normalisation, il n'existe peut-être pas de façon de gérer cela dans la définition de la structure. et donc ça sera le code qui devra vérifier la cohérence de ces données.

[escartefigue]

Bonjour,

il faut donc définir les types de collectivités et pour chaque type, associer le ou les pays pour lesquels ce type est applicable
Ensuite, on crée une association reflexive de la collectivité sur elle-même pour créer la hiérarchie.
Enfin, on vérifie par une contrainte d'inclusion que le pays de la collectivité est l'un des pays pour lesquels le type de collectivité est applicable.

Ce qui donne le modèle conceptuel suivant (créé ici avec Looping après avoir choisi le SGBD Postrgre) :




À partir de là, en un clic, on obtient les tables suivantes :




Et le script DDL généré par Looping ici décliné pour Postgre :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE TABLE YC_type_coll(
   YC_ident SERIAL,
   YC_code CHAR(4) NOT NULL,
   YC_libelle VARCHAR(100) NOT NULL,
   PRIMARY KEY(YC_ident),
   UNIQUE(YC_code)
);
 
CREATE TABLE PY_pays(
   PY_ident SERIAL,
   PY_code CHAR(3) NOT NULL,
   PY_libelle VARCHAR(100) NOT NULL,
   PRIMARY KEY(PY_ident),
   UNIQUE(PY_code)
);
 
CREATE TABLE CO_collectivite(
   CO_ident SERIAL,
   CO_nom VARCHAR(50) NOT NULL,
   CO_ident_parent INTEGER,
   PY_ident INTEGER NOT NULL,
   YC_ident INTEGER NOT NULL,
   PRIMARY KEY(CO_ident),
   FOREIGN KEY(CO_ident_parent) REFERENCES CO_collectivite(CO_ident),
   FOREIGN KEY(PY_ident) REFERENCES PY_pays(PY_ident),
   FOREIGN KEY(YC_ident) REFERENCES YC_type_coll(YC_ident)
);
 
CREATE TABLE YP_applicable(
   YC_ident INTEGER,
   PY_ident INTEGER,
   PRIMARY KEY(YC_ident, PY_ident),
   FOREIGN KEY(YC_ident) REFERENCES YC_type_coll(YC_ident),
   FOREIGN KEY(PY_ident) REFERENCES PY_pays(PY_ident)
);
 
alter table CO_collectivite
   add constraint FKCO01
   foreign key (YC_ident, PY_ident)
   references YP_applicable(YC_ident, PY_ident)
;

[sylvain257]

Bjr,

Désolé pour la réponse tardive.

@escartefigue :
Donc si je suis ton MCD :

dans la table CO_collectivite tu mets donc tous les niveaux sans distinction (commune de Toulouse, Département du Calvados, Canton de Vaud etc...)
dans la table YC_type_coll, tu donnes le type de collectivité (si c'est une commune, un département etc.)
dans la table PY_Pays, ce sont donc les pays.

Je comprend pas la cardinalité (0n;0n) que tu as entre les tables YC_type_coll et PY_pays. Pcq une commune est dans un pays, mais un pays a plusieurs communes. J'aurais dit plutôt une cardinalité (1,n;1,1). Du coup, il n'y aurait pas de table intermédiaire. Pourquoi cette cardinalite, 0 à plusieurs dans les deux sens ? [MAJ : c'est bon j'ai compri ;-) ]

Pourquoi tu mets "YC_code" en character(4) ?

Merci pour ton et vos retours

Sylvain

[escartefigue]

Bonjour,

dans la table CO_collectivite tu mets donc tous les niveaux sans distinction (commune de Toulouse, Département du Calvados, Canton de Vaud etc...)
dans la table YC_type_coll, tu donnes le type de collectivité (si c'est une commune, un département etc.)
dans la table PY_Pays, ce sont donc les pays.

Tout à fait

Je comprend pas la cardinalité (0n;0n) que tu as entre les tables YC_type_coll et PY_pays. Pcq une commune est dans un pays, mais un pays a plusieurs communes. J'aurais dit plutôt une cardinalité (1,n;1,1). Du coup, il n'y aurait pas de table intermédiaire. Pourquoi cette cardinalite, 0 à plusieurs dans les deux sens ? [MAJ : c'est bon j'ai compris ;-) ]

La cardinalité minimale de zéro coté [PY_pays] est plus une précaution qu'autre chose, pour le cas où l'on charge tous les pays dans la table, y compris ceux qu'on n'utilise pas (genre de choses qu'on rencontre parfois dans les tables de typologies).
Le seul intérêt de la cardinalité minimale de zéro coté [YC_type_coll] est le cas où un type applicable à un instant "t" ne l'est plus à "t+1"
Quoi qu'il en soit, la cardinalité maximale est bien "n" de chaque coté de l'association, on a donc bien dans tous les cas une table associative

Pourquoi tu mets "YC_code" en character(4) ?

Parce que dans les tables de typologies (pays, devises, mode de paiement, codes TVA....) on utilise en général un code en plus du libellé.
Le code est souvent normé (code ISO par exemple), ce qui facilite les échanges avec les tiers.
Par exemple, pour les pays, on peut utiliser l'une des 3 normes ISO 3166 : alpha 2, alpha 3 et numérique 3.

Du char parce que ce ne sont pas des montants. Comme il s'agit de normes externes dont le contenu peut changer, il ne serait pas prudent d'utiliser une zone numérique (par exemple, dans les années 70, le département corse n°20 a été divisé en deux, 2A et 2B. Les S.I. qui avaient commis l'imprudence de stocker les départements dans une zone numérique s'en sont mordu les doigts )

Et du char fixe, car le char variable (varchar) est moins performant que le fixe quand la longueur maximale est courte (en deçà de 20 à 25 caractères, le fixe est préférable).
En effet, le varchar nécessite 1 à 3 octets pour stocker la longueur du contenu et le varchar provoque des déplacements physiques des lignes dans les pages data et index quand la longueur effective change, ce qui peut ralentir fortement les transactions et provoque de la fragmentation (désorganisation).

[sylvain257]

Bjr,

Je suis d'accord pour le character pour le champ py_code, mais pour ma part je l'ai plutôt mis sur 3 caractères avec le code CIO.

J'ai suivi ton MCD pour l'intégration des communes dans ma bdd et ça marche nickel, avec des requêtes pas trop compliquées en plus, donc nickel pour une api.

Merci beaucoup :-)

Sylvain