[SQLite] Fusionner deux primary key multiple en une seule

Version imprimable

Voir 40 message(s) de cette discussion en une page

15/12/2020, 08h29
wulfram

[SQLite] Fusionner deux primary key multiple en une seule

Bonjour à tous,

Je chercher à donner à l'utilisateur la possibilité de modifier des valeurs utilisées pour constituer une primary key multiple. Hors, il pourrait choisir une nouvelle combinaison existant déjà, à un niveau ou un autre.
J'imagine que résoudre mon problème revient peu ou prou au même que trouver un moyen de fusionner deux primary key uniques, mais... ?

J'ai bien trouvé une idée de solution consistant à tourner les PRAGMA KEY OFF (donc pas d'UPDATE/DELETE ON CASCADE) puis recréer complétement la table de référence, modifier les valeurs dans toutes les tables rattachées et recréer ensuite les liaisons, mais cela me semble relativement lourd comme procédé. Je me demandais donc s'il existait un moyen de fusionner automatiquement deux groupes d'entrées ?

Pour exemple :
Table de référence :
PK1 - PK2
a1 - a1
a1- a2
a1 - a3
a2 - a2
a2 - a3
a2 - a4
a3 - a1
a3 -a3
a3 - a4

Si l'utilisateur voulait renommer "a3" de PK1, le résultat devrait être :
a1 - a1
a1- a2
a1 - a3
a1 - a4
a2 - a2
a2 - a3
a2 - a4

Et cela en ayant tous les enregistrements de tables rattachées avec (par exemple) "a4 - a1" maintenant rattachées à "a1 - a1".
Auriez-vous une idée de comment procéder de manière plus simple que l'approche que j'envisage ?

Bien à vous,
15/12/2020, 11h51
Waldar

Vous faites fausse route, on ne renomme pas, sauf cas de force majeure, une clef primaire. Elle se doit d'être le plus immuable possible.
Si votre utilisateur doit changer quelque chose, rajoutez une colonne (unique si besoin) et permettez-lui de modifier celle-ci, mais pas la clef primaire.
15/12/2020, 12h11
vanagreg

Bonjour,

En effet on ne met pas à jour des valeurs de PK, sinon c'est source de problèmes garantie. Il faut probablement changer le besoin car si l'utilisateur a le droit de modifier des valeurs de la PK alors c'est un sérieux problème de conception. Une PK est souvent un id qui n'a pas vraiment de sens fonctionnel donc transparent pour l'utilisateur final.

Bonjour,

J'ai quelques soucis de compréhension de votre modèle de données, et encore plus de ce que vous voulez faire.

Déjà, vos exemple de "clés primaires" montrent :
- des valeurs alphanumérique
- l'utilisation de deux colonnes
=> Une PK, dans la mesure du possible (enfin... non, tout le temps, vous n'avez pas le choix) doit être mono-colonne, numérique, si possible du nombre de bits que le mot standard du CPU/OS (long pour un x64, int pour un x86 par exemple), auto-incrémenté, dénué de sens, et surtout, totalement immuable dans le temps.

Vos valeurs ne respectent pas du tout ces conditions, et cela va forcément se ressentir à un moment où à un autre sur la montée de charge de votre base de données (d'autant plus important qu'on est sur du SQLite, donc de l'embarqué avec un moteur BDD très light et des ressource réduites).

Ensuite, une table avec deux PK, perso, connait pas. J'ai l'impression que vous nous présentez plutôt une table de jointure, qui référence les PK de deux autres tables. Ce sont donc des FK (foreign keys). Et ça, oui, ok, vous pouvez les modifier si ça vous chante (quoique généralement on fait plutôt des INSERT/DELETE dans ce genre de tables que des UPDATE).

Vous semblez donc vouloir fusionner des couples existants en "complétant" un ensemble avec les lignes d'un autre qu'il ne contient pas déjà.

Genre j'ai une voiture "v1" avec deux roues "r1" et "r2", je la fusionne avec une voiture "v2" qui a 4 roues (r1 à r4 + 1 volant z1), et vous souhaitez donc :
- insert r3, r4 et z1 pour pk1 = "v1" qui sont les lignes qui viennent de v2 que j'avais pas v1
- delete where pk1 = "v2"

C'est ça ?

Je pense que l'instruction MERGE pourrait vous permettre de vous en sortir.
Sauf que SQLite, sauf erreur de ma part, ne supporte que le non normatif UPSERT qui ne sait pas faire de DELETE sur match...

Donc avec une série de requêtes SQL :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 // Fusionne PK1 = "a2" et PK1 = "a1" // Ajoute à a1 les PK2 qui lui manquent insert into latable (pk1, pk2) select 'a1', t1.PK2 from latable t1 left outer join latable t2 on t2.pk1 = 'a1' and t2.pk2 = t1.pk2 where t1.pk1 = 'a2' and t2.pk1 is null; // Détruit a2 delete matable where pk1 = 'a2';

15/12/2020, 20h55
wulfram

Bonsoir à tous,

Merci bien pour vos retours.

C'est bien noté pour l'immuabilité des clés primaires. Recourir à un tel procédé ne m'aidera pas vraiment pour mon problème, que je vais essayer de redéfinir au mieux avec un exemple probablement plus parlant.

J'ai à ce jour une table1 avec trois colonnes. J'utilise ces trois colonnes combinées comme une clé primaire, car je souhaite que cette combinaison soit unique dans la table, sans pour autant que chaque colonne ne puisse avoir qu'un seule fois chaque valeur. Cette table me sert à stocker des catégories de dépenses sur trois niveaux, je peux donc avoir :

Supermarché - légumes secs - lentilles
Supermarché - légumes secs - fèves
Supermarché - légumes secs - autres légumes secs
Supermarché - légumes - autres légumes
Marché - légumes secs - haricots
Marché - légumes secs - pois
Marché - légumes secs - autres légumes secs
Marché - fruits - poires
...

Un peu n'importes quelles combinaison, selon ce qui passe dans la tête de l'utilisateur donc.

J'ai une table2 reprenant ces trois colonnes (pour le coup, je peux changer cela avec une clé primaire numérique dans la table1, mais pas certain que ça ne m'aide !) plus un tas d'autres informations (également issus d'autres tables) via une relation entre elles. (donc clé étrangère dans table2) Plusieurs tables "étrangères" sont concernées.

L'utilisateur peut à ce jour ajouter, supprimer des "catégories", ou les modifier sans pour autant reprendre une combinaison déjà existante.
Je souhaite lui permettre de "fusionner" deux catégories, que ce soit au premier, second ou troisième niveau.

On pourrait par exemple imaginer une fusion au niveau 1, en remplaçant "marché" et "hypermarché" par "alimentation", il faudrait donc obtenir :
Alimentation- légumes secs - lentilles
Alimentation - légumes secs - fèves
Alimentation- légumes secs - haricots
Alimentation- légumes secs - pois
Alimentation- légumes secs - autres légumes secs
Alimentation- légumes - autres légumes
Alimentation- fruits - poires
...

Ou un regroupement au second niveau, par exemple mettre tous les "légumes secs" et sous catégorie au niveau du "Marché".
Je me demandais donc s'il était possible de procéder à de tels regroupements sans modifier l'ensemble des tables liées une par une ?

J'espère avoir été plus clair dans l'explication de mon problème !

Bien à vous,
16/12/2020, 07h21
escartefigue
Bonjour Stringbuilder,

Citation:

Envoyé par StringBuilder

Déjà, vos exemple de "clés primaires" montrent :
- des valeurs alphanumérique
- l'utilisation de deux colonnes
=> Une PK, dans la mesure du possible (enfin... non, tout le temps, vous n'avez pas le choix) doit être mono-colonne, numérique, si possible du nombre de bits que le mot standard du CPU/OS (long pour un x64, int pour un x86 par exemple), auto-incrémenté, dénué de sens, et surtout, totalement immuable dans le temps.

Ceci est un raccourci un peu hasardeux...

Une clef candidate doit être
- Unique
- Stable : elle se propage dans les tables filles en participant aux contraintes d'intégrité, il ne faut donc pas avoir des mises à jour en cascade à effectuer si les valeurs devaient être modifiées
- Irréductible : une sous partie de la clef primaire ne peut suffire à assurer l'unicité
Parmi les clefs candidates correspondant à ces critères, on en choisit une comme clef primaire.

à partir de là, rien n'indique que cette PK doit s'appuyer sur une seule colonne, il y a même des cas où ce n'est pas possible

Cas de la table associative : la table issue d'une association n-aire du modèle conceptuel a pour clef primaire l'ensemble des identifiants de chaque type d'entité participant à l'association.

Exemple : si un fournisseur peut fournir plusieurs produits et qu'un produit peut être fourni par plusieurs fournisseurs, on a le MCD suivant
[FOURNISSEUR (FO_ident, FO_nom, ...)] 0,n --- (fournir) --- 1,n [PRODUIT(PR_ident, PR_reference, PR_designation...)]

Au niveau MLD, l'association fournir devient une table ayant pour PK la paire {FO_ident, PR_ident}

Cas de l'identification relative : quand, au niveau conceptuel, on a une entité-type dite "faible", on peut l'identifier relativement à l'entité-type dite "forte" à laquelle elle se rattache. Le cas d'école est la ligne de commande, entité-type faible de la commande, ou la ligne de facture relative à la facture.

ainsi le MCD suivant [COMMANDE(CO_ident, CO_date,...)] 1,n --- (contenir) --- 1,1(R) LIGNE_COMMANDE[(LC_ident, LC_quantite, ...)]
Donne, pour la table LIGNE_COMMANDE, la PK {CO_ident, LC_ident}.
Là aussi, la PK est bien constituée d'une paire d'identifiants.
Dans le cas de l'identification relative, cette paire d'identifiants choisie comme index cluster sera un gage de performances : toutes les lignes associées à une même commande seront stockées dans un espace contigü.

Par contre, ce qui est vrai, c'est que le choix de colonnes de type (var)char présente plusieurs inconvénients :
- c'est un type sensible à la collation, contrairement aux types numériques
- l'encombrement est plus important que le numérique, surtout l'integer, pour un nombre de valeurs identiques
- aussi et surtout, le (var)char contient le plus souvent des valeurs fonctionnelles et donc instables, mais si ce n'est pas le cas, alors pas de souci sur ce point
- enfin, le varchar peut provoquer des déplacements de lignes dans les pages data et index en cas de modification avec changement de longueur
16/12/2020, 15h05
StringBuilder
Citation:

Envoyé par wulfram

J'ai à ce jour une table1 avec trois colonnes. J'utilise ces trois colonnes combinées comme une clé primaire, car je souhaite que cette combinaison soit unique dans la table, sans pour autant que chaque colonne ne puisse avoir qu'un seule fois chaque valeur.

C'est donc ce qu'on appelle une clé composite.
Une telle clé peut, d'un point de vue conceptuel, être la clé primaire, mais en termes de performances, ne devrait jamais l'être.
On préfèrera créer une clé primaire mono-colonne numérique entière, bien plus simple à manipuler par le processeur (la taille des enregistrement sur le disque et en mémoire ont un impact direct sur les performances de la base, et ici, on multiplie par environ 100 la taille de votre index servant de référence aux jointures avec cette table !)

Une clé composite est donc généralement reléguée au statut de clé alternative, ce qui a pour unique conséquence qu'une réduction de la taille de vos index : vous créez dessus une contrainte unique et non nulle, elle se comportera non seulement comme une vrai clé primaire, mais en plus pourra être utilisé comme clé externe dans les autres tables (si SQLite le supporte, c'est à vérifier ceci dit)

Citation:

Envoyé par wulfram

Cette table me sert à stocker des catégories de dépenses sur trois niveaux, je peux donc avoir :

Supermarché - légumes secs - lentilles
Supermarché - légumes secs - fèves
Supermarché - légumes secs - autres légumes secs
Supermarché - légumes - autres légumes
Marché - légumes secs - haricots
Marché - légumes secs - pois
Marché - légumes secs - autres légumes secs
Marché - fruits - poires
...

Il y a une grosse erreur de modélisation : vous ne devriez pas stocker les valeurs littérales dans les colonnes de votre table, et ce, pour plusieurs raisons :
- Si demain on décide de modifier "Supermarché" en "Grande surface", ça vous oblige à mettre à jour plusieurs colonnes dans plusieurs tables, avec des risque d'oublis, un risque d'explosion de la transaction, etc.
- Vous perdez énormément de place, une fois de plus, aussi bien sur le disque qu'en mémoire.

On optera donc pour trois tables :
- magasin_type (id, nom)
- item_type (id, nom)
- item_subtype (id, item_type_id, nom)
=> On voit d'ailleur que "pois" peuvent être sous-type de "légumes secs" aussi bien dans un Supermarché qu'un Marché, il y a donc une relation mère-fille entre les deux.

Citation:

Envoyé par wulfram

J'ai une table2 reprenant ces trois colonnes (pour le coup, je peux changer cela avec une clé primaire numérique dans la table1, mais pas certain que ça ne m'aide !)

Il vous faut deux colonnes au minimum : une PK (so possible int auto incrément) pour table2, puis une référence étrangère vers une clé de table1 (soit son id int auto incrément, soit, si vous considérez que c'est plus simple/lisible et que SQLite le permet, sur sa clé composite : l'important c'est que cette clé composite ne soit pas la vraie PK)

Citation:

Envoyé par wulfram

On pourrait par exemple imaginer une fusion au niveau 1, en remplaçant "marché" et "hypermarché" par "alimentation", il faudrait donc obtenir :
Alimentation- légumes secs - lentilles
Alimentation - légumes secs - fèves
Alimentation- légumes secs - haricots
Alimentation- légumes secs - pois
Alimentation- légumes secs - autres légumes secs
Alimentation- légumes - autres légumes
Alimentation- fruits - poires
...

En vous basant sur la solution que je vous ai donné hier vous devriez pouvoir vous en sortir.
Cependant, attention, car autant pas de souci pour la table1, autant je ne sais pas ce que vous allez faire des données de la table2 : par exemple si la table2 contient les prix pour les pois en supermarché et marché, quelle ligne allez-vous conserver si vous fusionnez les deux en "pois alimentation" ?

Citation:

Envoyé par wulfram

Ou un regroupement au second niveau, par exemple mettre tous les "légumes secs" et sous catégorie au niveau du "Marché".
Je me demandais donc s'il était possible de procéder à de tels regroupements sans modifier l'ensemble des tables liées une par une ?

Malheureusement, les données liées doivent être modifiées. Et la question que je pose juste avant en est la raison : aucun moyen d'automatiser sans prendre une décision, qui vous revient.
Citation:
Envoyé par escartefigue

Bonjour Stringbuilder,

Ceci est un raccourci un peu hasardeux...

Une clef candidate doit être

Unique
Stable : elle se propage dans les tables filles en participant aux contraintes d'intégrité, il ne faut donc pas avoir des mises à jour en cascade à effectuer si les valeurs devaient être modifiées
Irréductible : une sous partie de la clef primaire ne peut suffire à assurer l'unicité

Parmi les clefs candidates correspondant à ces critères, on en choisit une comme clef primaire.

à partir de là, rien n'indique que cette PK doit s'appuyer sur une seule colonne, il y a même des cas où ce n'est pas possible
Raccourci, oui, dans la mesure où rien n'oblige (malheureusement) à prendre en compte les performances lors de la modélisation.
Hasardeux, non.
Une clé peut être composite, ce n'est pas pour autant qu'elle doit être primaire.
La plupart des SGBD maintenant supportent la norme SQL qui permet d'avoir une clé primaire (si possible la plus optimisée possible pour le moteur de la base de données, à savoir un mot de la taille d'un registre CPU, ou moins, jamais plus) et des clés alternative, représentées par des contraintes d'unicités : ces contraintes d'unicité peuvent servir de clé étrangère : avantage, l'index de la clé étrangère stocke en lieu et place de la clé composite la clé primaire, ce qui permet de l'alléger immensément.

Citation:

Envoyé par escartefigue

Cas de la table associative : la table issue d'une association n-aire du modèle conceptuel a pour clef primaire l'ensemble des identifiants de chaque type d'entité participant à l'association.

Dans le cas d'une table associative, dénuée de propriété, la clé primaire est tout simplement "inutile" : on aura bien une contrainte unique si on veut éviter les doublons (et seulement si on veut les éviter !) mais jamais besoin d'identifier une ligne en particulier autrement qu'en interrogeant les clés étrangères qui la compose.
Si la table associative possède des propriétés, et surtout si elle autorise les doublons, alors elle aura absolument besoin d'une clé primaire afin d'identifier ses lignes : aussi bien pour des raisons d'intégrité (cas des doublons, compliqué de passer et 4 à 3 doublons si on ne sait pas identifier les lignes) que pour des raisons de performances : quand j'ai le détail de ma ligne à l'écran, si la table associative référence 20 tables, ça va toujours être plus simple de faire update where id = 123 que update where a_id = 1 and b_id = 2 ... sans oublier la taille de l'index monstrueux qui en dédoulerait.

Citation:

Envoyé par escartefigue

Cas de l'identification relative : quand, au niveau conceptuel, on a une entité-type dite "faible", on peut l'identifier relativement à l'entité-type dite "forte" à laquelle elle se rattache. Le cas d'école est la ligne de commande, entité-type faible de la commande, ou la ligne de facture relative à la facture.

ainsi le MCD suivant [COMMANDE(CO_ident, CO_date,...)] 1,n --- (contenir) --- 1,1(R) LIGNE_COMMANDE[(LC_ident, LC_quantite, ...)]

On a une clé composite candidate. A mon sens, il sera bien plus performant d'avoir une autre clé int qui servira de clé primaire.
Après, certains SGBD comme Oracle biaisent le truc, puisque quelle soit la clé qu'on déclare, ils en créent une autre à eux en plus. Dans ce cas, toutes les clés, y compris la clé primaire sont en réalité des clés alternatives.

D'ailleurs, il me semble qu'il y a un truc bien goret avec SQLite : selon le type de la clé primaire déclarée, il l'utilise ou non, et de mémoire y'a des cas particuliers où ça peut foutre le bordel. Pas envie de relire la doc sur le sujet, je vous laisse ce plaisir ;)
16/12/2020, 15h17
vanagreg

Bonjour,

Je rejoins l'avis d'escartfigue concernant la clé primaire. Pas besoin d'être mono-colonne selon moi, même en termes de performance. J'ai travaillé essentiellement sur des base Oracle et je peux t'assurer qu'en terme de performances pas de souci à utiliser des PK composites, et même pas forcément en numérique. Dans une appli nous avions des PK sur du VARCHAR2(150) et aucun souci de performance sur des tables de plusieurs millions de lignes, en Standard Edition.
Si l'appli est correctement modélisée et codée alors c'est déjà gage de perf.
Alors peut-être que d'autres SGBDR sont plus pointilleuses là dessus.
Je me souviens d'un consultant dev Java qui m'expliquait qu'il fallait absolument que ma PK fût mono-colonne car Hibernate ne savait pas gérer. Un peu surpris de cette limitation mais en fait si c'est possible, il faut juste changer le paramétrage.
16/12/2020, 15h25
escartefigue

Citation:

Envoyé par StringBuilder

Dans le cas d'une table associative, dénuée de propriété, la clé primaire est tout simplement "inutile" : on aura bien une contrainte unique si on veut éviter les doublons (et seulement si on veut les éviter !) mais jamais besoin d'identifier une ligne en particulier autrement qu'en interrogeant les clés étrangères qui la compose

Dans une table associative, la PK est composée de FK... tout simplement et ce que cette association soit ou non porteuse d'attributs
Il suffit pour vous en convaincre de modéliser une association n-aire dans un logiciel adhoc (Power-AMC ou Looping par exemple) et tout naturellement vous le constaterez ;)

Citation:

Envoyé par StringBuilder

.
Si la table associative possède des propriétés, et surtout si elle autorise les doublons, alors elle aura absolument besoin d'une clé primaire afin d'identifier ses lignes : aussi bien pour des raisons d'intégrité (cas des doublons, compliqué de passer et 4 à 3 doublons si on ne sait pas identifier les lignes) que pour des raisons de performances : quand j'ai le détail de ma ligne à l'écran, si la table associative référence 20 tables, ça va toujours être plus simple de faire update where id = 123 que update where a_id = 1 and b_id = 2 ... sans oublier la taille de l'index monstrueux qui en dédoulerait.

Une TABLE n'autorise ni n'interdit les doublons, ce sont les contraintes comme PRIMARY KEY ou UNIQUE qui font ce travail en s'appuyant sur les INDEX pour faciliter cette vérification. Encore une fois, peu importe la présence d'attributs, la PK, unique par définition, sera composée automatiquement des identifiants issus des entité-type concernées par l'association.

Citation:

Envoyé par StringBuilder

.

On a une clé composite candidate. A mon sens, il sera bien plus performant d'avoir une autre clé int qui servira de clé primaire.
Après, certains SGBD comme Oracle biaisent le truc, puisque quelle soit la clé qu'on déclare, ils en créent une autre à eux en plus. Dans ce cas, toutes les clés, y compris la clé primaire sont en réalité des clés alternatives.

Autrement dit, une clef et donc un index supplémentaire puisqu'on a de toute façon besoin des index correspondants aux FK et de la contrainte unique sur le groupe d'identifiants FK formant la PK naturelle !
16/12/2020, 16h23
escartefigue
Citation:

Envoyé par vanagreg

Bonjour,

Je rejoins l'avis d'escartfigue concernant la clé primaire. Pas besoin d'être mono-colonne selon moi, même en termes de performance. J'ai travaillé essentiellement sur des base Oracle et je peux t'assurer qu'en terme de performances pas de souci à utiliser des PK composites, et même pas forcément en numérique. Dans une appli nous avions des PK sur du VARCHAR2(150) et aucun souci de performance sur des tables de plusieurs millions de lignes, en Standard Edition.
Si l'appli est correctement modélisée et codée alors c'est déjà gage de perf.
Alors peut-être que d'autres SGBDR sont plus pointilleuses là dessus.
Je me souviens d'un consultant dev Java qui m'expliquait qu'il fallait absolument que ma PK fût mono-colonne car Hibernate ne savait pas gérer. Un peu surpris de cette limitation mais en fait si c'est possible, il faut juste changer le paramétrage.

Ce n'est pas exactement ce que je dis, du numérique et plus particulièrement de l'INTEGER sera toujours plus performant que du CHAR et pire encore du VARCHAR.
Comme expliqué plus haut, char et varchar présentent les inconvénients suivants par rapport à leur utilisation en tant que PK :
- contenu le plus souvent fonctionnel, d'où risque d'instabilité avec propagation en cascade des mises à jour des tables enfant via les FK, ce qui peut être très lourd de conséquences
- à nombre de valeurs distinctes égal, du char ou du varchar est plus encombrant que de l'integer.
- le char et le varchar sont sensibles à la collation
- le varchar nécessite un réalignement sur longueur fixe pour les opérations de tri (order by, group by, partition by, distinct)
- le varchar peut engendrer des déplacements des lignes dans les pages data et index en cas de mise à jour avec modification de la longueur
- les types char et varchar ne peuvent pas être choisis comme IDENTITY (AUTO_INCREMENT pour certains SGBD), il faut donc gérer manuellement la valeur attribuée, c'est bien moins pratique et moins sécurisé que de laisser le SGBD faire ça tout seul
16/12/2020, 16h46
vanagreg

Oui on est d'accord. C'est nettement mieux du numérique pour les PK et c'est d'ailleurs bien ça que j'utilise quand j'ai la chance de pouvoir modéliser (ce qui est rarement le cas car on se retrouve bien souvent avec de l'existant déjà en place). En fait les colonnes PK dont je parlais portaient sur des colonnes caractères qui représentaient plus des codes que des données texte pertinentes, par exemple M1, M2, ...

Mais ce que je voulais dire c'est que je n'ai jamais constaté de pb de perf particulier sur des index uniques portant sur des colonnes varchar2, du moins sous Oracle. Dans la grande majorité des cas, les problèmes de perf proviennent d'une modélisation pourrie (style attribut/valeur), soit de requêtes vraiment mal écrites, ou du traitement en masse de données en faisant du ligne à ligne, comme on le faisait au 20è siècle.
16/12/2020, 16h56
Waldar

Citation:

Envoyé par vanagreg

je n'ai jamais constaté de pb de perf particulier sur des index uniques portant sur des colonnes varchar2, du moins sous Oracle.

Sur Oracle DB les nombres sont stockés comme des varchar. Stocker 999 ou '999' consommera la même place. Donc pas de différence en effet sur les performances.
16/12/2020, 17h51
StringBuilder

Citation:

Envoyé par escartefigue

Une TABLE n'autorise ni n'interdit les doublons, ce sont les contraintes comme PRIMARY KEY ou UNIQUE qui font ce travail en s'appuyant sur les INDEX pour faciliter cette vérification. Encore une fois, peu importe la présence d'attributs, la PK, unique par définition, sera composée automatiquement des identifiants issus des entité-type concernées par l'association.

Ben non, dans la table associative "voiture_roue", il y aura normalement toujours 5 lignes par voiture, et vu que ce sont 5 roues identiques (sauf si la roue de secours est différente) on aura bien des doublons dans la table.
Donc l'ensemble des FK ne constitue pas forcément une clé candidate.
16/12/2020, 17h53
StringBuilder

Citation:

Envoyé par Waldar

Sur Oracle DB les nombres sont stockés comme des varchar. Stocker 999 ou '999' consommera la même place. Donc pas de différence en effet sur les performances.

Euh, t'es sûr de ça ?

En tout cas, ce qui est sûr, c'est qu'Oracle gère un ROWID pour chaque ligne de chaque table, et c'est effectivement la réelle clé primaire "technique", et la PK telle que définie par les attributs "primary key" n'est techniquement qu'une clé alternative, au même titre que les autres contraintes d'unicité.

C'est pour cette raison que sous Oracle, mais c'est spécifique à Oracle, les performances restent semblables qu'on utilise une PK entière mono-colonne ou une PK varchar(250) multi-colonne.
Au final, les FK référencent les rowid et non la PK.

Edit : de la prose pour SQLite
https://www.sqlitetutorial.net/sqlit...s...%20More%20
C'est "p'têt bin qu'oui, p'têt bin qu'non", selon le type de la colonne définie en PK et la présente de la clause WITHOT ROWID, on a un rowid créé ou non, qui est un alias de a PK ou non... s'y retrouve qui pourra !

Citation:

Envoyé par StringBuilder

Ben non, dans la table associative "voiture_roue", il y aura normalement toujours 5 lignes par voiture, et vu que ce sont 5 roues identiques (sauf si la roue de secours est différente) on aura bien des doublons dans la table.
Donc l'ensemble des FK ne constitue pas forcément une clé candidate.

5 lignes par voitures, certes mais la PK étant, je le répète composée des identifiants de chaque type d'entité participant à l'association, la PK sera donc id_voiture + id_roue, ce binôme est bien entendu unique, il n'y a donc pas de doublon !

Voici l'exemple MCD et MLD pour voiture-roue modélisé avec Looping :

Pièce jointe 586801

Et voici le script généré en choisissant le SGBD SQLITE, je n'ai absolument rien modifié dans le script, on y voit clairement que la PK est composée des deux FK, ça n'a rien d'original, c'est ce qui se passe systématiquement quand on modélise dans les règles de l'art avec un outil adhoc.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE TABLE VO_voiture( VO_ident INTEGER, PRIMARY KEY(VO_ident) ); CREATE TABLE RO_roue( RO_ident INTEGER, PRIMARY KEY(RO_ident) ); CREATE TABLE VO_RO( VO_ident INTEGER, RO_ident INTEGER, PRIMARY KEY(VO_ident, RO_ident), FOREIGN KEY(VO_ident) REFERENCES VO_voiture(VO_ident), FOREIGN KEY(RO_ident) REFERENCES RO_roue(RO_ident) );
Le fait que l'association ne soit pas porteuse d'attribut ne change rien à l'affaire.
Affubler la table associative VO_RO d'un identifiant supplémentaire n'a véritablement aucun intérêt, autre que celui de pénaliser les performances à chaque insert, update ou delete et lors des servitudes.

16/12/2020, 20h29
wulfram

Je suis heureux de voir que ma question qui me semblait fort simple et attendant une réponse binaire (possible ou non possible) anime les foules !
Je ne perds pas une miette de la conversation, mais reste centré sur ma question quant j'en viens à répondre : je n'ai de toutes façons pas les compétences, vous l'aurez compris, pour argumenter dans un sens ou un autre !

Citation:

Envoyé par StringBuilder

C'est donc ce qu'on appelle une clé composite.
Une telle clé peut, d'un point de vue conceptuel, être la clé primaire, mais en termes de performances, ne devrait jamais l'être.
On préfèrera créer une clé primaire mono-colonne numérique entière, bien plus simple à manipuler par le processeur (la taille des enregistrement sur le disque et en mémoire ont un impact direct sur les performances de la base, et ici, on multiplie par environ 100 la taille de votre index servant de référence aux jointures avec cette table !)

Une clé composite est donc généralement reléguée au statut de clé alternative, ce qui a pour unique conséquence qu'une réduction de la taille de vos index : vous créez dessus une contrainte unique et non nulle, elle se comportera non seulement comme une vrai clé primaire, mais en plus pourra être utilisé comme clé externe dans les autres tables (si SQLite le supporte, c'est à vérifier ceci dit)

Oui, en principe cela fonctionne sans problèmes.

Citation:

Envoyé par StringBuilder

Il y a une grosse erreur de modélisation : vous ne devriez pas stocker les valeurs littérales dans les colonnes de votre table, et ce, pour plusieurs raisons :
- Si demain on décide de modifier "Supermarché" en "Grande surface", ça vous oblige à mettre à jour plusieurs colonnes dans plusieurs tables, avec des risque d'oublis, un risque d'explosion de la transaction, etc.
- Vous perdez énormément de place, une fois de plus, aussi bien sur le disque qu'en mémoire.

On optera donc pour trois tables :
- magasin_type (id, nom)
- item_type (id, nom)
- item_subtype (id, item_type_id, nom)
=> On voit d'ailleurs que "pois" peuvent être sous-type de "légumes secs" aussi bien dans un Supermarché qu'un Marché, il y a donc une relation mère-fille entre les deux.

Décidemment, je ne suis vraiment pas doué pour décrire ma situation !
Je cherche à reproduire un arbre de classification de dépenses. Su la base de trois tables, il me faudrait donc :
- niveau1 (id, nom)
- niveau2 (id, niveau1_id, nom)
- niveau3 (id, niveau2_id, nom)

Donc trois tables et huit colonnes au lieu d'une table et trois colonnes, ou cinq colonnes numériques en plus que dans ma construction initiale. Vraiment pertinent en termes de performance ? Certes, cela permet de moins répéter certaines valeurs, mais complexifie les requêtes finales...

Citation:

Envoyé par StringBuilder

Il vous faut deux colonnes au minimum : une PK (so possible int auto incrément) pour table2, puis une référence étrangère vers une clé de table1 (soit son id int auto incrément, soit, si vous considérez que c'est plus simple/lisible et que SQLite le permet, sur sa clé composite : l'important c'est que cette clé composite ne soit pas la vraie PK)

Ma table2 contient des références étrangères vers une dizaine de tables différentes, plus quelques informations qui lui sont propres : il s'agit d'une certaine manière d'une table de synthèse où je regroupe les informations "en dur" afin de facilement requêter dessus pour réaliser mes tableaux de synthèses. Je pourrais effectivement n'y enregistrer que des clés étrangères numériques rattachant aux informations qualitatives dans les autres tables, mais une fois encore il me semble que cela complexifie les requêtes à prévoir pour extraire les résultats qui m'intéressent.
Du fait de mon niveau, j'ai souhaité sacrifier (en pleine conscience) de la performance au profit de la simplicité...

Citation:

Envoyé par StringBuilder

En vous basant sur la solution que je vous ai donné hier vous devriez pouvoir vous en sortir.
Cependant, attention, car autant pas de souci pour la table1, autant je ne sais pas ce que vous allez faire des données de la table2 : par exemple si la table2 contient les prix pour les pois en supermarché et marché, quelle ligne allez-vous conserver si vous fusionnez les deux en "pois alimentation" ?

Malheureusement, les données liées doivent être modifiées. Et la question que je pose juste avant en est la raison : aucun moyen d'automatiser sans prendre une décision, qui vous revient.

erci pour la réponse à ma question initiale ! C'est bien ce que je craignais : il faudra modifier les autres tables utilisant des informations de la table1, et cela que j'utilise des clés primaires numériques ou en VARCHAR, que je les stock en dur, ou que je ne stock que les référence.
Je dois donc me pencher sur l'algorithme me permettant d'effectuer ces modifications correctement.
Le problème que vous soulevez et je vous en remercie, ne se posera pas, chaque entrée dans ma table2 de synthèse étant parfaitement indépendante l'une de l'autre, et le reflet du passé.
Si j'avais souhaité déterminer un prix pour chaque regroupement, j'aurai ajouté une colonne à ma table1 pour cela, celle-ci présentant une unicité de chaque catégorie (de trois niveaux).

En vous remerciant une fois de plus pour votre retour, je me permets de laisser encore la question en "non résolu", au cas où quelqu'un aurait une nouvelle idée / approche au regard de mes dernières réponses !

Bien à vous,
16/12/2020, 20h59
StringBuilder

Citation:

Envoyé par escartefigue

5 lignes par voitures, certes mais la PK étant, je le répète composée des identifiants de chaque type d'entité participant à l'association, la PK sera donc id_voiture + id_roue, ce binôme est bien entendu unique, il n'y a donc pas de doublon !

Ben si, tu peux avoir des doublons.
Ta voiture peut parfaitement faire référence à 5 identifiants de roues différentes (si tu gères le numéro de série dans la table roue par exemple).
Mais elle peut aussi faire référence à 5 fois le même identifiant de roue (si tu gères le numéro de série dans la table d'association justement).

A noter que le MCD sera le même dans les deux cas, ainsi que le MLD, alors que le couple VO_ident, RO_ident ne sera pas unique.

Citation:

Envoyé par escartefigue

Voici l'exemple MCD et MLD pour voiture-roue modélisé avec Looping :

Pièce jointe 586801

Et voici le script généré en choisissant le SGBD SQLITE, je n'ai absolument rien modifié dans le script, on y voit clairement que la PK est composée des deux FK, ça n'a rien d'original, c'est ce qui se passe systématiquement quand on modélise dans les règles de l'art avec un outil adhoc.

Alors plusieurs choses :
- Faire confiance au script généré par un outil est une erreur en soit (tout comme cliquer sur "enregistrer une macro" dans Excel et espérer avoir un code propre)
- L'outil, et on le voit ici, fait des "choix" et des "interprétations" qui produisent un résultat qui peut s'avérer faux
- Le boulot du DBA, ou à défaut de l'analyse programmeur, c'est justement de pondre un script mieux foutu que celui de l'outil

Citation:

Envoyé par escartefigue

Le fait que l'association ne soit pas porteuse d'attribut ne change rien à l'affaire.

Globalement, oui, mais elle permet tout de même de mieux comprendre pourquoi le modèle peut différer tout en restant parfaitement valable.
Je peux avoir un identifiant de roue pour chaque roue physique, comme je peux avoir un identifiant de roue pour chaque modèle de roue : imaginer un attribut "numéro de série" dans l'une ou l'autre des deux tables permet de mieux comprendre pourquoi le couple voiture/roue peut ou non être unique.

Citation:

Envoyé par escartefigue

Affubler la table associative VO_RO d'un identifiant supplémentaire n'a véritablement aucun intérêt, autre que celui de pénaliser les performances à chaque insert, update ou delete et lors des servitudes.

Pas forcément.
Dans le cas de SQLite, la présence d'une clé composite va de facto rajouter une clé primaire cachée, "rowid" de type int64. Potentiellement plus lourde à gérer qu'une PK int32, surtout sur du SQLite qui peut être embarqué dans un programme 32 bits.
Ensuite, dans le cas de la plupart des SGBD (SQL Server par exemple) les données vont être stockées dans l'ordre de la clé primaire explicitement déclarée comme telle. Par conséquent, si j'insère des données pour les voitures 1000 à 2000, puis que j'ajoute ensuite les données pour les voitures 0 à 1000 le SGBD va passer plus de temps à réorganiser les lignes déjà présentes pour laisser de la place aux nouvelles qu'à insérer effectivement les lignes.
Je te ne raconte pas si ensuite tu te dis "merde, j'ai oublié d'ajouter les sièges à toutes mes voitures !".
Avec un PK numérique auto-incrémentée, tes données seront ajoutées, par défaut, toujours à la fin, donc pas besoin de décaler des milliers de lignes quand tu rajoutes des données.

Bonsoir Wulfram,

Si on reprend votre expression de besoin initiale, à savoir :

Citation:

Envoyé par wulfram

[...] je peux donc avoir :

Supermarché - légumes secs - lentilles
Supermarché - légumes secs - fèves
Supermarché - légumes secs - autres légumes secs
Supermarché - légumes - autres légumes
Marché - légumes secs - haricots
Marché - légumes secs - pois
Marché - légumes secs - autres légumes secs
Marché - fruits - poires
...

L'utilisateur peut à ce jour ajouter, supprimer des "catégories", ou les modifier sans pour autant reprendre une combinaison déjà existante.
Je souhaite lui permettre de "fusionner" deux catégories, que ce soit au premier, second ou troisième niveau.

On pourrait par exemple imaginer une fusion au niveau 1, en remplaçant "marché" et "hypermarché" par "alimentation", il faudrait donc obtenir :
Alimentation- légumes secs - lentilles
Alimentation - légumes secs - fèves
Alimentation- légumes secs - haricots
Alimentation- légumes secs - pois
Alimentation- légumes secs - autres légumes secs
Alimentation- légumes - autres légumes
Alimentation- fruits - poires
...

La base de donnnées ne saurait aucunement imaginer ce qui se passe dans la tête de chaque utilisateur, fusionner "super marché" et "marché" en "alimentation" n'a aucun sens !
"super marché" et "marché" sont des types de commerce au même titre que "supérette", "hyper marché", "commerce de proximité" etc.
"alimentation" est un type de produit au même titre que "bricolage", "jardinage", "habillement" etc.
Donc, il faut pour chaque produit (pomme, poire, pantalon, clef à molette...) déterminer dans quel(s) type(s) de commerce(s) il peut être acheté d'une part
et il faut pour chaque produit (pomme, poire...) déterminer dans quelle(s) catégorie(s) il peut être rangé

à partir de là, chaque utilisateur pourra faire les regroupements qu'il souhaite

Voici un exemple de modèle de données (MCD et MLD) correspondant à ce besoin :

Pièce jointe 586813

et le script qui va avec :
Code:

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 34 35 36 37 38 CREATE TABLE TC_type_commerce( TC_ident INTEGER, PRIMARY KEY(TC_ident) ); CREATE TABLE TP_type_produit( TP_ident INTEGER, PRIMARY KEY(TP_ident) ); CREATE TABLE PR_produit( PR_ident INTEGER, TP_ident INTEGER NOT NULL, PRIMARY KEY(PR_ident), FOREIGN KEY(TP_ident) REFERENCES TP_type_produit(TP_ident) ); CREATE TABLE CP_categorie_produit( CP_ident TEXT, PRIMARY KEY(CP_ident) ); CREATE TABLE TC_PR( TC_ident INTEGER, PR_ident INTEGER, PRIMARY KEY(TC_ident, PR_ident), FOREIGN KEY(TC_ident) REFERENCES TC_type_commerce(TC_ident), FOREIGN KEY(PR_ident) REFERENCES PR_produit(PR_ident) ); CREATE TABLE PR_CP( PR_ident INTEGER, CP_ident TEXT, PRIMARY KEY(PR_ident, CP_ident), FOREIGN KEY(PR_ident) REFERENCES PR_produit(PR_ident), FOREIGN KEY(CP_ident) REFERENCES CP_categorie_produit(CP_ident) );
Bien sûr, je n'ai fait que le strict minimum, à savoir les identifiants.
À enrichir avec les attributs qui vont bien là où ils sont requis ;)

16/12/2020, 21h11
StringBuilder

Citation:

Envoyé par wulfram

Je cherche à reproduire un arbre de classification de dépenses. Su la base de trois tables, il me faudrait donc :
- niveau1 (id, nom)
- niveau2 (id, niveau1_id, nom)
- niveau3 (id, niveau2_id, nom)

Donc trois tables et huit colonnes au lieu d'une table et trois colonnes, ou cinq colonnes numériques en plus que dans ma construction initiale. Vraiment pertinent en termes de performance ? Certes, cela permet de moins répéter certaines valeurs, mais complexifie les requêtes finales...

Oui, c'est le bon modèle.
En termes de performances, cela va se ressentir immédiatement (d'autant plus je le répère avec SQLite qui implique un moteur léger, de la mémoire partagée donc limitée, et potentiellement une machine déjà limitée : c'est pas un serveur, elle fait tourner d'autres choses, etc.)
En effet, passer de PK de 150 caractères (donc potentiellement 300 octets si ce sont des chaines unicode) à 3 x 32 ou 64 bits, y'a pas photo, vous compressez vos index de plus de 99% !

Ensuite, faires des requêtes à rallonge ne représente une perte de temps que pour le développeur qui les écrit. A l'exécution, elles seront plus rapide (moins de données à manipuler), et d'un point de vue maintenance/évolutivité vous vous y retrouverez à la première modification du programme.

N'oubliez pas que vous pouvez écrire des vues : rien ne vous empêche de créer une vue v_table2 qui expose les noms correspondant aux PK correspondant à votre table actuelle.
Je vous invite à consulter l'unique billet sur mon blog (cf mon profil à gauche) pour voir comment procéder.
=> Sauf que vu que vous avez la main sur le code, vous vous contenterez de faire une vue pour les lectures, vous enregistrerez directement dans les tables au bon format ;)

Citation:

Envoyé par StringBuilder

Ben si, tu peux avoir des doublons.
Ta voiture peut parfaitement faire référence à 5 identifiants de roues différentes (si tu gères le numéro de série dans la table roue par exemple).
Mais elle peut aussi faire référence à 5 fois le même identifiant de roue (si tu gères le numéro de série dans la table d'association justement).

On part de très très loin 8O

Si le numéro de série de la roue entre en jeu alors ce n'est plus la roue en tant que type de roue qui entre en jeu dans l'association mais l'exemplaire de roue avec son numéro de série
et on arrive exactement au même résultat !

Le modèle devient en ce cas celui-ci
Pièce jointe 586814

Et le script (toujours pour SQLITE) devient celui là
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 CREATE TABLE TR_type_roue( TR_ident INTEGER, PRIMARY KEY(TR_ident) ); CREATE TABLE RO_roue( RO_ident INTEGER, TR_ident INTEGER NOT NULL, PRIMARY KEY(RO_ident), FOREIGN KEY(TR_ident) REFERENCES TR_type_roue(TR_ident) ); CREATE TABLE VO_voiture( VO_ident INTEGER, PRIMARY KEY(VO_ident) ); CREATE TABLE RO_VO( RO_ident INTEGER, VO_ident INTEGER, PRIMARY KEY(RO_ident, VO_ident), FOREIGN KEY(RO_ident) REFERENCES RO_roue(RO_ident), FOREIGN KEY(VO_ident) REFERENCES VO_voiture(VO_ident) );
La PK de la table associative reste un couple de FK, bien entendu.

Ce sont les fondamentaux de la modélisation d'une base de données relationnelle !

Concernant ces remarques :

Citation:

Envoyé par StringBuilder

- Faire confiance au script généré par un outil est une erreur en soit (tout comme cliquer sur "enregistrer une macro" dans Excel et espérer avoir un code propre)
- L'outil, et on le voit ici, fait des "choix" et des "interprétations" qui produisent un résultat qui peut s'avérer faux
- Le boulot du DBA, ou à défaut de l'analyse programmeur, c'est justement de pondre un script mieux foutu que celui de l'outil

"Avéré faux" est un contresens : "avéré" signifie "révélé vrai" cf. https://www.cnrtl.fr/definition/av%C3%A9rer
Il n'est pas question de faire une confiance aveugle à un script issu d'un outil de modélisation, bien sûr que quelques ajustements ici et là sont nécessaires, mais pour ce qui concerne la propagation des clefs étrangères dans les tables, associatives ou pas et la constitution des clefs primaires, je n'ai jamais vu de logiciel, gratuit ou pas, qui s'égarraient dans le choix des colonnes constitutives !
Le boulot du DBA est en effet de corriger les insuffisances de l'outil, de compléter ce qui doit l'être, mais certainement pas d'ajouter des PK redondantes là où c'est... ben je viens de le dire : redondant

Voir 40 message(s) de cette discussion en une page