Discussion :

[moimp]

Bonjour à tous,
Je ne suis pas sûr d'être sur le bon forum car ma question concerne PHP pour les vérifications et MySQL.
J'ai des données de contacts importées dans un tableau T.
Ces données sont à ventiler dans deux tables: une table dat_addresses et une table dat_members avec une colonne id_address (il peut y avoir plusieurs membres à la même adresse.)
Avant enregistrement, je souhaite vérifier l'absence de doublons dans les deux tables.
Si j'ai un doublon d'adresse je dois récupérer l'id de la table dat_addresses pour l'intégrer avec le nouveau membre si celui-ci n'est pas un doublon.
Si j'utilise le mot clef IGNORE, comment savoir quel clef du tableau T est concernée.

Suis-je assez clair? J'ai des doutes.

[escartefigue]

S'il s'agit de charger des données en masse dans une table, il est recommandé d'utiliser LOAD DATA INFILE beaucoup plus rapide que l'ajout par INSERT.
Pour que les doublons ne soient pas chargés, il faut activer l'option IGNORE. Evidemment, il faut que la ou les colonnes concernées fassent l'objet d'une contrainte UNIQUE.

[moimp]

Merci pour ce conseil. J'ai étudié ce tuto sur le sujet et je rencontre cependant plusieurs difficultés.

1. Les données sont à ventiler sur deux tables différentes. Il ne peut donc y avoir de clef unique. Par exemple l'adresse fait partie d'une table 'address' alors que le nom et le prénom font partie d'une table 'members'.
2. Le fichier à importer peut être plus ou moins complet et il doit être traiter avec des répétitions du séparateur pour les données manquantes.
3. J'ai un formulaire PHP qui pour chaque champ de la base cible sélectionne le champ csv à mettre en correspondance avant importation.
4. Est-ce que la méthode LOAD DATA INFILE permet d'ajouter les nouvelles données sans écraser les précédentes.

Il me semble que dans ce cas je devrais effectuer les traitements en PHP avant d'insérer mes données quelque soit la méthode employée.

[Séb.]

Si j'ai un doublon d'adresse je dois récupérer l'id de la table dat_addresses pour l'intégrer avec le nouveau membre si celui-ci n'est pas un doublon.

Si je comprends bien l'ID adresse n'est pas dans le fichier mais est généré au moment de l'insertion dans dat_addresses.
Tu ne connais donc pas address_id lors de l'import de masse.

2 possibilités :
1. Tu fais un traitement en PHP qui insère les bonnes données comme il faut
2. Tu fais du pur SQL avec un table temporaire ayant la même structure que ton fichier create temporary table data_tmp (...), tu y charges ton fichier avec load data, et tu utilises du SQL pour insérer/màj tes 2 tables finales selon la table temporaire, à la fin du traitement la table temporaire sera automatiquement détruite

Dur d'en dire davantage car tu ne donnes aucune infos précises

[moimp]

Si je comprends bien l'ID adresse n'est pas dans le fichier mais est généré au moment de l'insertion dans dat_addresses.
Tu ne connais donc pas address_id lors de l'import de masse.

C'est exact.

2 possibilités :
1. Tu fais un traitement en PHP qui insère les bonnes données comme il faut
2. Tu fais du pur SQL avec un table temporaire ayant la même structure que ton fichier create temporary table data_tmp (...), tu y charges ton fichier avec load data, et tu utilises du SQL pour insérer/màj tes 2 tables finales selon la table temporaire, à la fin du traitement la table temporaire sera automatiquement détruite

J'aurais tendance à privilégier la solution PHP que je maitrise mieux mais peut-être que la solution SQL est préférable ou plus facile.

Dur d'en dire davantage car tu ne donnes aucune infos précises

A la soumission, mon formulaire génère une variable $_POST['importFields'] comme ceci:

C:\wamp64\www\proginet\appOmnes\controller\cardFormHandler.php:567:
array (size=11)
  'address' => string '2' (length=1)
  'country' => string '3' (length=1)
  'zipcode' => string '4' (length=1)
  'locality' => string '5' (length=1)
  'homephone' => string '' (length=0)
  'familyname' => string '0' (length=1)
  'firstname' => string '1' (length=1)
  'email' => string '6' (length=1)
  'GSM' => string '' (length=0)
  'workphone' => string '' (length=0)
  'birthdate' => string '' (length=0)

Les valeurs du tableau correspondent au rang du champ (colonne) CSV. Les champs address à homephone vont dans la table dat_addresses et les suivants dans la table dat_members.

Les données récupérées sont de la forme:

C:\wamp64\www\proginet\appOmnes\controller\cardFormHandler.php:567:
array (size=7)
  0 => 
    array (size=7)
      0 => string 'Nom individu 1' (length=19)
      1 => string 'Sophie' (length=6)
      2 => string 'N'importe quelle adresse' (length=33)
      3 => string 'France' (length=6)
      4 => string '00000' (length=5)
      5 => string 'Une ville' (length=22)
      6 => string '' (length=0)
  1 => 
    array (size=7)
      0 => string 'Nom individu 2' (length=5)
      1 => string 'Pierre' (length=11)
      2 => string '39 rue Thomassin' (length=28)
      3 => string 'France' (length=6)
      4 => string '69002' (length=5)
      5 => string 'Lyon' (length=4)
      6 => string 'nom@fai.com' (length=25)

Même en PHP, j'ai des difficultés à organiser et ventiler les données.

[Séb.]

Code :

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

1
2
3
4
5
  'address' => string '2' (length=1)
  'country' => string '3' (length=1)
  'zipcode' => string '4' (length=1)
  'locality' => string '5' (length=1)
  'homephone' => string '' (length=0)

Je suppose donc que la clef UNIQUE est { address, country, zipcode, locality }.

homephone à l'adresse, tu es sûr ? Différentes personnes dans différents logements à la même adresse, chacune avec son numéro. Ce n'est pas géré.

Je ferais ça sans hésiter en SQL pur. Donne un échantillon de données et la structure de tes tables si tu veux que je te montre.

Code :

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

1
2
show create table dat_addresses;
...

[moimp]

Code :

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

1
2
3
4
5
  'address' => string '2' (length=1)
  'country' => string '3' (length=1)
  'zipcode' => string '4' (length=1)
  'locality' => string '5' (length=1)
  'homephone' => string '' (length=0)

Je suppose donc que la clef UNIQUE est { address, country, zipcode, locality }.

homephone à l'adresse, tu es sûr ? Différentes personnes dans différents logements à la même adresse, chacune avec son numéro. Ce n'est pas géré.

Non, la clef unique comprend aussi les colonnes id_user et homephone car plusieurs utilisateurs peuvent utiliser la même adresse et dans le cas de plusieurs logements à la même adresse, c'est justement le téléphone qui peut différencier les logements.

Je ferais ça sans hésiter en SQL pur. Donne un échantillon de données et la structure de tes tables si tu veux que je te montre.

Code :

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

1
2
show create table dat_addresses;
...

Résultat de show...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE `dat_addresses` (
  `id_user` int(10) unsigned NOT NULL,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `address` varchar(80) NOT NULL,
  `country` varchar(2) NOT NULL,
  `zipcode` varchar(10) NOT NULL,
  `locality` varchar(50) NOT NULL,
  `homephone` varchar(20) DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `selection` tinyint(1) NOT NULL DEFAULT '0',
  `update_date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE` (`id_user`,`address`,`country`,`zipcode`,`locality`,`homephone`),
  KEY `id_user` (`id_user`)
) ENGINE=InnoDB AUTO_INCREMENT=86 DEFAULT CHARSET=utf8
 
CREATE TABLE `dat_members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_address` int(10) unsigned NOT NULL,
  `firstname` varchar(50) DEFAULT NULL,
  `familyname` varchar(50) DEFAULT NULL,
  `email` varchar(60) DEFAULT NULL,
  `workphone` varchar(20) DEFAULT NULL,
  `GSM` varchar(20) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `relation` int(10) unsigned NOT NULL COMMENT '0-contact, 1-conjoint, 2-enfant, 3-enfant_du_cjt',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`id_address`,`firstname`,`familyname`) USING BTREE,
  KEY `id_address` (`id_address`),
  CONSTRAINT `dat_members_ibfk_1` FOREIGN KEY (`id_address`) REFERENCES `dat_addresses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=324 DEFAULT CHARSET=utf8

[escartefigue]

Les tables sont mal modélisées :

Par exemple, pour la table dat_adresses

• une seule ligne adresse de 80 caractères c'est très insuffisant. La norme de la poste prévoit jusqu'à 6 lignes de 38 caractères ;
• le code pays n'a rien à faire dans l'adresse, car plusieurs adresses partagent le même pays. Il faut donc l'externaliser dans une table des pays (identifiant, code, libellé...) en lien avec l'adresse. De plus, du varchar(2) est une aberration, à remplacer par du char fixe. Il est recommandé d'utiliser les codes pays ISO 3166 (deux codifications possibles : char(2) et char(3)) ;
• de même pour la ville et pour le code postal qui l'un comme l'autre doivent faire l'objet d'une table spécifique pour éviter les redondances ;
• le téléphone n'a rien à faire dans la table des adresses : soit chaque personne possède un et un seul téléphone, auquel cas il faut le positionner dans la table des membres, soit chaque personne peut avoir plusieurs téléphones, auquel cas il faut créer une table des téléphones en lien avec la personne et - facultativement - une table des types de téléphones, en lien avec la table des téléphones, pour savoir s'il s'agit d'un fixe domicile, fixe bureau, portable pro, portable d'astreinte, portable perso...
• avoir des noms différents pour un même attribut en fonction de la table où il se trouve ne facilite ni la compréhension, ni les études d'impact.
  
  Ainsi, "id" dans "dat_adresses" correspond à "id_adresse" dans la table "dat_members". Il serait bien préférable d'appeler cette colonne "id_adresse" partout ;
• la contrainte d'intégrité est mal construite : c'est l'adresse qui doit faire référence au membre et non l'inverse. Ainsi, si on supprime un membre, on doit aussi supprimer son adresse.
  De plus, si un membre peut avoir plusieurs adresses, alors il est recommandé d'identifier l'adresse relativement au membre : la PK de l'adresse devient alors id_membre augmenté d'un chrono (+1 à chaque nouvelle adresse d'un même membre). Si on définit un index cluster sur cette PK, les performances en sont augmentées ;
• la contrainte unique sur la combinaison adresse + téléphone est une curiosité, à expliquer et probablement à supprimer.

Vous trouverez dans ce fil de discussion un exemple de modélisation des adresses, villes et code postaux.

[Séb.]

id_user et homephone car plusieurs utilisateurs peuvent utiliser la même adresse et dans le cas de plusieurs logements à la même adresse, c'est justement le téléphone qui peut différencier les logements.

Cela me paraît douteux. Et si l'utilisateur change de numéro ? Et si l'utilisateur n'a pas de numéro ? Et si plusieurs utilisateurs à la même adresse mais dans des logements différents n'ont pas de numéro ? Ou dans le même logement mais avec chacun un numéro différent (2 lignes fixes, ça existe). Que se passe-t-il si 2 utilisateurs dans le même logement orthographient différemment l'adresse ?
Tu veux identifier quoi au final ? Un logement ou une adresse ? Ce n'est pas du tout la même chose, et identifier un logement est illusoire car il n'existe pas de plan de numérotation au logement officiel.
Je suis bien conscient qu'il est difficile de travailler sur des fichiers non produits avec nos impératifs, et que cela peut impliquer des choix drastiques et des imprécisions que nous devons accepter.

[moimp]

Je vais essayer d'apporter quelques précisions avant de répondre point par point.
Mon application est un annuaire personnel multi-utilisateurs. Chaque utilisateur gère ses propres données.
L'adresse est le logement d'un foyer (en principe une famille). C'est la raison pour laquelle, j'ai une adresse où logent plusieurs personnes, typiquement les parents et leurs enfants (membres de la famille). C'est la raison pour laquelle j'ai une adresse avec plusieurs membres.

une seule ligne adresse de 80 caractères c'est très insuffisant. La norme de la poste prévoit jusqu'à 6 lignes de 38 caractères ;

Je prends note, j'augmente. A propos, comment enregistrer au mieux les sauts de lignes en base de données?

le code pays n'a rien à faire dans l'adresse, car plusieurs adresses partagent le même pays. Il faut donc l'externaliser dans une table des pays (identifiant, code, libellé...) en lien avec l'adresse. De plus, du varchar(2) est une aberration, à remplacer par du char fixe. Il est recommandé d'utiliser les codes pays ISO 3166 (deux codifications possibles : char(2) et char(3)) ;

C'est bien le cas: Les deux caractères correspondent effectivement au code pays ISO 3166 qui sert d'identifiant. Une table séparée contient la liste des pays avec pour chaque code, le libellé du pays en plusieurs langues.
J'ai remplacé le varchar(2) par un char(2). Merci pour cette remarque.

de même pour la ville et pour le code postal qui l'un comme l'autre doivent faire l'objet d'une table spécifique pour éviter les redondances ;

J'en prends note mais je ne sais pas trop comment le gérer à l'international.

le téléphone n'a rien à faire dans la table des adresses : soit chaque personne possède un et un seul téléphone, auquel cas il faut le positionner dans la table des membres, soit chaque personne peut avoir plusieurs téléphones, auquel cas il faut créer une table des téléphones en lien avec la personne et - facultativement - une table des types de téléphones, en lien avec la table des téléphones, pour savoir s'il s'agit d'un fixe domicile, fixe bureau, portable pro, portable d'astreinte, portable perso...

Je suis parti du principe que, s'agissant d'un annuaire personnel, le téléphone fixe est lié au logement et donc ici à l'adresse alors que les autres téléphones sont liés au membre.

avoir des noms différents pour un même attribut en fonction de la table où il se trouve ne facilite ni la compréhension, ni les études d'impact.

Ainsi, "id" dans "dat_adresses" correspond à "id_adresse" dans la table "dat_members". Il serait bien préférable d'appeler cette colonne "id_adresse" partout ;

J'ai adopté la convention suivante: Lorsqu'il s'agit de la clef primaire de la table, il s'appelle id et, dans les autres tables je précise la table dont il est la clef primaire. Si c'est vraiment nécessaire, je peux modifier.

la contrainte d'intégrité est mal construite : c'est l'adresse qui doit faire référence au membre et non l'inverse. Ainsi, si on supprime un membre, on doit aussi supprimer son adresse.

Dans cette application spécifique, non. Un membre peut quitter le foyer et les autres membres restent à cette adresse.

la contrainte unique sur la combinaison adresse + téléphone est une curiosité, à expliquer et probablement à supprimer.

Compte tenu de ce qui précède, je me pose la question car le téléphone peut permettre d'éviter les doublons mais d'un autre côté, il n'est pas obligatoire.

Vous trouverez dans ce fil de discussion un exemple de modélisation des adresses, villes et code postaux.

J'ai vu mais compte tenu de ce qui précède ce n'est pas le même cas.

[escartefigue]

Bonjour,

A propos, comment enregistrer au mieux les sauts de lignes en base de données?

C'est du ressort du traitement, pas des données, donc à gérer coté applicatif.

C'est bien le cas: Les deux caractères correspondent effectivement au code pays ISO 3166 qui sert d'identifiant. Une table séparée contient la liste des pays avec pour chaque code, le libellé du pays en plusieurs langues.
J'ai remplacé le varchar(2) par un char(2). Merci pour cette remarque.

OK, j'aurais toutefois privilégié un identifiant technique asémantique, donc stable. En effet, la norme ISO3166 pourrait évoluer et modifier des valeurs, ce qui impacterait toutes les tables ayant cette colonne comme PK ou FK alors qu'un identifiant technique est à l'abri de ce type de mésaventures Utilisez la valeur ISO comme simple colonne non PK, mais avec une contrainte unique.

J'en prends note mais je ne sais pas trop comment le gérer à l'international.

De la même façon, seule la longueur des attributs, en particulier du code postal, peut être impactée.

Je suis parti du principe que, s'agissant d'un annuaire personnel, le téléphone fixe est lié au logement et donc ici à l'adresse alors que les autres téléphones sont liés au membre.

Ce n'est pas une bonne idée : à une même adresse, si le résident change (déménagement) le numéro de téléphone change aussi. D'ailleurs ça fait longtemps qu'on peut conserver son numéro de fixe même quand on déménage
D'ailleurs, on peut avoir plusieurs fixes à une même adresse, non seulement pour les entreprises, mais aussi pour les particuliers.
Le numéro de téléphone est donc bien lié à la personne et non à l'adresse.

J'ai adopté la convention suivante: Lorsqu'il s'agit de la clef primaire de la table, il s'appelle id et, dans les autres tables je précise la table dont il est la clef primaire. Si c'est vraiment nécessaire, je peux modifier.

Ce n'est pas nécessaire, mais tellement plus pratique à l'usage lors d'études d'impact.
D'ailleurs, les logiciels de modélisation propagent par défaut le même nom dans toutes les tables : si c'est "id_personne" la PK de la table "personne" alors cette colonne devenue FK dans toute autre table s'appellera également par défaut "id_personne". Seul le cas particulier d'association réflexive où une colonne est présente deux fois nécessite un renommage, en ce cas, il est préférable d'aouter un suffixe pour conserver le nom et faciliter les études d'impact.
Par exemple, pour le cas d'une relation parent-enfant, on aura une table des personnes ayant pour PK "id_personne" et une table associative ayant pour FK "id_personne_parent" et "id_personne", toutes deux faisant référence à "personne.id_personne".

J'ai vu mais compte tenu de ce qui précède ce n'est pas le même cas.

Pour la gestion des villes et des codes postaux, l'exemple de MCD que j'ai fourni reste applicable

[moimp]

Je ne m'en sors pas et je reviens sur le #5 de ce fil.
Je n'arrive pas à préparer mes données pour les avoir dans le bon ordre. Même si j'ai adapté mes tables en prenant en compte vos remarques les exemples de codes de ce #5 restent globalement valables.
En PHP, je n'arrive pas à mettre mes données dans l'ordre des colonnes de chaque table.