Discussion :

[La_Kai]

Bonjour,

Je suis en Licence professionnelle et pour mon stage de fin d'étude je dois "monter" une base de données Access. Elle sera composée de divers fichiers Excel éparpillés mais ayant des corrélations entre eux. Le but étant d'harmoniser la BD et de simplifier son utilisation. Je précise que je débute sur Access.

Après une période de nettoyage, je me penche vers Access pour préparer l'incorporation des fichiers Excel. Assez vite me vient une interrogation, j'ai cherché sans réellement trouver de réponse à ma question. Voila mon questionnement :
Ma base est constituée de diverses tables et au sein de ces diverses tables, il y a des redondances (par exemple des Nom/prénoms, codes postaux, nom de communes, etc). Il est préférable pour la lecture de cette base de données par son futur utilisateur que ces redondances restent présentes (elles sont limitées au maximum).
Ce que j'aimerais pouvoir faire (et que vous me disiez si c'est possible, et si oui, comment le faire), c'est mettre en place des mises à jour "automatiques" entre les tables.
C'est-à-dire que si je modifie le nom de la ville où réside une personne dans une table, que le nom de la ville soit également modifié dans une autre table.

Je vous mets un exemple en pièce jointe pour que vous puissiez mieux comprendre mon besoin.

J'ai essayé d'être le plus clair possible, n'hésitez pas à me demander des précisions,

Merci d'avance

[ONTAYG]

Bonjour,

Alors là il faut revoir complètement ta conception, ce que tu demandes même si c'est possible n'est pas du tout bon pour une base de données relationnele.

ONTAYG

[marot_r]

Bonjour et bienvenu dans le monde merveilleux de Access :-).

En complément de la réponse de ONTAYG, dans une BD relationnelle on s'efforce au maximum de répéter des données.
Quand on a besoin d'une information qui provient d'une autre table on va la pécher avec une requête ou une fonction comme DFirst() (PremDom() en français).

Ici un exemple simple : une relation entre une commune et un client.

tblCommune
ClefCommune (ex No Insee, de mémoire j'ai quitté la France il y a plus de 20 ans)
NomCommune
CodePostal

tblClient
Info sur le client
ClefCommune

En relation avec tblCommune sur ClefCommune.

On ne répète pas le code postal, on note la commune et on se servira de ce numéro pour le retrouver dans la table des communes.

Access n'a pas vraiment de triggers comme les grosses BD comme Oracle (elle a une espèce de machin, les macros de données, qui essaye d'y ressembler et que je trouve trop limité et contraignant) donc si tu dois absolument répéter de l'information dans une autre table il va, selon moi, falloir le faire par code VBA.

Et la fonction Mise à Jour en cascade ne marche que sur les clefs étrangères donc ne peut recopier des informations que si elles sont au départ des clefs primaires. Ce n'est pas adapter au besoin que tu décris.

A+

[Pierre Fauconnier]

Salut.

Si j'ai bien compris ton propos, tu mets justement en place une BD relationnelle.

Tu ne dois donc pas répéter le nom de la ville dans les tables, mais le stocker une seule fois dans la table des villes et faire le lien avec les autres tables grâce aux paires clé primaire/Clé étrangère.

Ainsi, tu récupères n'importe quel champ de la table des villes dans toute requête qui possède une clé étrangère vers cette table. Si tu changes une donnée de la table des villes, elle se "répercutera" automatiquement partout où tu utilises une jointure avec cette table.

L'idéal étant, avec Access, de le laisser déterminer ses clés primaires grâce aux champs numériques autoincrémentés. Perso, je ne suis pas chaud pour un code Insee comme clé primaire et préfère systématiser l'approche avec des entiers longs. Pour moi, la clé primaire ne devrait jamais avoir une signification métier.

Es-tu à l'aise avec ces concepts ou bien souhaites-tu un rafraîchissement?


Cela étant dit, je ne comprends pas bien pourquoi tu viens par Access pour réaliser ce genre de choses. Si la finalité est de "gérer" tes données dans un fichier Excel ou plusieurs et que tu pars d'Excel, pourquoi ne pas rester dans Excel?

[La_Kai]

Tout d'abord, merci pour vos conseils.

Si j'ai bien compris, je dois le plus possible réduire la quantité de champs similaires dans les diverses tables. C'est assez simple à corriger comme soucis dans mon projet.

ONTAYG : quelle genre de conception serait la meilleure pour une BD relationnelle ?

marot_r : je débute en Access et je connais de nom le code VBA, mais je n'ai aucune idée de comment l'utiliser. J'écris un peu en SQL, mais c'est tout.
Effectivement, je me suis rendu compte que la mise à jour en cascade était "réservée" aux clés primaires et étrangères, ce qui ne colle pas avec mon besoin.

Pierre Fauconnier : es-tu à l'aise avec ces concepts ou bien souhaites-tu un rafraîchissement? --> je veux bien un rafraîchissement s'il te plaît, ça ne me fera pas de mal.
Je vais voir si je garde ou non le Code Insee comme clé primaire pour les communes.
J'utilise Access car c'est ce qui a été proposé lors de l'élaboration du projet avec ma tutrice de stage. Y aurait-il d'autres logiciels plus adaptés pour les BD relationnelles ? (je précise que je suis dans une PME et que l'achat de logiciel est limité par le budget).
Le but n'est pas d'utiliser Excel en finalité, je pars d'Excel car la "base de données" existante est sur Excel, mais très mal organisée, elle est éparpillée en plein de fichiers différents, avec peu de structure, etc. Elle a été faite au fil du temps par son unique utilisatrice, et maintenant elle doit la partager avec un collègue, ce qui pose certains problèmes d'utilisation. L'objet de mon stage est le nettoyage et l'harmonisation/structuration de cette BD, à l'aide d'Access.
Il y aura certaines tables qui seront extraite d'Access vers Excel pour faire du publipostage par exemple. Mais cela ne concerne pas toutes les tables.


Si j'ai bien compris, il faut que je modifie la morphologie de ma base de données, et changer cette morphologie m'évitera de faire des redondances de champs dans les différentes tables et également d'avoir à faire des mises à jour en cascade. Je ne me trompe pas ?

Merci encore pour votre aide,

[marot_r]

Bonjour.

Si j'ai bien compris, il faut que je modifie la morphologie de ma base de données, et changer cette morphologie m'évitera de faire des redondances de champs dans les différentes tables et également d'avoir à faire des mises à jour en cascade. Je ne me trompe pas ?

Probablement, c'est le côté relationnelle de BD Relationnelle qu'il faut exploiter.
En gros tu as des tables de références qui contiennent tes listes d'éléments (ex : Commune) et tes tables utilisatrices qui ne contiennent que les clefs (ou identifiants) de tes éléments.
Si tu peux poster ton modèle (copie d'écran de la fenêtre des relations) actuel ce sera plus facile pour te donner des réponses ciblées avec des exemples sur tes propres données.

Pour le code Insee, oui c'est peut-être une bonne idée d'avoir ton propre numéro. Ceci dit c'est un code qui est très peu susceptible de changer donc personnellement cela ne me pose pas de problème de l'utiliser et cela rend selon moi les données plus "lisibles". Quand tu vois, NumCommune=123456 tu sais que c'est la commune 123456 et tu n'as pas à te référer à une autre table pour savoir qu'elle est cette commune.

A+

[Pierre Fauconnier]

Salut René,

Ce pourquoi je créerais les clés primaires en numérique long autoincrémenté, c'est que ça formalise et généralise l'approche. En pratiquant ainsi, je sais que les clés primaires sont des entiers longs, et donc mes clés externes aussi. Du coup, je ne dois pas réfléchir au type ni à la longueur de la clé externe.

De plus, sur des grosses bases de données, c'est nettement plus léger et plus rapide, car le traitements d'indexation en entier long sont les plus rapides qui soient, et les entiers longs tiennent sur 4 octets, ce qui donne 8 octets pour la paire clé primaire/clé externe plus le poids de l'indexation dans la table des index. Donc perso, je ne m'embête pas à me poser la question de savoir si la valeur "métier" va changer ou a un peu ou beaucoup de risques de ne pas changer. Ma règle d'or: les clés n'ont jamais de signification métier.

Après, chacun fait comme il veut, évidemment.

[marot_r]

Bonjour Pierre :

Après, chacun fait comme il veut, évidemment.

C'est pour cela que je présentais mes arguments en faveur du code Insee :-).

A+

[La_Kai]

Probablement, c'est le côté relationnelle de BD Relationnelle qu'il faut exploiter.
En gros tu as des tables de références qui contiennent tes listes d'éléments (ex : Commune) et tes tables utilisatrices qui ne contiennent que les clefs (ou identifiants) de tes éléments.
A+

C'est-à-dire que même si dans une table je n'ai plus mes noms de communes (car ils se trouvent dans une autre table, dite d'élément), si j'ai recours à une requête, j'aurais toujours la possibilité d'afficher les noms de communes au sein de cette requête si je le souhaites ?

Pour les codes Insee, je ne sais pas encore si je vais les utiliser en tant que clé primaire ou non.

Je n'ai pas encore de modèle de relations assez abouti pour que ce soit utile de le montrer, mais peut-être d'ici mercredi ou la semaine prochaine ! Je reviendrais le poster ici, et vous m'aiguillerais si le cœur vous en dit.
Actuellement je poursuis le nettoyage des fichiers, qui n'est pas très loin d'être terminé.

Merci de votre aide

[Pierre Fauconnier]

C'est-à-dire que même si dans une table je n'ai plus mes noms de communes (car ils se trouvent dans une autre table, dite d'élément), si j'ai recours à une requête, j'aurais toujours la possibilité d'afficher les noms de communes au sein de cette requête si je le souhaites ?

Oui, c'est le principe même de la Base de Données Relationnelle qui s'appelle l'unicité de la donnée. On renseigne la donnée 1 fois et on s'en sert grâce à la clé primaire. Comme cette clé primaire ne change normalement pas (si on respecte mon principe de clé primaire autoincrémentée sans signification métier), on est certain de toujours pointer vers la bonne donnée, et on est libre de pouvoir modifier les valeurs des enregistrements de la table sans risquer de casser l'intégrité.

Lorsque l'on a créé les tables, on crée la relation par défaut (presque toujours une relation un à plusieurs) et on place l'intégrité référentielle. En procédant ainsi, on s'assure qu'une clé étrangère pointe vers une clé primaire qui existe et en corollaire, on s'assure de ne pas pouvoir supprimer un enregistrement lorsqu'une clé étrangère pointe vers sa clé primaire. Ca permet de garantir que si j'ai des contacts qui habitent à Machault, je ne pourrai pas supprimer cette localité de ma table.




Voici un schéma qui montre la création des deux tables ainsi que leur liaison au travers des paires PK/FK. On voit bien que l'on a renseigné une seule fois chaque localité et que chaque contact "récupère" les données de sa localité grâce à la clé externe (FK).




Dans les faits, pour capitaliser sur la création des listes déroulantes, on place une liste déroulante pointant vers les localités dans la table des contacts, de façon à pouvoir sélectionner facilement la localité pour un contact donné.

Ici, j'illustre la création d'une liste déroulante dans la table des contacts pour le champ LocaliteFK qui récupère les données de la table des localités pour faciliter la sélection par le nom. On pourrait enregistrer la requête (Select...) pour capitaliser aussi sur ce travail. On voit que l'on peut alors choisir facilement la localité dans la table, mais aussi dans les formulaires et requêtes qui seront basés sur la table des contacts.




Je détaille la mise en oeuvre de cette liste déroulante dans ce tuto, notamment pour montrer que dans un formulaire, on pourrait proposer de saisir la localité sur base de son nom ou de son code postal (ou insee ou autre)

[La_Kai]

J'y vois déjà plus clair !

Pour ce qui est de la liste déroulante avec les communes, si j'ai 3600 communes dans ma plus grosse base de données (base de données communale à échelle régionale), est-il quand même intéressant de mettre en place cette liste déroulante ? Ce que je veux dire c'est que l'aspect "proposition" sera moindre car le nombre de communes est trop grand, non ?
Mais j'imagine qu'il y a quand même un intérêt à le faire.

Au niveau de la création de vos deux tables, quel est l'intérêt de mettre des numéros autoincrémentés à la place du nom de la ville par exemple ? [j'ai compris que c'est ce qu'il fallait faire, mais j'aimerais connaître l'explication précise pour que je ne me trompe pas, ou le moins possible sur ce point].

[Pierre Fauconnier]

Les noms de ville sont un très bon exemple de ce qu'il ne faut pas faire puisque tu pourrais avoir plusieurs villes éponymes dans ta liste. Du coup, tu ne saurais plus vers laquelle pointer. Or, deux propriétés caractérisent une clé primaire:

• elle est unique dans la table;
• elle est indexée.

On comprend bien pourquoi elle doit être unique. Il faut pouvoir y faire référence sans ambiguïté. C'est d'ailleurs pour cela que le code insee a été créé.

Pour ce qui est de l'indexation, il faut comprendre qu'Access crée une table parallèle dans laquelle il place les clés primaires par ordre croissant. Ca lui permet une recherche extrêmement rapide de l'enregistrement. Pour te donner un ordre de grandeur, ça permet à Access de retrouver une donnée dans un jeu de 1 million de données en lisant seulement 20 fiches au maximum! Si les clés n'étaient pas triées, Access devrait lire 500.000 lignes en moyenne pour trouver la bonne...

La clé primaire a pour vocation d'être utilisée dans plusieurs tables sous forme de clé externe. On le voit ici dans le schéma que j'ai illustré précédemment ou la table des contacts va pêcher l'info dans la table des localités. La clé primaire est donc présente UNE fois dans la table des villes et une fois dans la table des index, mais autant de fois en clé étrangère dans la table des contacts qu'il y a de contacts dans une ville. Si 5000 personnes habitent à SAINT-OUEN-DE-THOUBERVILLE (Insee 27580) dont la PK est par exemple 50, il y aura 5000 fois la valeur 50 dans la colonne LocaliteFK de la table des contacts. Il est donc intéressant d'avoir une clé assez courte. Une clé primaire "entier long" prend 4 octets quelle que soit sa valeur et permet en théorie 2 milliards de clés. On a donc en schématisant un peu une fois 4 octets dans la clé primaire, une fois 4 octets pour l'index et 5000 fois 4 octets pour les clés étrangères, soit 20008 octets occupés (c'est pas pile poil comme ça dans la réalité, mais pas très loin...). Comme dit précédemment, les calculs d'indexation sont extrêmement rapides sur des entiers longs et la lecture d'entiers longs lors de la recherche est extrêmement rapide aussi. On est en plein dans du calcul binaire. Par contre, si tu mets SAINT-OUEN-DE-THOUBERVILLE comme clé primaire, tu as 26 octets pour la clé primaire, 26 octets pour l'index et 5000 fois 26 octets pour les clés étrangères (je ne sais plus si c'est de l'unicode, mais si c'est de l'unicode, c'est le double)... tu alourdis ta base de 130 Ko au lieu de 20Ko... De plus, le traitement des chaines textuelles est plus lent que celui des entiers longs, tant sur l'indexation que sur la lecture... En clair, tu donnes plus de travail pour rien à ton moteur de données et ton fichier Access pèse plus lourd.

Si tu prends l'Insee, ce sera pareil même s'il est limité à 5 caractères. Tu ne peux pas le mettre en numérique dans ta table et donc tu vas obliger Access à traiter du texte plutôt que des entiers longs.

Imaginons un cas simple. Tu gères une liste de produits qui sont catégorisés (Bois, Panneaux, Quincaillerie, Outillage,...) Tu respectes ma bonne pratique et tu utilises donc un entier long comme pk et fk... Bois possède la clé 1 et tu as donc x produits qui pointent vers cette catégorie. Durant la vie de ton appli, tu souhaites renommer la catégorie "Bois" en "Planches". Tu vas simplement dans ta table des catégories et tu changes le libellé (5 secondes, faisable par n'importe qui ne connaît pas le fonctionnement d'Acess, sans devoir faire sortir tout le monde de l'appli). Tu ne suis pas ma recommandation et tu utilises "Bois" comme clé primaire textuelle. Tu as donc x articles qui pointent vers "Bois"... Tu veux renommer "Bois" en "Planches"... Tu fais sortir tout le monde, tu vas dans les relations, tu coches la case "modification en cascade", tu vas modifier "Bois" en "Palette" puis tout le monde peut se remettre à travailler... Voilà pourquoi je le redis encore une fois: jamais de signification métier sur une clé primaire (or, le code Insee EST une signification métier).


D'une façon générale, si tu ne normalises pas ton approche, tu vas perdre du temps lors de la création et la maintenance des tables, puisque tu devras constamment aller voir quelle forme a la clé primaire vers laquelle tu pointes: Une fois ce sera du texte de 5 caractères pour l'Insee, une fois ce sera les x caractères de ton code article, une fois ce sera le nom de ta catégorie d'articles qui sera sur 20 caractères... Si tu prends systématiquement l'entier long autoincrémenté, tu ne poses pas de question, ta clé externe sera un entier long. C'est rapide, efficace, généralisé et systématisé. Le développement informatique déteste ce qui n'est pas normalisé et cette absence de normalisation est la cause de beaucoup de problèmes et de pertes de temps.

[marot_r]

Bonjour.

Pour la liste déroulante 36000 enregistrements c'est trop, Access commence généralement à ramer vers 1000.

Pour la sélection des communes, un recherche par le nom serait sans doute le plus utilisable, en affichant le nom et le numéro Insee (et le département) tu devrais éviter les problèmes de sélection.

Il y a des cours sur les formulaires de recherche ici (https://access.developpez.com/cours/...#formrecherche).

On peut aussi ruser un peu avec les listes pour restreindre le nombre d'enregistrements (ex : ne lancer vraiment la recherche que quand 5 lettres ont été tapées) mais si tu débutes je pense que les formulaires de recherches seront plus accessibles.

A+

[Pierre Fauconnier]

[...]
Pour ce qui est de la liste déroulante avec les communes, si j'ai 3600 communes dans ma plus grosse base de données (base de données communale à échelle régionale)[...]

René t'a indiqué la piste du formulaire de recherche, mais tu peux aussi adapter la liste par département, avec une liste déroulante indépendante de la table des contacts (si je reprends mon exemple) et qui restreint les lignes de la requête qui ramène les villes. Ca permet déjà pas mal de choses.

Ce que j'ai voulu montrer avec la liste déroulante placée sur le champ de la table, c'est qu'en travaillant à ce niveau-là, tu récupères la liste déjà formatée dans les formulaires et les requêtes. Et c'est dans le formulaire qu'il faudra un peu chipoter pour gère des listes moins volumineuses.

[La_Kai]

Okay, je vais plutôt me pencher vers la non-utilisation des codes INSEE, si c'est pour me faire perdre du temps à corriger les défauts que ça entraîne, c'est pas la peine d'essayer de les utiliser.
Mais sinon, petite question, pourquoi n'est-il pas possible de mettre les codes INSEE en format numérique ?

Pour ce qui est du formulaire de recherche par nom, ça me semble pas mal comme idée, je me pencherai sur la question en temps et en heure !

Je vais rendre visite à mon professeur de GBD cette après-midi, je pense qu'il pourra m'aiguiller sur certains points et questionnements également, vu qu'il connaît un peu plus précisément la nature de mes fichiers de travail.

Merci encore, et je reviendrai vers vous si je bloque encore sur quelque chose.

A bientôt !

[Pierre Fauconnier]

[...]
Mais sinon, petite question, pourquoi n'est-il pas possible de mettre les codes INSEE en format numérique ?[...]

C'est possible de les mettre en numérique, mais je ne le conseille pas.

D'une façon générale, je ne mets en numérique que ce sur quoi je suis susceptible de réaliser des opérations arithmétiques ou mathématiques. Autrement dit, lorsque la valeur est un nombre ( <> suite de chiffres). Pour les codes Insee, c'est comme pour les numéros de téléphone, on ne les additionne pas, il me semble => ce ne sont pas des nombres => texte.

De plus, pour les codes Insee comme pour les numéros de téléphones ou les codes postaux, certains commencent par le caractère 0 => Si tu les mets en numérique, tu vas perdre ce 0. Par exemple, les codes Insee de l'Ardèche deviendront 7xxx alors que ce devrait être 07xxx. Tu vas alors devoir "t'amuser" à ajouter le 0 devant dans les requêtes et autres, faisant travailler ton moteur de données pour rien.

Ma règle en la matière: On ne met en numérique que ce qui est susceptible d'être traité par des opérations arithmétiques.

[La_Kai]

Okay, merci pour la clarification

[marot_r]

Bonjour.

Juste un point avec les 0 au début des données : prendre garde à Excel qui a tendance à convertir tout ce qui ressemble à un nombre en nombre et donc à faire sauter les 0 au début.

Le problème n'est pas Access mais il est bon de le savoir.

A+

[Pierre Fauconnier]

Ca n'a rien à voir avec Excel. Si tu enregistres un Insee dans un champ numérique en Access, ton 0 à gauche fout le camp. idem pour un "champ" numéro de téléphone. Excel n'a strictement rien à voir là dedans.

Règle d'or: On réalise des opérations arithmétiques? => Oui, numérique. Non, Texte.

[marot_r]

Bonjour Pierre.

Ce n'étais pas une réponse à ton poste. C'était une mise en garde car il arrive souvent (en tout cas à moi) qu'on transfert des données dans Excel pour faire certaines manips qui sont pénibles en Access puis retour et au cours de ces transferts les 0 en débuts sont perdus. J'ai eu un cas intéressant où mon client avait les codes 005, 0005 et 5 qui désignaient 3 choses différentes au départ et qui lors d'un passage par Excel avaient été confondues, tous ramenés à 5.

A+