Comment modeliser cette structure ?

Bonjour;

J'aurais besoin de votre aide pour modéliser la situation suivante :

Je dois importer un fichier csv vers une base MYSQL, afin d'y effectuer des traitements : CRUD, filtrage....

Ne connaissant pas le type ni le nombre des colonnes du fichier source, je ne peux pas faire une table statique.

J'utilise donc, pour le moment, cette solution que je trouve moche :

Admettons que mon fichier a la structure suivante :

"Nom", "Prenom", "mail"
"Dupont", "Fabrice", "fabrice@developpez.com"
...

------------------------------------------------------------------------

j'ai une table "mapping" qui décrit les colonnes du fichiers csv.
elle contient les champs,nom des colonnes, ...

Table mapping:

id | colonne_1 | colonne_2 | colonne_3 | ...... jusqu'à 30
1 | nom::string | prenom::string | mail::email-adress | ....


Table contacts :
id | mapping_id | colonne_1 | colonne_2 | colonne_2 | ..... jusqu'à 30
1 | 1 | Dupont | Fabrice | fabrice@developpez.com |...


Bien que cette solution fonctionne trés bien, il y des inconveniants :
- Il faut limiter le nombre de colonnes (30 dans mon cas) alors qu'un fichier peut contenir plus que 30 colonnes !

- chaque fois que je veux extraire le type d'une colonne, je doit faire 2 requetes + des explodes (PHP)....

- Je la trouve franchement moche, et je pense qu'on peut faire mieux.



POUVEZ vous m'aider svp.

Une autre solution c'est d'ajouter les champs que tu trouves dans ton .csv dans une table "champ" et dans une autre table tu mets les valeurs que tu trouves dans ton fichier:

table "champ"
id_champ | libellé_champ

table "fichier"
id_fichier | nom_fichier

table "valeur_fichier"
id | id_fichier | id_champ | valeur

/!\ dans cette solution on stocke les valeurs seulement sous forme de chaîne de caractères

Merci pour ta réponse rapide !

C'est le schéma qui saute au yeux en effet, mais dans mes fichier csv il y a de 10 000 à 1 000 000 de lignes ! imagine le casse tête pour faire du crud avec ce schéma, en plus le nombre d'enregistrements dans la table "valeur_fichier" sera multiplié par le nombre de champs..

Nombre d'enregistrements par solution : (fichier de 20 colonnes et 10 000 lignes)

Solution 1 :
table mapping : 1
table contacts : 20 000 !


Solution 2 :
table champs : 20
table fichier : 1
table valeur_fichier : 200 000 lignes !


En gros j'ai 2 choix :
Utiliser une structure horizontale :
- Limite le nombre de colonnes d'un fichier
- moche ! (je vois ça comme un inconvénient).

+ Optimise le nombre d'enregistrements
+ CRUD rapide

ou bien
Utiliser une structure verticale
- trop d'enregistrements
- case tète pour le CRUD.

+ Solution structurée et normalisée (pas moche :aie:).



Y aurait il une 3éme solution qui combinerait les avantages des 2 premières ?

La 3ème solution, c'est d'avoir une table pour laquelle l'application modifie dynamiquement les colonnes pour en ajouter si nécessaire.
C'est comme ça que fonctionne Drupal6 (module CCK) pour gérer les types de contenus définis par l'utilisateur.

Il me semble que dans Drupal7 le choix a été de créer une table pour chaque "attribut" (on est pas loin de la première proposition que j'avais faite sauf qu'au lieu d'avoir 1 table avec n attributs x m valeurs, on a n tables avec m valeurs)
Drupal6 procède de la même façon seulement pour les attributs multivalués (relation 1..n).

Bref, il faut voir si une telle architecture est réalisable pour toi et ensuite valider avec des tests de performances...

Merci.

Pour moi, Il est hors de question de jouer avec les structures des tables.

j'ai besoin de contrôler ma base, en plus imagine que j'ai 200 clients sur mon système, chaque un se connecte en fin de soirée et importe 5 fichiers de structures différentes.
Je me lève le matin , allume mon pc, regarde la BDD => crise cardiaque :aie:

Bon je pense que je vais garder ma solution pour le moment, en esperant trouver mieux en terme de normalisation, car en terme de performance, c'est la meilleure solution.


Encore merci montesq pour ton aide.
n'hésiter pas à poster vos solutions.

Je me suis penché sur la partie "modélisation", mais finalement pas sur l'aspect "besoin".
En effet, en relisant ton premier post, je ne suis pas sûr de vraiment comprendre ton besoin.
En effet, supposons que l'on ait 2 utilisateurs différents, le premier envoie son fichier, la 3ème colonne a le libellé "email", le second envoie son fichier, la 4ème colonne a le libellé "mail".
Au final, en base de données tu as les mails du premier utilisateurs dans la 3ème colonne mais ceux du 2ème utilisateurs dans la 4ème!? A quoi cela va-t'il te servir?

C'est vrai que j'ai pas vraiment explique mon besoin :

en gros, un utilisateur peut:

- Importer un/des fichiers csv
(Je ne connais pas le contenu des fichiers ! généralement, ce sont des fichiers de contacts, avec des champs: nom, prénom, mail, adresse....
mais je n'impose aucune restriction sur le contenu.)


- faire des traitements sur les enregistrements : CRUD, filtrage, tri,dédoublage, nettoyage des adresses mails...

- Je n'ai pas besoin d'avoir toutes les adresses mails dans une seule colonne.
Par contre j'ai besoin de savoir, pour un fichier donné, ou sont stockées les adresses mails !

En gros t'a solution me convient très bien du point de vue modélisation mais point de vue traitements : elle est très gourmande en requêtes SQL.


pour faire un UPDATE, actuellement j'ai besoin d'une seule requête qui touche une seule ligne de la table contacts, mais si j'ai bien compris ta solution, j'aurais besoin de faire autant de requêtes que de colonnes !

30 colonnes = 30 requêtes SQL pour mettre à jour un seul contact !
c'est pas vraiment le top coté performances !

tu me rejoins sur ce point ?

oui je suis entièrement d'accord avec toi.
Dernière question toutefois, as-tu vraiment besoin de stocker ces données en bases de données?
Car une fois les traitements de ces données effectués cette table ne te sert plus à rien!

Bien sure que si ! mais ce n'était pas dans mon poste de début :oops:

Suite des besoins:
Après avoir nettoyer sa base, l'utilisateur peut créer des segments (des contacts ayant des adresses mails valides par exemple) , envoyer des campagnes d'emailing, sms,...... et peut exporter sa base propre à tout moment sous format csv, xml, html...

Il y a beaucoup de traitement sur les contacts , c'est pour ça que j'avais besoin d'une structure solide et adaptable et de minimiser au maximum le nombre de requêtes sql, (n'oublie pas que je travaille avec php+mysql 8O)
mais je vais juste limiter le nombre de colonnes pour le moment.

Si un client veut importer + de 30 colonnes, qu'il aie se faire...... :aie:

Personnellement, j'aurais réglé le problème en analysant au maximum les données standards dont l'utilisateur aura besoin, par exemple si c'est une sorte de carnet d'adresse:
nom, prénom, mail, date de naissance, adresse...

Et après réserver 2/3 colonnes (voire plus) pour enregistrer les données avec un contexte utilisateur particulier (ou utiliser le modèle évoqué plus tôt dans la mesure où l'on devrait avoir grandement réduit le nombre de colonnes non gérées)

Ainsi au moment de l'import tu demandes à l'utilisateur de faire le mapping avec les colonnes "standards" de ta bdd.

J'utilise une appli web professionnelle qui fonctionne de cette manière. Bien sûr, c'est plus compliqué que ta solution, mais tu as au moins une table qui ressemble à quelque chose...

Super montesq, tu a bien compris mon besoin, et je pense que tu va me fournir une belle solution. sachant que :

- je fais bien le mapping avant l'import( les clients ne sont pas obligés d'importer la totalité des colonnes, juste ceux dont il auront besoin pour l'appli...)

J'ai bien des colonnes standards : nom, prénom, téléphone,...

"
Et après réserver 2/3 colonnes (voire plus) pour enregistrer les données avec un contexte utilisateur particulier"

c'est plutôt 10 ou 20 colonnes de plus. et c'est la tout le problème.
comment modéliser "le contexte particulier" c'est a dire les colonnes spécifiques à chaque client ?

J'assigne de base 20 colonnes supplémentaires, je les nomme "colonne_1", "colonne_2", .... puis j'utilise une autre table pour décrire ces colonnes.
c'est ma solution initiale moche !!!! on n'est pas sorti de l'auberge.

Salut,
Vous pouvez regarder des solutions NoSQL telles que CouchDB qui permettent de répondre à ce type de besoins.

Si vous voulez rester dans le monde SGDB-R. Il faut modéliser un peu.

Transformez (mentalement) vos CSV en structures clés: valeurs.
Cà ressemble à { k1: v1, k2: v2, .... } (et c'est représentable facilement en JSON).
Tous les attributs - les Ki - ne méritent sans doute pas d'être 'colonnes', seulement ceux sur lesquels s'appuieront des selects.
Parmi eux, un sous ensemble pourra définir l'identité d'un enregistrement i.e. permettre la gestion des duplications et des mises à jour.

Que faire des autres champs: on les jette ou on les garde?
L'étape précédente définit les colonnes et un ensemble d'attributs qu'on veut récupérer des CSV... Pour les autres, pourquoi ne pas stocker leur ensemble dans une colonne texte/blogs/...

Voilà pour l'idée de départ...
Reste à construire à la volée la table qui recevra les lignes en précisant les "colonnes" et celles qui définissent la relation d'identité.
Mais bon CSV = texte: çà simplifie quand même bien les choses.

- W

Salut,

Citation:

---

j'ai besoin de contrôler ma base, en plus imagine que j'ai 200 clients sur mon système, chaque un se connecte en fin de soirée et importe 5 fichiers de structures différentes.
Je me lève le matin , allume mon pc, regarde la BDD => crise cardiaque

---

Si on veut de la flexibilité, il est plus sympa de créer les tables 'à la volée'.
Ensuite il faut certes éviter la crise cardiaque.
Mais, je n'ai pas trop compris si s'agissait de partager des informations ou des services de traitements. i.e a-t-on 200*5 "tables" ou 200 utilisateurs qui doivent partager les informations qu'ils rassemblent dans 5 "tables".
Note: J'imagine que c'est sans doute un peu des deux, mais ou est placé le curseur?
- W

On a en effet 200*5 "tables" !!!!
c'est pour ça que la création a la volée est hors de question pour moi.

Citation:

---

Envoyé par matamine

On a en effet 200*5 "tables" !!!!
c'est pour ça que la création a la volée est hors de question pour moi.

---

Si les données sont "privées" à chaque utilisateur, quels avantages - autre que faciliter le déploiement des applis - à créer ces "tables" sur une BDD "partagée" plutôt que "locale" au poste de travail?
- W
PS: De toutes façons, il faudra bien créer un paquet de tables avec les bonnes informations "dedans".
Reste à savoir quelle sera leur durée de vie, les "options" côte gestion des changements et les implications sur le boulot à faire pour les réaliser.

J'ai travaillé à l'INRA sur une application permettant de faire des statistiques sur des données importées de sources diverses.

Le coeur du système était une base de méta-données.

L'utilisateur importe des données à partir d'un fichier texte dans une table.
Le logiciel identifie chaque entête de colonne et demande confirmation de la structure, autorisant l'utilisateur à ajouter des informations sur la nature des colonnes.
Il paramètre également les droits d'accès à ces données et tout est enregistré en base de méta-données.
Le logiciel utilise ensuite les méta-données pour accéder aux données réelles.

Est-ce que ce principe correspondrait davantage à ton besoin ?

Citation:

---

Envoyé par CinePhil

J'ai travaillé à l'INRA sur une application permettant de faire des statistiques sur des données importées de sources diverses.

Le coeur du système était une base de méta-données.

L'utilisateur importe des données à partir d'un fichier texte dans une table.
Le logiciel identifie chaque entête de colonne et demande confirmation de la structure, autorisant l'utilisateur à ajouter des informations sur la nature des colonnes.
Il paramètre également les droits d'accès à ces données et tout est enregistré en base de méta-données.
Le logiciel utilise ensuite les méta-données pour accéder aux données réelles.

Est-ce que ce principe correspondrait davantage à ton besoin ?

---

Ca semble effectivement répondre à la problématique de notre ami, mais tu ne nous dis pas comment était organisée cette base de méta-données...

Grosso-modo, de mémoire et vite fait...

zone_depot (zd_id, zd_nom, zd_id_proprietaire) <== Représente la BDD où sont stockées les données importées

import (imp_id, imp_id_zone_depot, imp_nom_table) <== Représente la table de données externes importées et stockée dans la zone dépôt.

variable (var_id, var_id_import, var_nom_colonne, var_type, ...) <== Représente les colonnes de la table de données importées.

C'était bien sûr plus complet que ça, avec la gestion des utilisateurs par groupes, la gestion des droits sur les données, l'enregistrement des études statistiques réalisées sur les données avec leurs paramètres...

Encore et toujours le même problème d'un schéma de base de données dynamique, c-a-d non anticipé. Voir les discussions là http://www.developpez.net/forums/d80...ynamique-base/

Citation:

---

Envoyé par Hephaistos007

Encore et toujours le même problème d'un schéma de base de données dynamique, c-a-d non anticipé. Voir les discussions là http://www.developpez.net/forums/d80...ynamique-base/

---

Heu?!?

Il me semblait qu'ici le sujet était justement d'éviter cela en essayant de mettre en correspondance les schéma "utilisateurs" avec ceux de la base.

C'est un problème de gestion de meta données qu'on retrouve plus généralement dans les ETL - qui sont peut être un peu trop lourds à mettre en oeuvre dans ce cas.
- W