Discussion :

[BModjo]

Bonjour à tous.

J'ai un petit souci sur la partie suivante de mon MCD. Je possède deux tables :
- Une table t_elem (table éléments) pour des plantations (haies, arbres...) :

id_elem : identifiant élément (entier auto-incrémenté)
insee_e : code Insee de la commune de plantation ("e" pour "externe" puisque ce sont des données issues de partenaires externes)

- Une table t_com_15 basée sur le Code Officiel Géographique de l'Insee de 2015 et peuplée grâce à ces mêmes données :

insee_com_15 : le code insee me sert de clé primaire
nom_com_15 : le nom de la commune

Le souci est que, comme ma table t_com_15 est basée sur les données Insee 2015, certains des codes Insee que je possède pour peupler la table t_elem ne correspondent plus : certaines communes ont été fusionnées (donc code Insee n'existe plus dans les données 2015), ont été supprimées...

Il est important que je conserve à la fois l'information insee_e (le code Insee qu'on m'a remonté) et le nom de la commune correspondant, et que je puisse tout de même faire le lien avec le code de 2015.

J'avais donc pensé à ajouter une table intermédiaire comme suit :
- t_elem (table éléments):

id_elem : identifiant élément
insee_e : code Insee externe

- ti_localise (table intermédiaire):

#insee_e : code Insee externe
nom_com_e : nom de la commune remonté (correspondant au code Insee insee_e)
#insee_15 : code Insee 2015 correspondant au code externe

- t_com_15 (table commune selon le référentiel Insee 2015):

insee_com_15 : le code insee 2015
nom_com_15 : nom de la commune 2015

L'idée étant de conserver le nom_com_e uniquement quand celui-ci est différent du nom_com_15 pour éviter des données inutiles. Cette table permet aussi de faire le lien entre plusieurs insee_e et un insee_15 (par exemple dans le cas de regroupement de communes).

Cette solution me semble un peu branlante, dans la mesure où la plupart du temps, insee_e et insee_15 seront identiques, donc la table intermédiaire t_localise stocke pas mal d'info pour rien. Y a-t-il une solution plus pratique ou plus légère ?

Merci d'avance et un excellent journée à tous.

EDIT : j'avais aussi penser à rapatrier id_elem dans ma table ti_localise pour faire le lien mais cela me semble également redondant en terme d'information... J'avoue être un peu perdu.

[escartefigue]

Bonjour,

- Une table t_com_15 basée sur le Code Officiel Géographique de l'Insee de 2015 et peuplée grâce à ces mêmes données :

insee_com_15 : le code insee me sert de clé primaire
nom_com_15 : le nom de la commune

Le souci est que, comme ma table t_com_15 est basée sur les données Insee 2015, certains des codes Insee que je possède pour peupler la table t_elem ne correspondent plus : certaines communes ont été fusionnées (donc code Insee n'existe plus dans les données 2015), ont été supprimées...

Et oui, le choix d'une clef primaire dont la valeur est issue d'une nomenclature externe vous expose fatalement à ce genre de mésaventure.
Une clef primaire doit être stable (invariante), et pour ce faire asémantique.

J'avais donc pensé à ajouter une table intermédiaire comme suit :
- t_elem (table éléments):

id_elem : identifiant élément
insee_e : code Insee externe

- ti_localise (table intermédiaire):

#insee_e : code Insee externe
nom_com_e : nom de la commune remonté (correspondant au code Insee insee_e)
#insee_15 : code Insee 2015 correspondant au code externe

- t_com_15 (table commune selon le référentiel Insee 2015):

insee_com_15 : le code insee 2015
nom_com_15 : nom de la commune 2015

Que se passe-t-il quand 2 communes ou plus fusionnent ? garde-t-on le code INSEE de l'une des communes fusionnées ?
Si c'est le cas, votre solution ne fonctionnera que dans un sens car il n'y aura pas bijection entre nouvelle et ancienne valeur : déterminer le nouveau code INSEE à partir de l'ancien est possible, mais l'inverse ne l'est pas.
Sinon vous n'êtes pas obligés de stocker toutes les correspondances ancien/nouveau code, vous pouvez ne stocker que les changements, et vous ne consulterez ces changements que si vous ne trouvez pas la valeur dans la table des codes insee.

Peut être faut il prévoir aussi des fusions itératives ?
N'y a-t- il pas d'autres cas de changement de n° de commune que les fusions ?
N'existe-t- il pas aussi des cas de séparation ?

[antuki]

Bonjour,

Que se passe-t-il quand 2 communes ou plus fusionnent ? garde-t-on le code INSEE de l'une des communes fusionnées ?
Si c'est le cas, votre solution ne fonctionnera que dans un sens car il n'y aura pas bijection entre nouvelle et ancienne valeur : déterminer le nouveau code INSEE à partir de l'ancien est possible, mais l'inverse ne l'est pas.
Sinon vous n'êtes pas obligés de stocker toutes les correspondances ancien/nouveau code, vous pouvez ne stocker que les changements, et vous ne consulterez ces changements que si vous ne trouvez pas la valeur dans la table des codes insee.

En général, quand deux communes fusionnent, on garde en effet le code Insee d'une des communes fusionnées sauf en cas, je crois, de transfère de chef-lieu. Pour travailler sur ce genre de données il faut donc travailler avec des tables de passages à partir du code officiel géographique (COG) publié par l'Insee.

Quelques liens utiles qui montrent que vos questions sont d'actualité suite à la disparition de 1500 communes environ depuis 2015.
- https://github.com/etalab/geohisto : une réutilisation du COG faite par Etalab (bases de données historiques faites à partir du COG)
- https://github.com/antuki/COGugaison : un package sur lequel je suis en train de travailler pour le logiciel statistique R permettant de transformer des bases de données d'une géographie à une autre depuis 1968 grâce à quelques fonctions.

[CinePhil]

Vite fait, de mémoire et sauf erreur de ma part, le référentiel INSEE comprend l'historique des communes, avec les dates de validité (création et/ou disparition, voire table de fusions de communes). Avez-vous cherché de ce côté ?

Pour modéliser, vous pouvez par exemple faire ceci :
tr_commune_cmn (cmn_id, cmn_code_insee, cmn_nom, cmn_date_creation, cmn_date_fin)
tj_cmn_fusionner_cmn_cfc (cfc_id_commune_absorbante, cfc_id_commune_absorbee, cfc_date_fusion)