[SQL] Mes achats

[foetus]

Avant Propos: J'ai maquillé un vrai projet: donc c'est la même chose, mais peut être que le problème que je vais exposer va sembler bizarre.

Je demande des avis, des conseils des expériences.

Le projet: Je suis en train de coder une application (le langage n'est pas important) pour des achats: créer des achats, faire des totaux et afficher une courbe d'achat en fonction d'un filtre paramétrable.

Et donc, j'ai vais utiliser SQLite pour stocker les achats et faire des requêtes SQL pour interroger tout ce petit monde.

Un achat a comme attributs:

1. une catégorie
   
2. un total
   
3. une date
   
4. une date de création // Pour faire un tri par ordre de création: peut être qu'il va être supprimer
   
5. un type // Soit "physique", Soit "en-ligne"
   
6. nom du magasin
   
7. franchisé ou pas // Voir après
   
8. information de franchise // Voir après
   

Interrogation 1:

La catégorie est en fait une liste que l'utilisateur ne pourra pas modifier (fixe): Informatique, Livre, Loisir, etc. Comme faire cela?

a) Faire une petite base de donnée (ID, Intitulé) et faire une jointure. Je trouve cela bizarre (juste 1 champ), mais SQL est exploité à fond. Et avec les clefs étrangères, je peut contraindre de partout.

b) Faire dans le code un champ énuméré + une table de correspondance "Chaîne de caractères" et mettre la valeur énuméré dans la table achat. Inconvénient: 1) Plus de code à faire 2) SQL pas exploité à fond

b bis) SQLite ne supporte pas l'extension SQL "champ énuméré"

c) Mettre carrément la catégorie "chaîne de caractères" dans la table achat. Inconvénient: 1) SQL pas exploité du tout 2) Base plus lourde (un champ "Chaîne de caractères" ou lieu d'un ID) 3) Aucune contrainte

Interrogation 2:

Est ce que stocker un code de hachage (md5 par exemple) en plus va permettre d'accélérer le traitement "chaîne de caractères"?

Pour ma liste catégorie faire une petite base ainsi: (ID, Intitulé, hachage).

Et ensuite n'utiliser que le hachage et potentiellement virer des "if" dans le code.

Le nœud du problème: Dans la description de mes achats, il y a deux dépendances très fortes.

Le nom du magasin va dépendre du type de magasin et donc je vais avoir deux listes de magasins que l'utilisateur va pouvoir modifier:

Une liste de magasin en ligne et une liste de magasin physique.

Même topo pour la franchise. L'information de franchise va dépendre si le magasin est franchisé ou pas et je vais donc avoir 2 listes d'informations que l'utilisateur va pouvoir modifier:

Une liste d'informations avec le nom du magasin propriétaire si non franchisé, et une liste d'informations avec les adresses sinon.

Et booum, je tombe sur 4 cas:

Magasin Franchisé physique, Magasin Franchisé en ligne, Magasin Non-Franchisé physique, Magasin Non-Franchisé en ligne.

Est-ce qu'il y a une possibilité de faire cela sans avoir à faire au moins 4 ou 5 jointures (en comptant 1 de plus si ma catégorie est une petite base) pour récupérer un achat?

Ou alors SQL est très fort et je ne vais pas plomber mes performances?

Une solution envisagée:

Faire 4 bases de données: une pour chaque cas (Magasin Franchisé physique, Magasin Franchisé en ligne, Magasin Non-Franchisé physique, Magasin Non-Franchisé en ligne)

a) Interrogation: Est-ce que SQL ne préfère pas une grosse base pour faire un tas de traitements au lieu de 4 petites.

b) Inconvénient: Je vais être obligé de faire soit 2 ou 4 requêtes et donc SQL ne sert presque à rien.

Exemple: le total de mes achats en-ligne.

Ou alors est-ce qu'il y a un moyen de merger les bases de données pendant une requête SQL? Ou un autre moyen?

c) Autre Inconvénient: Mon tri par ordre de création. Il faudra faire cela au code avec les 4 résultats triés et faire avancer des pivots en fonction de la date: cela va être très pénible.

Le problème, c'est qu'avec 4 bases, je ne peux pas faire une table d'association style (date de création, ID dans la base), puisque je n'ai aucun moyen d'assurer l'unicité de la clef primaire sur 4 bases:

Une idée 1: Pouvoir faire des ID de base avec un modulo. Pour ma base "Magasin Franchisé physique" ID est un multiple de 4, pour ma base "Magasin Franchisé en ligne" D est un multiple de 4 + 1, etc..

Une idée 2: Passer par un trigger pour rechercher le maximum dans les 4 bases et ensuite faire un 1:

Édit: La solution (enfin une) crève les yeux. En plus cette solution permet de faire des clefs étrangères, de garder une grosse base pour mes achats, et d'avoir au plus 3 jointures (en comptant la jointure pour mon champs "Catégorie", puisque je vais faire une petite base pour cette liste).

Par contre le champs "date de création" est totalement stupide puisque la clef primaire de la base est auto-incrémentée.

Je vais tester tout cela.

[Shtong]

1 - a sans hésiter. Une table de typo (c'est comme ça que j'appelle ces mini-tables) te permet non seulement d'exploiter SQL à fond mais aussi et surtout de ne jamais coder en dur la liste des catégories ailleurs dans ton code. Tout le monde sait qu'un client change tout le temps d'avis, et tu seras bien content quand il changera d'avis sur les catégories et que tu n'auras qu'à ajouter une nouvelle ligne dans ta table pour que ça soit pris en compte partout.

2 - a pas compris en quoi le hachage serait utile ?

Noeud - Difficile à dire sans avoir plus de détails sur ton projet (par exemple le niveau de séparation des différents types de magasin dans la ou les applications), mais d'une manière générale je conseillerai l'approche suivante :

- Trouver tous les points communs de tous les types de magasin (nom, adresse, etc...), les mettre dans une table "Magasins" et lier les achats à cette table.

- Si tous les types de magasins peuvent être réduits aux mêmes attributs (et ça serait vraiment mieux), alors on les met tous dans la même table et c'est fini. Bien sûr on aura un attribut TypeMagasin qui pointe vers une autre table de typo contenant les types de magasins possibles :)

- Si certains types de magasins ont vraiment des données spécifiques (par exemple "Magasin Franchisé physique"), faire des tables contenant uniquement ces données spécifiques, et la lier à une ligne de la table des magasins. Le total des informations sur un magasin sera donc l'union des colonnes de la table des magasins classiques + celle de la table de magasin spécialisée. Comme ça :

-- Chaque magasin a un identifiant unique, quel que soit son type (celui de la table Magasins)

-- Tous tes magasins sont dans la même base, et surtout tous les achats pointent vers la même table de magasins.

-- Pour avoir des détails sur les magasins, rien ne t'empêche de travailler par exemple avec des vues contenant à la fois la table magasins générale et la spécialisée par exemple

[foetus]

Effectivement, j'ai fait cela:

1) Table Catégorie(ID, nom), primary key(ID)

2) Table Magasin(ID, estPhysique, nom), primary key(ID, estPhysique)

3) Table Information(ID, estFranchise, information), primary key(ID, estFranchise)

4) Table Achat(ID, total, catégorie, date, estPhysique, IDMagasin, estFranchise, IDInformation), primary key(ID), foreign key(estPhysique, IDMagasin), foreign key(estFranchise, IDInformation), foreign key(catégorie)

Par contre:

1. SQLite n'est pas typé: donc les champs "estPhysique" et "estFranchise" sont des entiers au lieu de booléens. Un peu mieux pour la maintenance, quoique (ajout futur autres états).
   
2. SQLite ne peut pas auto-incrémenté l'ID lorsque l'ID est une partie de la clef primaire. Il faut que l'ID soit la clef primaire. Donc, un peu de boulot à la main.
   
3. Pour l'instant les informations sont un unique champ de type "chaînes de caractères" (mais pas la même signification): c'est pratique
   
4. Avec ce schéma, je n'ai pas besoin des vues en fait. Dans l'application, j'ai toujours soit une partie des magasins (en fonction du champs "estPhysique"), soit une partie des informations (en fonction du champs "estFranchise"), mais jamais les 2 parties en même temps.
   

Pour l'histoire de hachage des chaînes de caractères, certains SGBD le font (en interne) et tu peux les forcer à en faire (si j'ai tout bien compris).

En fait, en plus de la chaîne de caractères, tu stockes un hachage: un exemple

Table Catégorie(ID, hachage, nom), primary key(ID)

Ensuite tu utilises tout le temps ce hachage, puisqu'il est en théorie unique (surtout sur une tout petite base de donnée).

L'avantage, c'est que le hachage est soit un entier soit une petite chaîne est donc tu accélères les recherches.

Je suis en train de tester tout cela: je suis tombé sur un wrapper SQLite qui ne fait aucun test de retour et donc en cas de requêtes mal formées (essentiellement), j'ai des variables à NULL/ nil/ 0. Et en C, NULL -> Déréférencement (Accès mémoire) -> Plantage:

[Shtong]

Pourquoi avoir fait des clés primaires sur deux colonnes ? la colonne ID ne suffit-elle pas ? En plus ça te force à stocker estPhysique / estFranchise dans la table des achats :]

L'objectif/utilité de la table Information n'est absolument pas évident (après si c'est simplifié/renommé par rapport au cas réel c'est peut-être pas grave). S'il s'agit de vraies tables alors un renommage / réorganisation est peut-être à envisager

SQLite n'est pas typé: donc les champs "estPhysique" et "estFranchise" sont des entiers au lieu de booléens. Un peu mieux pour la maintenance, quoique (ajout futur autres états).

Oui et non car remplacer le booléen par une énumération implique de changer le nom de la colonne (éventuellement grosse opération de maintenance) ou de la laisser telle quelle ce qui va réduire la lisibilité

SQLite ne peut pas auto-incrémenté l'ID lorsque l'ID est une partie de la clef primaire. Il faut que l'ID soit la clef primaire. Donc, un peu de boulot à la main.

Encore une fois je ne pense pas que les PK sur deux colonnes soient nécessaires

Pour l'instant les informations sont un unique champ de type "chaînes de caractères" (mais pas la même signification): c'est pratique

A pas compris

Pour l'histoire de hachage des chaînes de caractères, ........

J'ai toujours pas compris l'interêt dans ton cas.

Soit tu crée un index sur la colonne nom si tu veux faire des recherches par nom.

Voire un index FTS.

Mais pourquoi veux-tu gérer toi-même des mécanismes qui sont déjà gérés pas la DB en interne (qui d'ailleurs le fait sûrement mieux que toi ou moi) ?

[foetus]

L'objectif/utilité de la table Information n'est absolument pas évident (après si c'est simplifié/renommé par rapport au cas réel c'est peut-être pas grave). S'il s'agit de vraies tables alors un renommage / réorganisation est peut-être à envisager

Effectivement, il y a une imprécision dans mon "maquillage": il faut faudrait remplacer le couple (franchisé, information de franchise), par un truc style (aFacture, information) (qui paramètre les achats et non pas l'autre couple Magasin). Ainsi, s'il y a une facture, il faut avoir une liste des différents types de factures. Sinon, une liste des différents paiement (PayPal, CB, Chèque, Nature).

Pourquoi avoir fait des clés primaires sur deux colonnes ? la colonne ID ne suffit-elle pas ? En plus ça te force à stocker estPhysique / estFranchise dans la table des achats :]

En fait, il y a de la redondance et des plus fortes contraintes: effectivement.

Mais, si je veux tous les achats en-ligne: pas de jointure (table Achat). Si je veux tous les magasins physiques: pas de jointure (table Magasin).

Je suis en train de voir l'utilité du truc.

Oui et non car remplacer le booléen par une énumération implique de changer le nom de la colonne (éventuellement grosse opération de maintenance) ou de la laisser telle quelle ce qui va réduire la lisibilité

Le code va être ultra minimaliste: donc la maintenance Mais, j'y pense.

A pas compris

Dans ma table Information, si estFranchise est vrai, alors Information est un nom (du propriétaire). Sinon, c'est une adresse: pas la même signification

Mais il faudrait dire maintenant: si aFacture est vrai, alors Information est un type de facture. Sinon, c'est un type de paiement: pas la même signification

J'ai toujours pas compris l'interêt dans ton cas.

Soit tu crée un index sur la colonne nom si tu veux faire des recherches par nom.

Voire un index FTS.

Mais pourquoi veux-tu gérer toi-même des mécanismes qui sont déjà gérés pas la DB en interne (qui d'ailleurs le fait sûrement mieux que toi ou moi) ?

Pas grave, mais il voir cela avec le code autour. Imagine, je fais une liste déroulante avec tous les magasins en-ligne. Okay, l'utilisateur sélectionne Grosbil: comment je fais comment pour trouver l'ID? Une requête => Inutile.

Alors qu'avec un hachage, je peux faire un truc direct style: "SELECT * FROM MAGASIN WHERE hachage = hachage(Grosbil)"

L'avantage, par rapport au nom c'est que le hachage est soit un entier soit quelques caractères (est souvent unique et donc le mettre en clef primaire (mais très danger et violent)).

[Shtong]

A pas compris

Dans ma table Information, si estFranchise est vrai, alors Information est un nom (du propriétaire). Sinon, c'est une adresse: pas la même signification

Mais il faudrait dire maintenant: si aFacture est vrai, alors Information est un type de facture. Sinon, c'est un type de paiement: pas la même signification

Ah d'accord, ça va loin... Un conseil : ne fais jamais de colonnes qui peuvent avoir des significations différentes en fonction de tel ou tel truc. C'est très mauvais en termes de conception mais surtout c'est tendre le fouet pour te faire battre et passer des heures à résoudre des problèmes que tu aurais pu éviter en t'y prenant autrement.

Pas grave, mais il voir cela avec le code autour. Imagine, je fais une liste déroulante avec tous les magasins en-ligne. Okay, l'utilisateur sélectionne Grosbil: comment je fais comment pour trouver l'ID? Une requête => Inutile.

Alors qu'avec un hachage, je peux faire un truc direct style: "SELECT * FROM MAGASIN WHERE hachage = hachage(Grosbil)"

L'avantage, par rapport au nom c'est que le hachage est soit un entier soit quelques caractères (est souvent unique et donc le mettre en clef primaire (mais très danger et violent)).

Bha en général je m'arrange pour stocker simplement l'identifiant unique de la ligne (colonne ID) dans l'élément de liste déroulante, à coté du texte (pas visible bien sûr mais il est là). Ensuite je peux le récupérer pour retrouver où j'en suis. En plus ça évite d'aller chercher l'identifiant correspondant au hash si tu veux exploiter une foreign key.

99% des systèmes d'affichage de listes déroulantes te permettent de faire ça, alors pas d'excuse :)

[windu.2b]

Pas grave, mais il voir cela avec le code autour. Imagine, je fais une liste déroulante avec tous les magasins en-ligne. Okay, l'utilisateur sélectionne Grosbil: comment je fais comment pour trouver l'ID? Une requête => Inutile.

Alors qu'avec un hachage, je peux faire un truc direct style: "SELECT * FROM MAGASIN WHERE hachage = hachage(Grosbil)"

L'avantage, par rapport au nom c'est que le hachage est soit un entier soit quelques caractères (est souvent unique et donc le mettre en clef primaire (mais très danger et violent)).

Et si tu renommes "GrosBil" en GrosBill" ? Ton hashage est foutu ?

Comme dit Shtong, je ne vois pas du tout l'intérêt de ce hashage qui fait clairement doublon avec l'ID... D'autant qu'il est toujours possible d'associer une valeur à une ligne d'une liste, comme le montre ce petit exemple en HTML :

<option value="ID_1">nom_1</option>

Quand tu valideras le formulaire, c'est "ID_1" qui sera envoyé, pas "nom_1" ! À partir de là, tu peux construire tes requêtes SQL sans pb.

[foetus]

Pas grave, mais il voir cela avec le code autour. Imagine, je fais une liste déroulante avec tous les magasins en-ligne. Okay, l'utilisateur sélectionne Grosbil: comment je fais comment pour trouver l'ID? Une requête => Inutile.

Alors qu'avec un hachage, je peux faire un truc direct style: "SELECT * FROM MAGASIN WHERE hachage = hachage(Grosbil)"

L'avantage, par rapport au nom c'est que le hachage est soit un entier soit quelques caractères (est souvent unique et donc le mettre en clef primaire (mais très danger et violent)).

Et si tu renommes "GrosBil" en GrosBill" ? Ton hashage est foutu ?

Comme dit Shtong, je ne vois pas du tout l'intérêt de ce hashage qui fait clairement doublon avec l'ID... D'autant qu'il est toujours possible d'associer une valeur à une ligne d'une liste, comme le montre ce petit exemple en HTML :

<option value="ID_1">nom_1</option>

Quand tu valideras le formulaire, c'est "ID_1" qui sera envoyé, pas "nom_1" ! À partir de là, tu peux construire tes requêtes SQL sans pb.

Malheureusement, je suis sous Objective C et je dois faire "des roulettes" pour la sélection.

Le problème des roulettes c'est que tout est fixe et visible (impossibilité de griser de cacher ou de faire quoique ce soit).

Pour l'histoire de la clef primaire sur 2 colonnes ("Composite key" en anglais), j'ai recherché et il y a 3 choses à savoir:

1) Très peu de monde les utilise.

2) Elles servent dans les tables d'associations N-N. Exemple d'un côté des élèves, de l'autre des professeurs. Un élève peut avoir plusieurs profs, et un prof peut avoir plusieurs élèves. C'est pour mettre une contrainte forte sur un couple.

3) Il faut avoir le maximum de requêtes qui utilisent toutes les parties de la clef en même temps

Dans mon cas j'ai un type soit "physique", soit "en-ligne" et un nom du magasin. en fait ce que je devrais faire c'est ceci:

Table TypeMagasin(id, type), primary key(id)

Un exemple:

0 physique

1 en-ligne

2 association

Table NomMagasin(id, nom), primary key(id)

Un exemple:

0 Grosbill

1 Surcouf

2 LDLC

Table MagasinType(idType, idMagasin), primary key(TypeMagasin.idType, NomMagasin.idMagasin)

Un exemple

0 1

1 1

2 2

Et le nœud du problème est précisément là:

Parce que si dans ma table Achats, je ne peux plus mettre seulement "idMagasin", mais il faut aussi le type "idType" (*):

1) faire 2 jointures pour avoir le type du Magasin: Achat -> MagasinType -> TypeMagasin

2) faire 2 jointures pour avoir le nom du Magasin: Achat -> MagasinType -> NomMagasin

3) 3 jointures pour avoir les deux informations

4) Pour avoir la liste des magasins pour un certain type: 2 jointures

(*) -> pour éviter cela, il faut faire une "surrogate key" dans la Table MagasinType: mais, je perds la contrainte (Magasin, Type)

Ce n'est pas beaucoup, mais j'ai 2 couples comme cela: (typeMagasin, Magasin) et (facture, typeFacture/ typePayement) (et non plus (magasin, information (soit nom propriétaire soit adresse)))

Plus ma table de catégorie qui ajoute 1 jointure.

En fait, je vais gruger comme un goret pour réduire les jointures:

1) Étant donné que mon type est binaire (soit en-ligne soit non physique) et fixe, je n'ai pas besoin de faire la table TypeMagasin. Et donc, mettre directement dans MagasinType soit 0 soit 1: 1 jointure en moins.

Inconvénient: la maintenance (il faudra peut être créer la table TypeMagasin plus tard) et la signification du type doit être codé dans le code (d'ou mon booléen estPhysique).

2) La table NomMagasin est importante: l'utilisateur pourra rajouter des magasins. Mais pour supprimer une jointure en plus, je vais fusionner la table NomMagasin avec la table MagasinType

Et je vais arriver à cela: Table MagasinType (idMagasin, idType, nomMagasin), primary key(idMagasin, idType)

L'inconvénient: il faudra maintenir l'ID du magasin, c'est à dire pour un même nom de magasin mettre le même ID (cela se fait en une requête)

Et pour que tout cela fonctionne, il faut rajouter une contrainte UNIQUE(idType, nomMagasin)

C'est sûr qu'avec 2 types cela paraît beaucoup: mais j'ai eu une contrainte forte sur le magasin et son type, et "Grosbill en-ligne" n'est pas le même magasin que "Grosbill physique".

Maintenant comme je l'ai déjà dit: mettre la clef composite(idMagasin, idType) dans ma table Achats fait de la redondance mais permet de n'avoir presque plus aucune jointure.

Je réfléchis toujours.

[Shtong]

Malheureusement, je suis sous Objective C et je dois faire "des roulettes" pour la sélection.

Le problème des roulettes c'est que tout est fixe et visible (impossibilité de griser de cacher ou de faire quoique ce soit).

Faux

Tu peux faire comme ça par exemple (à adapter of course si tu utilise core data par exemple) :

// Le modèle Magasin contient (entre autre) un champ ID et un champ Nom
// La propriété magasinsSource contient un tableau de tous les magasins

// AFFICHAGE DU PICKER
-(NSString *) pickerView:(UIPickerView *)picker titleForRow:(NSInteger) row forComponent:(NSInteger) component
{
 return [[self magasinsSource] objectAtIndex:row].nom;
}

// RECUPERATION DE L'ID DE L'ELEMENT SELECTIONNE
-(void) pickerView:(UIPickerView *) picker didSelectRow:(NSInteger) row inComponent:(NSInteger) component
{
 NSNumber idMagasin = [[self magasinsSource] objectAtIndex:row].ID;
 // Utiliser l'ID pour faire des trucs...
 // ...
}

Pour le reste... ne le prends pas mal, mais tes explications me donnent plus l'impression que tu t'enfonce dans un vortex de complexité non nécessaire

Les clés composites :

1)Très peu de monde les utilise => c'est pas pour rien

2)Elles servent dans les tables d'associations N-N => en effet mais les exemples que tu as donnés ne sont pas des exemples de relations N-N ! C'est... autre chose qui n'as pas de nom

3)Il faut avoir le maximum de requêtes qui utilisent toutes les parties de la clef en même temps => oui... encore une belle occasion de te compliquer la vie !

Enfin bref, avec quelque chose de ce genre plus de problème de clés composites et autres relations tirées par les cheveux :

... mais permet de n'avoir presque plus aucune jointure

L'objectif n'est pas non plus d'éviter les jointures