avril
2012
Un article de SQLpro
Faisant suite à l’article consacré à la modélisation des personnes, voici celui consacré à la modélisation des adresses répondant aux normes européennes de l’adresse postale :
Commençons par le MCD :
L’entité entité T_E_ADRESSE_ADR comporte les éléments de tête de l’adresse :
– code postal (8 caractères)
– ville (32 caractères)
–> Soit au total 38 caractères (32 + blanc + 8)
– éventuel CEDEX
– éventuel mention de « Chez m. Untel… »
L’entité fille ligne d’adresse T_E_ADRESSE_LIGNE_ADL permet de faire figurer 1 à 4 lignes complémentaires de 38 caractères.
On doit indiquer la position de chaque ligne (1, 2, 3 ou 4 donc une contrainte de domaine) car un SGBDR étant par nature ensembliste, bien que les lignes aient été rentrées dans un certains ordre, il est possible qu’il restitue dans le désordre les lignes saisie, d’où l’importance de la notion de position de la ligne.
En sus une indication booléenne permet de savoir quelle ligne est la voie (lorsqu’il y en a une) afin de pouvoir effectuer un contrôle de validité d’adresse auprès d’un organisme spécialisé.
Vous noterez la dissociation entre l’entité externe des codes postaux T_X_CODE_POSTAL_CPL (données venant de La Poste, mais que l’on pourrait étendre à des listes de codes postaux étrangers) et le couple code postal / ville dans l’entité T_E_ADRESSE_ADR. Ceci est voulu car les codes postaux évoluent continuellement et qu’un couplage fort par intégrité référentielle rendrait difficile à maintenir. En ce faisant, la liste des codes postaux est récupérée auprès de la poste 1 fois l’an et on peut ajouter dans la table des adresse des codes postaux divergents, par leur nouveauté. L’entité T_X_CODE_POSTAL_CPL servant simplement d’aide à la saisie dans l’IHM.
Au niveau du MPD, deux contraintes sont à ajouter :
Outre la contrainte de domaine de la colonne ADL_POSITION (CHECK (ADL_POSITION BETWEEN 1 AND 4)) définie dans le MCD, on doit ajouter :
– une contrainte d’unicité entre dans la table T_E_ADRESSE_LIGNE_ADL pour les colonnes ADR_ID et ADL_POSITION :
ALTER TABLE T_E_ADRESSE_LIGNE_ADL
ADD CONSTRAINT UK_ADL_ADR_POS
UNIQUE (ADR_ID, ADL_POSITION);
– une contrainte sous forme de déclencheur afin qu’il n’y ait jamais qu’une seule ligne d’adresse ayant la valeur « true » pour la colonne ADL_VOIE. Voici le code d’un tel déclencheur pour MS SQL Server :
CREATE TRIGGER E_IU_ADL
ON T_E_ADRESSE_LIGNE_ADL
AFTER INSERT, UPDATE
IF EXISTS(SELECT NULL
FROM T_E_ADRESSE_LIGNE_ADL
WHERE ADL_VOIE = 1
GROUP BY ADR_ID
HAVING COUNT(*) > 1)
ROLLBACK;
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
Bonjour,
Pouvez-vous apporter des précisions sur la ligne ADR_CHEZ,
En quoi est-elle différente des lignes adresses de la table T_E_ADRESSE_LIGNE_ADL ?
Si celle-ci est utilisée, il faut donc limiter le nombre de lignes de la table T_E_ADRESSE_LIGNE_ADL relative à l’ID à 3 ?
Je souhaitais lier la table T_X_CODE_POSTAL_CPL à la table T_X_INSEE_PAYS_PAY, mais cela n’est pas correct, car cela lié l’entité à l’ensemble des codes postaux. Il n’est pas possible de relier la table des codes postaux aux pays
Merci
Bonjour,
en regardant par curiosité cet exercice, j’ai remarqué que vous nommiez vos entités T_uneLettre_libelle_trigramme
j’aimerais savoir à quoi correspond la lettre, a-t-elle une sémantique ou est-elle choisi arbitrairement ?
Ces lettre ne sont pas la par hasard. Elles ont une signification précise :
E => Entité (ce sont les principales entités du MCD)
R => Référence (c’est le référentiel de données, exemple liste des civilités, des sexes…)
J => Jointure (table de jointure ou d’association, résultant d’une cardinalité n:m dans le MCD)
A => Administrative (par exemple liste des utilisateurs)
X => référentiel externe (par exemple code postaux, liste ISO des payes…)
etc…
Le but étant que ces tables puissent avoir des traitements de maintenance génériques voir des privilèges particuliers.
Merci !
Bonjour,
Pourquoi ne pas enlever les champs ADR_CP et ADR_VILLE de T_E_ADRESSE_ADR et faire une jointure avec T_X_CODE_POSTAL_CPL et avoir ainsi CPL_ID en clé étangère dans T_E_ADRESSE_ADR ?
1) un même code postal est attribué à plusieurs villes. La clef CODE POSTAL ne suffit pas;
2) la liste des codes postaux étant gérée par La Poste en France, cette liste évolue en permanence avec de nouveaux CP, le retrait d’anciens et la modification d’autres. Cependant la liste complète n’est publiée qu’une fois par an, alors que de nouvelles données évoluent rapidement;
3) cette table sert à vérifier la présence d’un code postal, d’aide à la saisie du CP d’une adresse et peut être mise à jour par certains utilisateurs. Le but est qu’en cas de saisie d’une adresse comportant un CP inconnu, une alerte NON BLOQUANTE soit envoyée à l’utilisateur.
Bonjour,
Quel fichier externe utilisez vous pour les codes postaux?
J’ai comme référence celui-ci : http://www.galichon.com/codesgeo/
Cordialement.
Calculatrice en panne ?
–> Soit au total 38 caractères (32 + blanc + 8)
Chez moi ça fait 41
8 car il existe des pays avec des CP à 8 caractères.
mais en France 5….
donc 5 + blanc + 32 = 38 !
On peut rajouter une contrainte de ce genre :
ALTER TABLE T_E_ADRESSE_ADR
ADD CONSTRAINT CK_ADR_VIL_CP
CHECK (CHARACTER_LENGTH(CONCAT(ADR_CP, ‘ ‘, ADR_VILLE) <= 38);