IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes MySQL Discussion :

Splitter une table obèse en plusieurs tables, relation 1 à n


Sujet :

Requêtes MySQL

  1. #1
    Membre à l'essai
    Profil pro
    Inscrit en
    août 2004
    Messages
    26
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : août 2004
    Messages : 26
    Points : 10
    Points
    10
    Par défaut Splitter une table obèse en plusieurs tables, relation 1 à n
    Bonjour à tous,

    Je reste totalement bloqué sur qqch qui pourtant, à priori, me semble simple :

    Soit une table obèse, regroupant des informations variées n'ayant rien à faire ensemble telle que suit :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    CREATE TABLE `bazar` (
      `lot` int(11) NOT NULL AUTO_INCREMENT,
      `entree` timestamp NOT NULL DEFAULT current_timestamp(),
      `sortie` date DEFAULT NULL,
      `civilite` varchar(5) NOT NULL,
      `nom` varchar(100) NOT NULL,
      `prenom` varchar(100) NOT NULL,
      `ad1` varchar(100) NOT NULL,
      `ad2` varchar(100) NOT NULL,
      `ad3` varchar(100) NOT NULL,
      `ville` varchar(100) NOT NULL,
      `codpost` varchar(5) NOT NULL,
      PRIMARY KEY (`lot`),
    )
    Vous noterez qu'une personne (civilité + nom+ prénom ) ne peut, en l'état, ainsi avoir qu'une adresse (ad1, ad2, ad3, ville, codpost) et un dossier (lot, entrée, sortie)

    Le besoin est de splitter tout ceci en 3 entités : personne, adresse & dossier, en prenant en compte qu'une personne peut avoir n adresses et n dossiers.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE `personne` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `civilite` varchar(5) CHARACTER SET utf8,
      `nom` varchar(100) CHARACTER SET utf8 NOT NULL,
      `prenom` varchar(100) CHARACTER SET utf8 NOT NULL,
      PRIMARY KEY (`id`)
    )
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE TABLE `adresse` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `personne_id` int(11) NOT NULL,
      `ad1` varchar(100) CHARACTER SET utf8 NOT NULL,
      `ad2` varchar(100) CHARACTER SET utf8 NOT NULL,
      `ad3` varchar(100) CHARACTER SET utf8 NOT NULL,
      `ville` varchar(100) CHARACTER SET utf8 NOT NULL,
      `codpost` varchar(5) CHARACTER SET utf8 NOT NULL
      PRIMARY KEY (`id`)
    )
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    CREATE TABLE `dossier` (
      `lot` int(11) NOT NULL DEFAULT 0,
      `personne_id` int(11) NOT NULL,
      `entree` timestamp NOT NULL DEFAULT current_timestamp()
      PRIMARY KEY (`lot`)
    )

    Là ou les choses se compliquent, c'est que cette table, alimentée depuis des années, possède un nombre non négligeable de doublons sur le couple personne + adresse, puisqu'une personne peut posséder plus d'un dossier.
    Il me faut donc d'abord dédoublonner les personnes (soit même nom, prénom, ad1, ad2,ad3, codpost & ville) avant de les splitter dans mes deux nouvelles entités personne et adresse, puis lier à ces personnes nouvellement créées leur (s)dossier(s) sachant qu'il est impératif que je conserve dans l'entité dossier la référence 'lot' comme identifiant.

    Je ne sais par quel bout prendre cette corvée ... un peu d'aide siouplait ?

  2. #2
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    5 093
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 5 093
    Points : 15 256
    Points
    15 256
    Par défaut
    Salut à tous.

    Je ne m'intéresse pas à la structure de tes nouvelles tables. D'autres se chargeront d'y remédier.

    Citation Envoyé par SomeoneElseIsHere
    Vous noterez qu'une personne ( civilité + nom + prénom ) ne peut, en l'état, ainsi avoir qu'une adresse (ad1, ad2, ad3, ville, codpost) et un dossier (lot, entrée, sortie)
    Je ne vois pas pourquoi une personne aurait une seule adresse et un seul dossier.

    Vous partez du triplé ( civilité + nom + prénom ) pour remplir votre nouvelle table personne.
    Que se passe-t-il, pour une même qui parait plusieurs fois dans votre table "bazar", si son nom est écrit différemment ?
    Majuscule au lieu de minuscule, des tirets absents ou présents, ...
    Il se peut même que vous ayez des homonymes (ça arrive surtout avec les Dupont ou Dupond, Durand).

    Je pense que ce point de départ n'est pas suffisant.

    Pas de numéros de téléphone ? Surprenant.

    L'adresse dépend de la façon dont elle a été saisie.
    Un même code postal peut évoluer dans le temps. Un nom de rue peut changer.
    Voire même, là où il n'y avait pas de numéro (à cause de la fibre optique, dans les villages), vous aurez un numéro !

    Il y a pire, une même personne peut changer d'adresse. Voire même, une personne peut posséder plusieurs adresses.

    Si j'ai bien compris, dans votre table "bazar", l'identification de la ligne se fait à partir de la colonne "lot".
    Je vous suggère de créer une table de travail qui va servir à faire le lien entre votre ancienne table "bazar" et votre nouvelle table personne.
    Elle sera composée que de deux colonnes, à savoir :
    --> la colonne "lot" de votre ancienne table "bazar".
    --> la colonne "personne_id" de votre nouvelle table "personne".
    Le couple (personne_id, lot) sera l'identifiant de cette table de travail.

    Pour chaque nouvelle personne identifié, vous pouvez utiliser une autre numérotation.
    De ce fait, plusieurs "lot" pourront pointer sur le même identifiant.

    Pour remplir cette table de travail, vous devrez trouver l'unicité de la personne.
    C'est très facile à faire quand le nom est référencé qu'une seule fois dans votre table "bazar".
    Cela devient un peu plus compliqué quand il y a des doublons, sachant que :
    a) l'adresse est la même.
    b) l'adresse est presque la même.
    c) l'adresse est différente.

    Pour le critère, je suggère de faire un regroupement sur :
    --> civilité
    --> nom
    --> prenom
    --> code postal
    --> ville
    --> adresse 1
    --> adresse 2
    --> adresse 3
    En concaténant toutes ces informations dans une même colonne, qui servira de critère à l'identification de la personne.

    A vous de vérifier visuellement que le critère est bon.
    Vous devrez peut-être corriger l'adresse ou le nom de la personne, sans changer la nature de l'information pour que le regroupement se fasse correctement.

    Citation Envoyé par SomeoneElseIsHere
    Là ou les choses se compliquent, c'est que cette table, alimentée depuis des années, possède un nombre non négligeable de doublons sur le couple personne + adresse, puisqu'une personne peut posséder plus d'un dossier.
    Ce n'est pas nécessairement un grave problème pour alimenter vos nouvelles tables.

    Vous lisez une ligne de la table "bazar".

    Vous cherchez dans la table de travail, l'identifiant de la personne.
    Vous créez une ligne dans la table "personne" si celle-ci n'existe pas encore.
    si elle existe déjà, vous ne la modifier pas.

    Comme vous avez l'identifiant de la personne, vous pouvez créer dans la table "adresse" une nouvelle ligne.
    Si celle-ci existe déjà (avec les mêmes informations), il est inutile de la créer à nouveau.

    Dans la table "dossier", vous aurez exactement le même nombre de lignes que dans la table "bazar".
    Je suggère de supprimer les lignes trop ancienne, voire les archiver.

    Cordialement.
    Artemus24.
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  3. #3
    Membre à l'essai
    Profil pro
    Inscrit en
    août 2004
    Messages
    26
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : août 2004
    Messages : 26
    Points : 10
    Points
    10
    Par défaut
    Bonjoiur Artemus24 et merci pour votre réponse.

    Citation Envoyé par Artemus24 Voir le message
    Salut à tous.
    Je ne vois pas pourquoi une personne aurait une seule adresse et un seul dossier.

    Vous partez du triplé ( civilité + nom + prénom ) pour remplir votre nouvelle table personne.
    Que se passe-t-il, pour une même qui parait plusieurs fois dans votre table "bazar", si son nom est écrit différemment ?
    Majuscule au lieu de minuscule, des tirets absents ou présents, ...
    Il se peut même que vous ayez des homonymes (ça arrive surtout avec les Dupont ou Dupond, Durand).

    Je pense que ce point de départ n'est pas suffisant.
    J'ai du mal m'exprimer, je suis en total accord avec vous, il se trouve que j'herites de la table "bazar" tel quel et fais le même constat que vous. Ma question est comment splitter tout ceci proprement et rapidement et tenant compte du fait qu'à ce stade, une personne = une adresse = n dossiers ... donc sans créer de doublons et en s'assurant le bon rattachement des n dossiers à une seule et même personne

    Citation Envoyé par Artemus24 Voir le message
    Pas de numéros de téléphone ? Surprenant.
    Ce point a déjà été résolu par le passé

    Citation Envoyé par Artemus24 Voir le message
    L'adresse dépend de la façon dont elle a été saisie.
    Un même code postal peut évoluer dans le temps. Un nom de rue peut changer.
    Voire même, là où il n'y avait pas de numéro (à cause de la fibre optique, dans les villages), vous aurez un numéro !

    Il y a pire, une même personne peut changer d'adresse. Voire même, une personne peut posséder plusieurs adresses.
    Nous sommes toujours d'accord !

    Citation Envoyé par Artemus24 Voir le message
    Si j'ai bien compris, dans votre table "bazar", l'identification de la ligne se fait à partir de la colonne "lot".
    Je vous suggère de créer une table de travail qui va servir à faire le lien entre votre ancienne table "bazar" et votre nouvelle table personne.
    Elle sera composée que de deux colonnes, à savoir :
    --> la colonne "lot" de votre ancienne table "bazar".
    --> la colonne "personne_id" de votre nouvelle table "personne".
    Le couple (personne_id, lot) sera l'identifiant de cette table de travail.

    Pour chaque nouvelle personne identifié, vous pouvez utiliser une autre numérotation.
    De ce fait, plusieurs "lot" pourront pointer sur le même identifiant.

    Pour remplir cette table de travail, vous devrez trouver l'unicité de la personne.
    C'est très facile à faire quand le nom est référencé qu'une seule fois dans votre table "bazar".
    Cela devient un peu plus compliqué quand il y a des doublons, sachant que :
    a) l'adresse est la même.
    b) l'adresse est presque la même.
    c) l'adresse est différente.

    Pour le critère, je suggère de faire un regroupement sur :
    --> civilité
    --> nom
    --> prenom
    --> code postal
    --> ville
    --> adresse 1
    --> adresse 2
    --> adresse 3
    En concaténant toutes ces informations dans une même colonne, qui servira de critère à l'identification de la personne.

    A vous de vérifier visuellement que le critère est bon.
    Vous devrez peut-être corriger l'adresse ou le nom de la personne, sans changer la nature de l'information pour que le regroupement se fasse correctement.


    Ce n'est pas nécessairement un grave problème pour alimenter vos nouvelles tables.

    Vous lisez une ligne de la table "bazar".

    Vous cherchez dans la table de travail, l'identifiant de la personne.
    Vous créez une ligne dans la table "personne" si celle-ci n'existe pas encore.
    si elle existe déjà, vous ne la modifier pas.

    Comme vous avez l'identifiant de la personne, vous pouvez créer dans la table "adresse" une nouvelle ligne.
    Si celle-ci existe déjà (avec les mêmes informations), il est inutile de la créer à nouveau.

    Dans la table "dossier", vous aurez exactement le même nombre de lignes que dans la table "bazar".
    Je suggère de supprimer les lignes trop ancienne, voire les archiver.

    Cordialement.
    Artemus24.
    @+
    Vous avez parfaitement compris, et j'avoue bêtement ne pas avoir songé à la table de travail. Ca me semble parfaitement répondre au besoin ! Je vais tenter ça, merci beaucoup

  4. #4
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 693
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 7 693
    Points : 26 408
    Points
    26 408
    Billets dans le blog
    2
    Par défaut
    Bonjour,

    Tant qu'à faire de refondre tout ça, ce qui est effectivement nécessaire, profitez-en pour appliquer les normes

    Pour les civilités :
    pour éviter les civilités fantaisistes (typiquement Mr au lieu de M.) et les graphies multiples, appliquez la norme disponible ici :
    https://www.les-abreviations.com/civilite.html
    Un contrainte "check" (applicable à partir de MySQL V8) ou à défaut "enum" sera la bienvenue.


    Pour les adresses :
    la norme de la poste est 6 à 7 lignes de 38 caractères selon que l'adresse est française ou étrangère. Du varchar(100) est donc inutile.
    cf. https://www.76310.fr/norme-afnor-nf-z-10-011.htm
    Mais la ville ne doit pas être un attribut de l'adresse, elle est partagée par de nombreuses adresses, il faut donc l'externaliser dans une table spécifique en lien avec l'adresse.
    Ce qui fait une ligne de moins dans la table adresse
    En France, tout code postal fait toujours exactement 5 caractères, il faut donc remplacer le varchar(5) par du char(5) ce sera plus performant et moins encombrant.

    Ensuite, si une adresse concerne une et une seule personne et qu'une personne peut avoir zéro (ou une) à plusieurs adresses, alors, la bonne façon d'identifier l'adresse est de l'identifier relativement à la personne. Ca signifie que la PK de l'adresse utilisera, dans cet ordre, la PK de la personne, puis un chrono adresse. Le chrono adresse seul n'est pas unique, c'est le couple PK personne + chrono adresse qui garantit l'unicité. Comme une personne peut avoir un nombre limité d'adresses, une colonne de type tinyint suffira largement pour le chrono.
    L'avantage est que, en définissant la PK comme index cluster (ce qui est synonyme pour MySQL), toutes les adresses d'une même personne seront rangées de façon contiguë, d'où une recherche optimisée.

    Ce qui donne le DDL suivant :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TABLE adresse (
      personne_id int(11) NOT NULL,
      adresse_id  tinyint NOT NULL,
      ad1 varchar(38) CHARACTER SET utf8 NOT NULL,
      ad2 varchar(38) CHARACTER SET utf8 NOT NULL,
      ad3 varchar(38) CHARACTER SET utf8 NOT NULL,
      ad4 varchar(38) CHARACTER SET utf8 NOT NULL,
      ad5 varchar(38) CHARACTER SET utf8 NOT NULL,
      codpost char(5) CHARACTER SET utf8 NOT NULL,
      PRIMARY KEY (personne_id, adresse_id),
      FOREIGN KEY(personne_id) REFERENCES personne(personne_id)
    Notez la suppression des quotes inversées inutiles et pénibles à l'usage (elles ne sont nécessaires que si des noms d'objets sont des mots réservés SQL, ce qui n'est pas recommandé)


    Même raisonnement concernant les dossiers si un dossier ne concerne qu'une et une seule personne

  5. #5
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 902
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 902
    Points : 49 641
    Points
    49 641
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par escartefigue Voir le message
    ...
    Pour les civilités :
    pour éviter les civilités fantaisistes (typiquement Mr au lieu de M.) et les graphies multiples, appliquez la norme disponible ici :
    https://www.les-abreviations.com/civilite.html
    Un contrainte "check" (applicable à partir de MySQL V8) ou à défaut "enum" sera la bienvenue.
    Ou mieux, une table de référence !

    Pour les adresses :
    la norme de la poste est 6 à 7 lignes de 38 caractères selon que l'adresse est française ou étrangère. Du varchar(100) est donc inutile.
    cf. https://www.76310.fr/norme-afnor-nf-z-10-011.htm
    Mais la ville ne doit pas être un attribut de l'adresse, elle est partagée par de nombreuses adresses, il faut donc l'externaliser dans une table spécifique en lien avec l'adresse.
    Ce qui fait une ligne de moins dans la table adresse
    Non, là je suis pas d'accord. Nombre de localité change de nom. Le nom de la ville doit être conservé tel qu'il était au moment de la saisie.... Il se peut qu'il y ait des homonymes tolérés dans un même département...

    En France, tout code postal fait toujours exactement 5 caractères, il faut donc remplacer le varchar(5) par du char(5) ce sera plus performant et moins encombrant.
    Mais à l'étranger jusqu'a 8 caractères...


    Ensuite, si une adresse concerne une et une seule personne et qu'une personne peut avoir zéro (ou une) à plusieurs adresses, alors, la bonne façon d'identifier l'adresse est de l'identifier relativement à la personne. Ca signifie que la PK de l'adresse utilisera, dans cet ordre, la PK de la personne, puis un chrono adresse. Le chrono adresse seul n'est pas unique, c'est le couple PK personne + chrono adresse qui garantit l'unicité. Comme une personne peut avoir un nombre limité d'adresses, une colonne de type tinyint suffira largement pour le chrono.
    L'avantage est que, en définissant la PK comme index cluster (ce qui est synonyme pour MySQL), toutes les adresses d'une même personne seront rangées de façon contiguë, d'où une recherche optimisée.

    Ce qui donne le DDL suivant :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TABLE adresse (
      personne_id int(11) NOT NULL,
      adresse_id  tinyint NOT NULL,
      ad1 varchar(38) CHARACTER SET utf8 NOT NULL,
      ad2 varchar(38) CHARACTER SET utf8 NOT NULL,
      ad3 varchar(38) CHARACTER SET utf8 NOT NULL,
      ad4 varchar(38) CHARACTER SET utf8 NOT NULL,
      ad5 varchar(38) CHARACTER SET utf8 NOT NULL,
      codpost char(5) CHARACTER SET utf8 NOT NULL,
      PRIMARY KEY (personne_id, adresse_id),
      FOREIGN KEY(personne_id) REFERENCES personne(personne_id)
    Notez la suppression des quotes inversées inutiles et pénibles à l'usage (elles ne sont nécessaires que si des noms d'objets sont des mots réservés SQL, ce qui n'est pas recommandé)


    Même raisonnement concernant les dossiers si un dossier ne concerne qu'une et une seule personne
    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  6. #6
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 693
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 7 693
    Points : 26 408
    Points
    26 408
    Billets dans le blog
    2
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Ou mieux, une table de référence !
    Si la liste de valeurs est utilisée dans d'autres associations oui
    Si la liste de valeurs est associée à d'autres colonnes (date de début et de fin de validité, libellé court et long, etc.) oui aussi
    Sinon c'est inutile


    Citation Envoyé par SQLpro Voir le message
    Non, là je suis pas d'accord. Nombre de localité change de nom. Le nom de la ville doit être conservé tel qu'il était au moment de la saisie.... Il se peut qu'il y ait des homonymes tolérés dans un même département...
    Effectivement, les villes changent parfois de nom, le plus souvent à l'occasion de fusion de communes, mais pas seulement.
    Raison supplémentaire pour ne pas à voir à mettre à jour plusieurs fois les communes concernées quand c'est le cas
    La conservation de la valeur saisie peut avoir des raisons d'être (à préciser dans les règles de gestion), mais dans la grosse majorité des cas, on doit prendre le nouveau nom de la commune !
    Il faut donc bien externaliser le nom de la commune en dehors de l'adresse dans la plupart des cas.

    Citation Envoyé par SQLpro Voir le message
    Mais à l'étranger jusqu'a 8 caractères...
    À voir selon que des adresses étrangères sont requises ou pas, certains pays n'ont d'ailleurs pas de code postal (ex : Djibouti, Bénin)

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 902
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 902
    Points : 49 641
    Points
    49 641
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par escartefigue Voir le message
    Si la liste de valeurs est utilisée dans d'autres associations oui
    Si la liste de valeurs est associée à d'autres colonnes (date de début et de fin de validité, libellé court et long, etc.) oui aussi
    Sinon c'est inutile
    Et si l'intitulé change (exemple récent Mademoiselle transformé en Madame.... tu met à jour inutilement des milliers de lignes.
    Si tu doit traduire ta base, tu met à jour toutes les lignes de la base.
    etc...
    Donc, non, systématiser les tables de référence, toujours et encore !

    Effectivement, les villes changent parfois de nom, le plus souvent à l'occasion de fusion de communes, mais pas seulement.
    Raison supplémentaire pour ne pas à voir à mettre à jour plusieurs fois les communes concernées quand c'est le cas
    La conservation de la valeur saisie peut avoir des raisons d'être (à préciser dans les règles de gestion), mais dans la grosse majorité des cas, on doit prendre le nouveau nom de la commune !
    Non, mais conserver un historique.... éventuellement
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  8. #8
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 693
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 7 693
    Points : 26 408
    Points
    26 408
    Billets dans le blog
    2
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Citation Envoyé par escartefigue Voir le message
    Si la liste de valeurs est utilisée dans d'autres associations oui
    Si la liste de valeurs est associée à d'autres colonnes (date de début et de fin de validité, libellé court et long, etc.) oui aussi
    Sinon c'est inutile
    Et si l'intitulé change (exemple récent Mademoiselle transformé en Madame.... tu met à jour inutilement des milliers de lignes.
    Si tu doit traduire ta base, tu met à jour toutes les lignes de la base.
    etc...
    Donc, non, systématiser les tables de référence, toujours et encore !
    IL y a donc un intitulé associé au code, ce qui renvient au deuxième cas que j'avais mentionné pour lequel il faut une table de référence.
    C'est quand le code est utilisé seul sans libellé ni date de validité ou autres attributs et qu'il n'intervient pas dans d'autres associations, que la contrainte CHECK se justifie

  9. #9
    Membre averti
    Profil pro
    Administrateur
    Inscrit en
    mai 2008
    Messages
    237
    Détails du profil
    Informations personnelles :
    Localisation : Canada

    Informations professionnelles :
    Activité : Administrateur
    Secteur : Industrie

    Informations forums :
    Inscription : mai 2008
    Messages : 237
    Points : 433
    Points
    433
    Par défaut OpenRefine
    Citation Envoyé par SomeoneElseIsHere Voir le message
    Bonjour à tous,

    Là ou les choses se compliquent, c'est que cette table, alimentée depuis des années, possède un nombre non négligeable de doublons sur le couple personne + adresse, puisqu'une personne peut posséder plus d'un dossier.
    Il me faut donc d'abord dédoublonner les personnes (soit même nom, prénom, ad1, ad2,ad3, codpost & ville) avant de les splitter dans mes deux nouvelles entités personne et adresse, puis lier à ces personnes nouvellement créées leur (s)dossier(s) sachant qu'il est impératif que je conserve dans l'entité dossier la référence 'lot' comme identifiant.

    Je ne sais par quel bout prendre cette corvée ... un peu d'aide siouplait ?
    Je te conseille de télécharger openrefine, une application (ETL) opensource de nettoyage et de mise en forme de données
    Il se décline en une interface web développer en Java
    Importation au formats : sql, csv, excel , json .....
    Exportation aux formats : csv, sql, json, excel ...
    Il y a ausi un language de script permettant de traiter tes données.
    Il te fera gagner beaucoup de temps pour détecter les doublons ou formater tes données, avant de les migrer dans tes nouvelles tables.
    Surtout si tu as une grande volumétrie : 50 000, 100 000 entrées, etc...

    https://openrefine.org/

  10. #10
    Membre averti
    Profil pro
    Administrateur
    Inscrit en
    mai 2008
    Messages
    237
    Détails du profil
    Informations personnelles :
    Localisation : Canada

    Informations professionnelles :
    Activité : Administrateur
    Secteur : Industrie

    Informations forums :
    Inscription : mai 2008
    Messages : 237
    Points : 433
    Points
    433
    Par défaut
    Si cette modélisation peut t'aider:

    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
    33
     
    CREATE TABLE civilites (
      civilite_id integer PRIMARY KEY,
      civilite varchar(17) CHARACTER SET utf8
    );
     
    CREATE TABLE villes (
      ville_id integer PRIMARY KEY,
      ville varchar(17) CHARACTER SET utf8
    );
     
    CREATE TABLE personnes (
      personne_id integer PRIMARY KEY,
      civilite_id integer NOT NULL REFERENCES civilites(civilite_id),
      nom varchar(35) CHARACTER SET utf8 NOT NULL,
      prenom varchar(35) CHARACTER SET utf8 NOT NULL,
      creation timestamp NOT NULL DEFAULT current_timestamp()
    );
     
    CREATE TABLE adresses (
      adresse_id integer PRIMARY KEY,
      personne_id integer REFERENCES personnes(personne_id),
      ville_id integer NOT NULL REFERENCES villes(ville_id),
      adresse varchar(38) CHARACTER SET utf8 NOT NULL,
      code_postal char(5) CHARACTER SET utf8 NOT NULL,
      creation timestamp NOT NULL DEFAULT current_timestamp()
    );
     
    CREATE TABLE dossiers (
      lot integer PRIMARY KEY,
      personne_id integer REFERENCES personnes(personne_id),
      creation timestamp NOT NULL DEFAULT current_timestamp()
    );

  11. #11
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 693
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 7 693
    Points : 26 408
    Points
    26 408
    Billets dans le blog
    2
    Par défaut
    • l'usage pour nommer les types d'entités dans le MCD, et donc les tables qui en sont issues dans le MLD, est d'utiliser le singulier : "ville" plutôt que "villes".
      Il est évident que chaque table contiendra plusieurs occurrences
    • dans une table de typologies (devises, pays, unités de mesure), il est très utile de stocker le code et le libellé (voire des dates de début et de fin de validité)
      Or, varchar(17) pour un code civilité c'est trop, au maximum, on a besoin de 5, du coup du char(5) (fixe donc) est préférable
      Et le libellé le plus long est "mesdemoiselles" (code "mlles") qui occupe 14 caractères.
      cf. https://www.les-abreviations.com/civilite.html
    • à l'inverse, varchar(17) pour la ville est très insuffisant, pour rappel, la norme de la poste (voir réponses plus haut) est de 38 caractères
      Dans la table des villes, pour les villes françaises, le code insee de la ville peut être utile.
      La liste des villes françaises avec leur code insee peut être téléchargée gratuitement.

  12. #12
    Membre averti
    Profil pro
    Administrateur
    Inscrit en
    mai 2008
    Messages
    237
    Détails du profil
    Informations personnelles :
    Localisation : Canada

    Informations professionnelles :
    Activité : Administrateur
    Secteur : Industrie

    Informations forums :
    Inscription : mai 2008
    Messages : 237
    Points : 433
    Points
    433
    Par défaut
    J'ai l'impression que tu l'énonces comme une norme.

    Sauf exception, j'opte toujours pour des noms de table au pluriel, tout en gardant le nom du modèle (Entité) ou de la classe correspondante au Singulier. C'est ce que plusieurs frameworks font d'ailleurs. Le plus simplement du monde, juste des lettres en minuscule et un underscore.
    Pas de fantaisie du genre tbl_vehi (pour vehicules).
    C'est selon tout un chacun, ce qui n'est pas correct, c'est de pas disposer d'une convention de nommage.

    Citation Envoyé par escartefigue Voir le message
    [LIST]
    l'usage pour nommer les types d'entités dans le MCD, et donc les tables qui en sont issues dans le MLD, est d'utiliser le singulier : "ville" plutôt que "villes".
    Il est évident que chaque table contiendra plusieurs occurrences
    Stocker les termes "mesdemoiselles", "messieurs",... est inutile pour son cas.
    Pour un varchar, cela n'est pas non plus pertinent d'avoir une taille strictement égale à celle de la plus longue chaîne de caractères. Le plus important est que la taille maximale spécifiée lui soit égale ou supérieure, au risque d'être tronquée.

    Citation Envoyé par escartefigue Voir le message
    [LIST]
    Or, varchar(17) pour un code civilité c'est trop, au maximum, on a besoin de 5, du coup du char(5) (fixe donc) est préférable
    Et le libellé le plus long est "mesdemoiselles" (code "mlles") qui occupe 14 caractères.
    cf. https://www.les-abreviations.com/civilite.html
    Oui, stocker le code iso d'un pays, ou le code insee d'une ville peut s'avérer être pertinent.

    Citation Envoyé par escartefigue Voir le message
    • à l'inverse, varchar(17) pour la ville est très insuffisant, pour rappel, la norme de la poste (voir réponses plus haut) est de 38 caractères
      Dans la table des villes, pour les villes françaises, le code insee de la ville peut être utile.
      La liste des villes françaises avec leur code insee peut être téléchargée gratuitement.

Discussions similaires

  1. Réponses: 1
    Dernier message: 02/08/2015, 20h32
  2. [MySQL-5.6] SELECTION DANS UNE TABLE AVEC PLUSIEURS RELATIONS
    Par glodybiss4 dans le forum Requêtes
    Réponses: 3
    Dernier message: 12/10/2013, 12h34
  3. remplir une table relation
    Par javalhoussain dans le forum ASP.NET
    Réponses: 1
    Dernier message: 20/05/2011, 14h39
  4. Réponses: 2
    Dernier message: 08/09/2009, 11h57
  5. relation entre une table pour plusieurs sous tables
    Par nicolovitch dans le forum Access
    Réponses: 2
    Dernier message: 15/07/2006, 20h03

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo