Discussion :

[gok6tm]

Bonjour,

Je ne maitrise pas du tout la modélisation.

J'ai déjà avancé le développement d'un projet, et lors de la phase test je m'apercoit qu'il y a de nombreux manques de réflexion sur la structure de la bdd, notamment la partie ville.

J'étais partis d'une base de donnée des villes de l'insee
id | nom ville | article | dep | codepostal

Dans les différentes fiches qui utilisent une adresse je fait référence à l'id de la ville pour récupérer le nom et le code postal de la dite ville.

La base de données n'est pas optimale
- Il manque certaines villes (par ex : euralille)
- Les cedex xx ne sont pas gérés
- Certains code postaux particuliers ne sont pas référencés (34064 Montpellier cedex 4)

Du coup je me tank une bdd (36500 entrées) incomplète

Bon et après tout ca me direz vous ?

Ben je suis perdu, alors peut-être pourriez vous me montrer / expliquer des exemples de gestion des villes dans un projet bien structuré pour que je m'en inspire.

[CinePhil]

Une petite indication ici.

Il me semblait que SQLPro abordait le sujet dans un autre article et y donnait les liens vers les tables des codes postaux mais je ne le retrouve pas.

Vous risquez déjà des problèmes avec ça :

Dans les différentes fiches qui utilisent une adresse je fait référence à l'id de la ville pour récupérer le nom et le code postal de la dite ville.

Un village n'a généralement qu'un seul code postal mais une grande ville en a souvent plusieurs !

Modélisons...
Adresse -1,1----Situer----0,n- Commune -1,n----Attribuer----1,n- Code postal

Traduction :
1) Une adresse se situe dans une seule commune et une commune peut situer plusieurs adresses
2) A une commune est attribué à 1 ou plusieurs codes postaux et un code postal est attribué 1 ou plusieurs communes.

Les codes postaux correspondant à des cedex peuvent être ajoutés au fur et à mesure de l'alimentation de votre base, ainsi que les lignes de l'association Attribuer pour ces nouveaux codes postaux.

A moins que vous ne trouviez un ficher de tous les Codes postaux des cedex. Renseignez-vous à la Poste ou voyez sur leur site.

[gok6tm]

Merci pour la piste !

Cela ne m'aide pas encore à choisir.

== possibilité ==
* Structure sql :
- table codepostaux
- table cp-ville
- table ville

* Quand l'utilisateur indique sa localité :
demande du code postal puis choix de la ville dans liste
mais je ne dispose pas de tous les codes postaux existants (arrondissements ok, mais pas ceux correspondants aux cedex)

Trouve t'on des projets open source intégrant un tel type de bdd ?

[f-leb]

effectivement, grosse mef sur les codes postaux

auteur : Nanci
Le code postal en France identifie le bureau distributeur qui achemine le courrier dans une commune. En conséquence et d'après cette définition, il n'EXISTE pas de relation entre le code postal et le code du département de la commune ...

cf faq Merise http://merise.developpez.com/faq/?pa...MCD_CodePostal

[JeitEmgie]

la structure de l'insee est une bonne base de départ…

mais vous pouvez ajouter un code "langue" et le "code iso" du pays si vous voulez être un poil plus européen pour pas cher…
(on peut encore aller plus loin avec des coordonnées géographiques… utiles dans le transport routier… )

Quant à utiliser l'id du record pour le référencement relationnel cela ne pose aucun problème technique (l'id étant une clé primaire du genre entier auto incrémenté …) quelque soit le nombre de "synonymes" en code postaux ou en noms…
par contre, évaluer bien ce qui se passe dans le temps… si vous maintenez les adresses des quidams avec des liens relationnels (foreign key -> primary key), cela signifie que des modifications peuvent se propager dans le "passé"…
si les adresses des quidams doivent conserver une valeur "historique" il faut recopier les valeurs de la table des villes dans le descriptif de l'adresse du quidam et non maintenir un lien relationnel…

par ailleurs, modéliser de manière trop compliquée l'implémentation des tuples (codes postaux, nom, pays, …) n'apportera pas grand chose à la plupart des applications…

par contre s'attarder un peu sur la notion d' "adresse" (dont code postal, ville, pays ne sont qu'une partie…) peut être plus intéressant…

[f-leb]

* Quand l'utilisateur indique sa localité :
demande du code postal puis choix de la ville dans liste
mais je ne dispose pas de tous les codes postaux existants (arrondissements ok, mais pas ceux correspondants aux cedex)

Trouve t'on des projets open source intégrant un tel type de bdd ?

j'savais bien que ça me disait qqchose, regarde là pour chercher bonheur
http://ardecheearth.free.fr/basededonnees.htm

[gok6tm]

si les adresses des quidams doivent conserver une valeur "historique" il faut recopier les valeurs de la table des villes dans le descriptif de l'adresse du quidam et non maintenir un lien relationnel…

logiquement la table des villes et/ou des code postaux et censé être fixe, je me trompe ? et donc le lien sera toujours valable.

par ailleurs, modéliser de manière trop compliquée l'implémentation des tuples (codes postaux, nom, pays, …) n'apportera pas grand chose à la plupart des applications…

Dans mon application la localisation à une importance certaine, Pour l'instant je me contente d'un tri par région, département et ville. Mais je dois pouvoir faire évoluer ces filtres pour pouvoir apprécier les distances (rayon de x km, chef lieu, ...) et aussi niveau européen et monde dans une mesure moindre ou idem mais séparée.

Ce que je cherche c'est des informations sur cet aspect là de la gestion de relation. et comme mauvais développeur que je suis je n'ai pas listé mes besoins. Un exemple de modélisation assez pousser me permettrai de mieux appréhender mon cas.

j'savais bien que ça me disait qqchose, regarde là pour chercher bonheur
http://ardecheearth.free.fr/basededonnees.htm

C'est justement la version que j'utilise

[fsmrel]

Bonsoir gok6tm,


Votre table des communes devrait comporter un attribut pour le code INSEE de la commune, c'est quand même la donnée la plus fiable.

Voyez les fichiers INSEE, qui en plus sont accompagnés d'explications :

http://www.insee.fr/fr/methodes/nome...chargement.asp

[JeitEmgie]

logiquement la table des villes et/ou des code postaux et censé être fixe, je me trompe ? et donc le lien sera toujours valable.

le lien est toujours valable, oui mais exemple avec des adresses de lieu de naissance :

au temps T0, où le quidam est né sa ville de naissance s'appelait X0 et avait pour code postal P0 ce qui vous a permis d'établir la relation vers un record R0 et vous avez établi la relation en mettant R0 dans le champ "reference_ville_naissance" du quidam…

au temps T1, une décision administrative change le nom de la ville, son code postal, … soit X1, P1, … donc après le changement R0 -> (X1, P1, … )
quand vous allez imprimer le lieu de naissance du quidam, vous allez imprimer le nouveau nom Y alors que le quidam est né à une époque où ce nom n'existait pas…
situation qui peut être acceptable ou non en fonction des critères fonctionnels de l'application…

et ce problème de valeur "historique" concerne aussi le pays de la ville… penser aux changements de frontières…

donc : retour aux besoins de l'application…

Dans mon application la localisation à une importance certaine, Pour l'instant je me contente d'un tri par région, département et ville. Mais je dois pouvoir faire évoluer ces filtres pour pouvoir apprécier les distances (rayon de x km, chef lieu, ...) et aussi niveau européen et monde dans une mesure moindre ou idem mais séparée.

avoir beaucoup de colonnes et une seule table n'est pas "compliqué"…
je faisais allusion à

Code :

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

1
2
3
4
- table codepostaux
- table cp-ville
- table ville

si vous voulez modéliser en plus des notions de régions, départements, … vous allez déjà vite vous retrouvez avec des choses compliquées surtout si vous prévoyez d'être "internationnal"…

donc inutile de compliquer en plus au niveau basique des villes et des codes postaux …
si l'on peut l'éviter car cela n'apporte pas grand chose au niveau fonctionnel…

mais la question "première" dans tout cela serait :

pourquoi modéliser à partir des tables ?
pourquoi ne pas partir d'un autre niveau : par exemple modéliser les objets et leurs classes et laisser un ORM générer les tables ?

[souviron34]

logiquement la table des villes et/ou des code postaux et censé être fixe, je me trompe ? et donc le lien sera toujours valable.

Faux , voir plus bas.

le lien est toujours valable, oui mais exemple avec des adresses de lieu de naissance :
...
au temps T1, une décision administrative change le nom de la ville, son code postal, … soit X1, P1, … donc après le changement R0 -> (X1, P1, … )
quand vous allez imprimer le lieu de naissance du quidam, vous allez imprimer le nouveau nom Y alors que le quidam est né à une époque où ce nom n'existait pas…
situation qui peut être acceptable ou non en fonction des critères fonctionnels de l'application…

et ce problème de valeur "historique" concerne aussi le pays de la ville… penser aux changements de frontières…
..

Sans compter qu'une ville peut "naitre", ou bien être scindée, ou au contraire regroupée...

• a) les seules données fiables sont celles de l'Etat (en général payantes)
• b) la seule possibilité d'être "historique" est de sauvegarder année après année les bases officielles.
• c) c'est bien le problème des open-sources/freeware dans ce domaine : la vérité des bases de données et pour professionels seulement (accès payant).
• d) les modifications peuvent être mulit-annuelles (le répertoire interne de la Poste se modifie presque tous les jours (nouvelles rues dans nouveaux lotissements, etc etc)).

si vous voulez modéliser en plus des notions de régions, départements, … vous allez déjà vite vous retrouvez avec des choses compliquées surtout si vous prévoyez d'être "internationnal"…
...

Absolument. La notion de département est française. La notion de région peut être plus étendue, mais pas internationale.

En bref, j'ai travaillé sur des choses comme ceci dans le passé, et la seule "modélisation" possible est une modélisation quasi-plate, avec champs optionnels..

Pour les villes :

nom, latitude, longitude, altitude, type, nb d'habitants, pays, région, département, code

(les 3 derniers champs sont plus ou moins optionnels (absence = champ vide)). (type peut être maison isolée, hameau, village, ville)

Les adresses sont une liste se référant à la liste ci-dessus.

Ces listes sont disponibles soit dans les Agnces Nationales de Toponymie, soit en France à l'IGN.

Pour les codes postaux, les seules sources fiables sont les Postes de chaque pays.


Tout ce qui ne passe pas par les organismes officiels pour ce genre de choses est du bidouillage, qui ne donnera aucune garantie.


Et le lien se fait via une liste nominative de gens, avec un lien vers une adresse, qui elle-même contient un lien vers les autres tables...

[JeitEmgie]

En bref, j'ai travaillé sur des choses comme ceci dans le passé, et la seule "modélisation" possible est une modélisation quasi-plate, avec champs optionnels..

ou alors, il faut changer complétement de point de vue, faire une modélisation "sémantique" genre OWL-RDF, qui génère des classes d'objets, qui seront ensuite soit converties en tables par un ORM… soit par un outil spécialisé RDF…

villes, pays, régions, départements, … sont des "individus" membres de "listes" et ces individus peuvent être mis en relation entre eux de différentes manières elle-mêmes meta-décrites dans des tables adhoc…

on a alors un modèle très orthogonal mais dont toute l'intelligence est transférée dans le code qui le manipule…
(il faut bien "payer" quelque part…)

trouver les villes de France n'est plus un simple :

"sélectionner les villes dont le code ISO du pays est 'FR'"

mais

"sélectionner les individus de la liste des villes qui sont en relation "est située dans le pays" avec un individu de la liste des pays et dont l'attribut "a pour code ISO" est 'FR' "

c'est évidemment plus lourd et plus verbeux surtout si l'on ne dispose pas des outils pour automatiser la génération du code…

par contre ajouter une notion comme "est traversée par le cours d'eau" devient alors (presque) trivial…
car on ne doit plus modifier le modèle… (enfin, pour tout dire, si on est passé par un ORM, un petit peu quand même suivant la façon dont ce dernier gère les relations d'héritage…)… mais simplement ajouter des données aux tables de meta-description des listes de valeurs (si la liste des cours d'eau n'existe pas encore) et des relations entre individus de ces listes (pour définir que "est traversée par le cours d'eau" peut s'appliquer seulement entre individus de la "liste des villes" et et de la "liste des cours d'eau" …)

[gok6tm]

Merci pour votre participation !

La valeur historique de l'adresse ne m'intéresse pas, je me focalise sur l'adresse actuelle, qui doit être à jour et valide, je suppose alors que la gestion via des liens est dans mon cas viable.

le code postal reste un souci, car plusieurs cp par ville, alors mieux vaut le stocker dans la table quidam pour éviter les duplication d'entrées dans la table ville.

une structure de table comme celle ci devrait correspondre :

Code :

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

1
2
3
4
5
6
7
8
- table quidam :
id, [...], adresse, id_ville, codepostal, cedex

- table villes :
id, nom, latitude, longitude, type, nb d'habitants, pays, région, département

+ table region
+ table dep

inspiré de l'exemple de souviron34 (a quoi correspond le champ "code" ? dans ton exemple)

Du coup la table ville, en passant à un niveau international atteint une taille monstrueuse, est ce viable ?

mais la question "première" dans tout cela serait :

pourquoi modéliser à partir des tables ?
pourquoi ne pas partir d'un autre niveau : par exemple modéliser les objets et leurs classes et laisser un ORM générer les tables ?

Tout à fait thierry !
Pourquoi ? ... Car je développe en procédural (pas tapé) et la modélisation m'est inconnue du coup ce type de discours :

ou alors, il faut changer complétement de point de vue, faire une modélisation "sémantique" genre OWL-RDF, qui génère des classes d'objets, qui seront ensuite soit converties en tables par un ORM… soit par un outil spécialisé RDF…

villes, pays, régions, départements, … sont des "individus" membres de "listes" et ces individus peuvent être mis en relation entre eux de différentes manières elle-mêmes meta-décrites dans des tables adhoc…

on a alors un modèle très orthogonal mais dont toute l'intelligence est transférée dans le code qui le manipule…
(il faut bien "payer" quelque part…)

me parait fort intéressant mais est complètement incompréhensible pour moi.

[CinePhil]

la modélisation m'est inconnue

Tu prendras bien une petite Merise ?

Un petit détail :

- table villes :
id, nom, latitude, longitude, type, nb d'habitants, pays, région, département

Evite les noms de colonnes avec des accents (région, département) et surtout en plusieurs mots avec apostrophe (nb d'habitants). Les SGBD n'aiment pas trop ça et c'est idéal pour faire des erreurs.

a quoi correspond le champ "code"

Je pense qu'il s'agit du code INSEE de la commune.
Tu connais ton numéro de sécurité sociale ?
Voici le début du mien : 1 63 07 76 351...
Ce que j'ai mis en gras sont le numéro de département et le numéro de la ville dans le département. Le code de ma ville de naissance, Le Havre, est donc le 76351. Rien à voir avec le code postal.

Du coup la table ville, en passant à un niveau international atteint une taille monstrueuse, est ce viable ?

Il y a un peu plus de 36500 communes en France qui est probablement un des pays qui en a le plus.
Tu peux peut-être trouver sur le net des fichiers qui répertorient les villes du monde mais pas sûr que ce soit exhaustif.
En tout cas, ça m'étonnerait qu'il y ait plus de quelques centaines de milliers de communes dans le monde.
Je travaille quotidiennement avec des tables de plusieurs dizaines de millions de lignes alors pas d'affolement.

[JeitEmgie]

ici avec seulement 50 pays : 583182 …
(et sans la Chine, l'Inde, … et aucun pays d'Amérique… )

[souviron34]

a quoi correspond le champ "code" ? dans ton exemple)

Je pense qu'il s'agit du code INSEE de la commune.

Absolument ..

Il y a un peu plus de 36500 communes en France qui est probablement un des pays qui en a le plus.
Tu peux peut-être trouver sur le net des fichiers qui répertorient les villes du monde mais pas sûr que ce soit exhaustif.
En tout cas, ça m'étonnerait qu'il y ait plus de quelques centaines de milliers de communes dans le monde.
Je travaille quotidiennement avec des tables de plusieurs dizaines de millions de lignes alors pas d'affolement.

ici avec seulement 50 pays : 583182 …
(et sans la Chine, l'Inde, … et aucun pays d'Amérique… )

D'où un intérêt certain pour par exemple découper par pays ou continent

Mais encore une fois ça dépend pas mal de ce que le PO a dans la tête ...

[JeitEmgie]

D'où un intérêt certain pour par exemple découper par pays ou continent

Mais encore une fois ça dépend pas mal de ce que le PO a dans la tête ...

villes, régions, pays, … sont des notions à caractère géopolitique…

alors qu'une liste de codes postaux n'a qu'un caractère opérationnel lié à la distribution de courrier, colis, …
(en gros ça ne sert qu'à contruire un texte qu'on appelle "adresse postale" qui sera "imprimé" sur un "support" lié un "objet" qui doit être livré à l'endroit désigné par cette "adresse postale" à travers un processus logistique qui est prévu pour fonctionner sur base de ces dites "adresses postales"… après tout : les coordonnées GPS désignent de manière tout aussi précise l'endroit en question…)

(et même dans une "adresse" au sens large, le code postal n'est pas toujours mentionné… par exemple dans un acte notarié…)

parmi les questions que vous devez vous posez :
- la notion géopolitique de ville a-t-elle une utilité dans l'application ou est-ce seulement la notion de code postal qui compte ?
- si les 2 sont nécessaires : est-il utile ou nécessaire de les gérer séparément, (pourquoi…) et si oui faut-il un lien relationnel entre les 2 (à quelle question répondrait-il …) ? (dans ce cas, notez que le code INSEE n'a peut-être rien à faire dans la liste des codes postaux… mais qu'il est utile dans la liste des villes… et encore rien que pour les villes françaises…)
- etc.

[gok6tm]

Merci pour tous ces éclaircissements, je pense avoir défini une structure cohérente avec mes besoins.

Evite les noms de colonnes avec des accents (région, département) et surtout en plusieurs mots avec apostrophe (nb d'habitants). Les SGBD n'aiment pas trop ça et c'est idéal pour faire des erreurs.

oui bien entendu