Dédoublonnage des inscriptions : modèle, fusion et index

Schéma proposé :

• etudiant(id PK, nom, prenom, email_normalise UNIQUE, telephone)
• formation(id PK, code UNIQUE, libelle)
• inscription(id PK, etudiant_id FK → etudiant, formation_id FK → formation, annee, date_inscription, UNIQUE (etudiant_id, formation_id, annee))

Anomalies supprimées, cas par cas :

• Mise à jour : corriger « but informatique » se fait sur une seule ligne de formation, au lieu de 6 000 lignes. C'est l'anomalie la plus coûteuse décrite dans le texte.
• Insertion : un étudiant peut exister sans inscription ; les 12 étudiants à deux emails deviennent un seul étudiant avec deux inscriptions.
• Redondance : nom et email ne sont plus répétés à chaque inscription. Un changement de formation crée une nouvelle ligne reliée au même etudiant_id, ce qui produit l'historique demandé.
• Intégrité : UNIQUE (etudiant_id, formation_id, annee) interdit la double saisie, et les clés étrangères interdisent une inscription orpheline.

Choix assumés. email reste un attribut avec index unique, pas une clé primaire : un étudiant peut changer d'adresse, et l'identifiant doit rester stable. Si plusieurs téléphones sont possibles, ils sortent dans une table telephone (première forme normale). La normalisation n'est pas un dogme : elle est justifiée ici par des anomalies mesurées.

Non, pas de fusion automatique. 312 paires de nom et prénom identiques sur 24 000 lignes correspond à un ordre de grandeur normal d'homonymes. Fusionner à tort mélangerait les inscriptions et les notes de deux personnes ; l'erreur est difficilement réversible et le coût est sans commune mesure avec une vérification manuelle de 312 cas.

Procédure en trois temps.

1. Candidats forts - même email normalisé : SELECT lower(trim(email)) AS mail, count(*), array_agg(id) FROM inscription WHERE email IS NOT NULL GROUP BY 1 HAVING count(*) > 1; Ces groupes sont quasi certains et peuvent être pré-remplis.
2. Candidats faibles - même nom+prénom mais emails différents : GROUP BY lower(trim(nom)), lower(trim(prenom)) HAVING count(DISTINCT lower(trim(email))) > 1. À traiter manuellement, avec une preuve complémentaire si elle existe (date de naissance, INE, téléphone).
3. Traçabilité - table correspondance(source_id, etudiant_id, motif, traite_par, date) et conservation des lignes d'origine dans une table d'archive, pour que toute fusion soit annulable et auditable.

Règle de fond : l'algorithme propose, un humain valide. L'automatisation ne porte que sur les cas certains, et chaque décision laisse une trace.

Ce qu'un index fait : il accélère la recherche et le tri sur les colonnes indexées en évitant un parcours complet. Ce qu'il ne fait pas : il ne supprime ni la redondance, ni les libellés incohérents, ni les doublons. Ici, le problème est un problème de modèle, pas de performance : un index sur formation dans la table plate accélérerait un WHERE formation = 'BUT Info' qui raterait les deux autres orthographes. On gagne du temps pour trouver une réponse fausse.

Index utiles sur le nouveau schéma :

• UNIQUE (email_normalise) sur etudiant : la contrainte et l'index ne font qu'un. En PostgreSQL, plusieurs NULL sont autorisés, ce qui convient aux étudiants sans email ; un index partiel WHERE email_normalise IS NOT NULL suffit si le volume de NULL est important.
• Index sur inscription(etudiant_id) et inscription(formation_id) : PostgreSQL n'indexe pas automatiquement les clés étrangères, et sans cela les jointures et les suppressions en cascade se dégradent.
• Composite (formation_id, annee) pour les états par formation et par année.
• À éviter : un index simple sur annee (6 valeurs) ou sur un statut peu sélectif. Chaque index ralentit les écritures et occupe de l'espace : un index se justifie par une requête réelle, pas par principe.