Discussion :

[Jiyuu]

Je me pose actuellement une question sur l'organisation d'une BDD Sqlite qui risque de remettre celle-ci grandement en question : comment lier deux tables.

Pour faire simple j'ai une table A avec 3 colonnes :

• ID - qui est la clé primaire ;
• CODE - qui est unique
• LIB - qui est du texte.

Et une table B :

• ID - qui est la clé primaire ;
• A_ID - qui représente une ligne de ma table A
• LIB - qui est du texte.

Dans le programme je dois rapprocher la zone CODE de la table A à la zone LIB de la table B. En passant par la structure présentée ci-dessus ça fonctionne, c'est d'ailleurs, il me semble, le principe qui est recommandé dans le cas de jointure (que je ne déclare pas spécialement ici).
Cela dit, le traitement des infos n'en est que plus compliqué.

Je me pose alors les deux questions suivantes :

• pourquoi ne pas lier mes deux tables via le CODE de la table A qui de toute façon est unique ?
• que peut m'apporter une réelle déclaration de jointure ?

D'avance merci pour vos conseils.

++

J

[CinePhil]

que peut m'apporter une réelle déclaration de jointure ?

Une jointure est une opération qui s'effectue dans une requête. Cela n'a rien à voir avec le modèle de données. Donc on ne "déclare" pas une jointure.

Dans le programme je dois rapprocher la zone CODE de la table A à la zone LIB de la table B.

Pourquoi donc le code de la table A se retrouve t-il dans la table B alors qu'il y a déjà la clé étrangère A_ID dans la table B ? C'est de la redondance d'information donc inutile et risqué pour al cohérence des données.

Puisque CODE est unique dans la table A, c'est donc une clé alternative de la table A mais les jointures s'effectuent en principe sur la clé primaire d'une table et la clé étrangère d'une autre table. Dans certains cas, cela peut se faire entre deux clés étrangères mais en principe ça ne doit jamais se faire sur une clé alternative.

[Waldar]

Pourquoi donc le code de la table A se retrouve t-il dans la table B alors qu'il y a déjà la clé étrangère A_ID dans la table B ? C'est de la redondance d'information donc inutile et risqué pour al cohérence des données.

Il n'y est pas, je pense qu'au final la question de Jiyuu est :

Quel est l'intérêt des tables (id_a, code_a, lib_a) et (id_b, id_a, lib_b) versus (code_a, lib_a) et (id_b, code_a, lib_b) ?

Et bien ça dépend, l'intérêt n'est pas toujours réel.
Dans le cas où votre code_a est invariable, stable et raisonnablement court (par exemple un code pays ISO), la clef subrogée est inutile.

Faites une recherche sur "surrogate vs natural keys", c'est un sujet sans fin !

[Jiyuu]

Hello,

En effet ma question est bien celle comprise par waldar, à la différence que je prévoyais aussi d'avoir un ID dans a, mais c'est en effet pas forcément nécessaire dans mon cas. Pour mieux illustrer mon cas il s'agit en fait de rapprocher une base d'articles à une base de devis.

La méthode actuelle demande beaucoup de traitement dans le programme alors que la solution sur laquelle je m'interroge le simplifierait grandement.

Merci pour votre aide et vos avis.

J

[CinePhil]

Pour mieux illustrer mon cas il s'agit en fait de rapprocher une base d'articles à une base de devis.

Pour reprendre ce qu'a dit Waldar :

Dans le cas où votre code_a est invariable, stable et raisonnablement court (par exemple un code pays ISO), la clef subrogée est inutile.

Quand il écrit "raisonnablement court", ça veut dire 4 caractères maxi.
Les identifiants sont en principe des entiers auto-incrémentés et un entier occupe généralement 4 octets.
Une chaîne de type CHAR(4) occupera aussi 4 octets. Par contre, une VARCHAR(4) occupera 5 octets car il en faut un pour la longueur.

Si vos articles sont codés de la forme 'xv24', c'est acceptable. Si c'est 'abc-824-12', c'est trop gros. Idem pour les devis.

La méthode actuelle demande beaucoup de traitement dans le programme alors que la solution sur laquelle je m'interroge le simplifierait grandement.

Je ne vois pourtant pas ce qu'il peut y avoir de bien compliqué !
Quels sont les articles chiffrés dans le devis DV-2015-005 ?

Code :

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

1
2
3
4
5
SELECT DISTINCT a.art_code, a.art_nom
FROM te_article_art a
INNER JOIN te_ligne_devis_ldv l ON l.ldv_id_article = a.art_id
	INNER JOIN te_devis_dev d ON d.dev_id = l.ldv_id_devis
WHERE d.dev_code = 'DV-2015-005'

[SQLpro]

Héritage ou pas ? That is the question !!!!

A +

[Jiyuu]

Je confirme que si je déclare mes codes articles comme clé primaire il y aura plus de 4 caractères.

Pourquoi est-ce compliqué ? Les quelques tests que j'ai pu faire jusqu'à présent ont mis en évidence qu'il était, dans le cas présent, plus rapide d'importer l'intégralité de la table, de traiter les informations nécessaires via le langage de mon programme (en l’occurrence QML) que de consulter la base de données pour chaque critère.

À titre d'exemple, ce que je souhaite c'est quantifier le nombre de vente par article entre date.
Actuellement je fais ou prévois de faire ceci (j'en suis à l'étape 3) :

1. Import de ma base articles ;
2. import de ma base devis entre date ;
3. indentification de l'id d'article contenu dans ma base devis au code correspond dans ma base articles
4. pour chaque identifié j'additionne toutes les ventes ;
5. je crée un tableau regroupant par code article vendu la quantité.

À ce stade, et une nouvelle fois d'après les tests que j'ai faits (compteur en début et fin de fonction globale), il apparaît que l'import total des bases et le traitement par la suite est plus rapide que de faire une requête du style : pour chaque article de la base article, consulte la base devis et cherche les correspondances.

C'est pour cela que je m'interroge sur la fiabilité de lier deux tables via un champs unique autre qu'une clé primaire qui concrètement m'éviterai bien d'avoir l'étape 3.

Évidemment, la fonction globale n'étant pas achevée il faut prendre en considération que l'étape 4 peut potentiellement changer la donne. Je vais donc probablement coder les deux versions, faire le test de rapidité d'exécution et si la différence penche plus d'un coté ou de l'autre prendre une décision. À moins que vous ayez déjà une idée précise du résultat.

@+

J

[CinePhil]

Évidemment, si vous utilisez un SGBD comme un simple entrepôt de stockage, ça n'est pas performant !

Imaginez que vous alliez à la bibliothèque pour y chercher un livre, allez vous prendre dans vos petits bras musclés tous les livres du rayon où se trouve celui dont vous avez besoin ?

Ben là c'est exactement ce que vous faites avec votre programme !

Un SGBD, c'est fait pour traiter les données et beaucoup plus rapidement que par programme.

À titre d'exemple, ce que je souhaite c'est quantifier le nombre de vente par article entre date.

La requête qui va bien sur une hypothétique structure de table de mon imagination...
Quelles sont les quantités vendues par article en 2014 ?

Code :

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

1
2
3
4
5
6
7
8
SELECT a.art_code, a.art_nom,
	COALESCE(SUM(l.lcd_quantite, 0)) AS nb_vente
FROM te_article_art a
LEFT OUTER JOIN te_ligne_commande_lcd l ON l.lcd_id_article = a.art_id
	LEFT OUTER JOIN te_commande_cde c ON c.cde_id = l.lcd_id_commande
WHERE c.cde_date BETWEEN '2014-01-01' AND '2014-12-31'
GROUP BY a.art_code, a.art_nom
ORDER BY a.art_code, a.art_nom

8 lignes de code SQL qui s'exécuteront en moins d'une seconde, même avec des centaines de milliers de commandes et des millions de lignes de commande, si la base de données est correctement modélisée et indexée.

C'est quand même plus pratique que votre usine à gaz applicative, non ?

Apprenez le langage SQL si vous voulez programmer des applications utilisant des bases de données. Vous gagnerez un temps fou en programmation et en performance applicative.
Faites un tour chez SQLPro

[StringBuilder]

Juste un grain de sel, qui ne fera peut-être pas l'unanimité mais bon...

Pourquoi utiliser ID (clé primaire générée automatiquement) et un CODE (unique) ?

En effet SI ET SEULEMENT SI CODE est à la fois immuable dans le temps et jamais NULL, alors une dénormalisation très basique consisterait à faire sauter ID au profit de CODE.

- Pas de perte de performances (si ce n'est éventuellement la taille de l'index primaire, compensé par la disparition d'un index et d'une contrainte).
- Recopie de CODE dans les tables filles, permettant de s'affranchir d'une lecture dans la table A à chaque lecture dans la table B.

Je dis pas que c'est parfait, mais ça peut être un bon compromis, je pense. A tester, et surtout analyser plus finement si les données de la table B sont nombreuses, et qu'il y a une répartition plus ou moins homogène de lignes de B en fonction des lignes de A.

[Jiyuu]

Un grand merci à tous et surtout à toi CinePhil. Tes quelques lignes et exemples, très clairs, m'ont forcés à me pencher sur la question et grâce à un article sur les jointures de SqlPro, tout ceci semble moins nébuleux .

En effet, avec mes bidouilles il me faut pas moins de 20 lignes de code (afin de m'y retrouver j'ai volontairement bien dissocié chaque étape) alors qu'une requête SQL assez simple permet de faire ce que je veux et plus vite je le confirme.
Heureusement que je ne suis qu'au début de mon projet

Juste un grain de sel, qui ne fera peut-être pas l'unanimité mais bon...

Pourquoi utiliser ID (clé primaire générée automatiquement) et un CODE (unique) ?

En effet SI ET SEULEMENT SI CODE est à la fois immuable dans le temps et jamais NULL, alors une dénormalisation très basique consisterait à faire sauter ID au profit de CODE.

- Pas de perte de performances (si ce n'est éventuellement la taille de l'index primaire, compensé par la disparition d'un index et d'une contrainte).
- Recopie de CODE dans les tables filles, permettant de s'affranchir d'une lecture dans la table A à chaque lecture dans la table B.

Je dis pas que c'est parfait, mais ça peut être un bon compromis, je pense. A tester, et surtout analyser plus finement si les données de la table B sont nombreuses, et qu'il y a une répartition plus ou moins homogène de lignes de B en fonction des lignes de A.

C'est une manière de faire que j'envisageais. Mais je risque dans ce cas de me retrouver avec pseudo ID long (8 à 12 caratères) mélangeant chiffres et lettres. À priori ce n'est pas le top.

Les explications de CinePhil et l'utilisation des jointures va me simplifier la tâche et c'est ce que je cherchais... sauf si je rencontre d'autres soucis majeurs, je continuer sur cette voies.

Encore merci à tous

++

J

[CinePhil]

Cool !

Considères-tu que ton problème est ?

[Jiyuu]

Oui évidemment ... je répondais juste à StringBuilder.

++ et en route pour les jointures.

[SQLpro]

Juste un grain de sel, qui ne fera peut-être pas l'unanimité mais bon...

Pourquoi utiliser ID (clé primaire générée automatiquement) et un CODE (unique) ?

Même s'il était parfaitement asémantique, un code bâti sur une chaine de caractères est nettement plus lent dans toutes ses opérations, car il nécessite de prendre en compte la collation pour chaque opération :

• sensibilité à la casse
• sensibilité aux caractères diacritique (accents, ligatures...)
• sensibilité a la largeur (2 = ² ?)
• sensibilité aux kanatypes pour l'criture japonsaise

A +

[Luc Orient]

Même s'il était parfaitement asémantique, un code bâti sur une chaine de caractères est nettement plus lent dans toutes ses opérations, ...

Je n'ai jamais constaté cela sur le SGBD que j'ai longtemps utilisé à savoir DB2 for z/OS ...

[StringBuilder]

@SQLpro > C'est pour ça que je disais que ma solution n'était pas parfaite A tester selon le besoin.

Sinon, pour s'affranchir des jointures à la main, il suffit de créer des vues sur les tables filles,et les utiliser à la place des tables.
D'autant plus qu'il me semble avoir lu, il y a un moment, qu'on n'était pas censé travailler directement sur les tables quand on code l'application métier (notamment pour cette raison).

ce qui donne :

Code sql :

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

1
2
3
4
5
6
 
create view v_tableB
as
select a.id a_id, a.code a_code, a.lib a_lib, b.id b_id, b_lib
from tableA A
inner join tableB B on b.a_id = a.id

Comme ça on a directement les information de A dans l'objet qui manipule B.
Et cerise sur le gâteau : la plupart des SGBD permettent aujourd'hui de faire des mises à jour directement sur la vue (avec un certain nombre de limites ceci dit) sans avoir à écrire de triggers dessus, donc on ne complexifie en rien le code côté SGBD.

[SQLpro]

Je n'ai jamais constaté cela sur le SGBD que j'ai longtemps utilisé à savoir DB2 for z/OS ...

Avez vous fait des tests avec :
1) les mêmes volumétries de jointures avec des collations différentes
2) en comparaison avec des jointures identique mais en INTEGER ?

A +