Pour répondre à une demande (populaire ?), voici ce que je voulais dire, en quelques mots (OUI, c'est un sacré euphémisme, vu le mur de texte qui déboule !).
L'exemple qui suit est très bête car presque dénué de véritable sémantique. Or, outre le fait de garantir par contrat les propriétés ACID des transactions, l'intérêt d'une base de données est de modéliser une sémantique forte entre les entités manipulées.
Il va donc vous falloir un soupçon d'imagination pour accorder crédit à ce qui suit.
Une fois ce préambule avalé, prenons un exemple similaire à ce qui est décrit dans le post initial :
Code : Tout sélectionner
CREATE TABLE "Stock_1" (
"ItemId" INTEGER NOT NULL PRIMARY KEY,
"Designation" CHAR NOT NULL,
"Col1" CHAR,
"Col2" CHAR,
"Col3" CHAR,
"Col4" CHAR,
"Col5" CHAR,
"Col6" CHAR,
"Col7" CHAR);
Mettons-y une population quelconque :
https://dl.dropboxusercontent.com/u/264 ... ock_1.html
Là-dessus on peut toujours faire une requête du genre :
Code : Tout sélectionner
select * from stock_1
where Col3 like 'abc%'
or
Col2 like '-%';
et on obtient bien ce qu'on veut :
https://dl.dropboxusercontent.com/u/264 ... ock_1.html
Bon maintenant si on se trouve dans le cas d'attributs en nombre variable pour chaque item, le schéma ci-dessus devient d'une grande lourdeur, car on est contraint d'ajouter autant de colonnes que d'attributs possibles, qui sont souvent en grande majorité inutilisés. Pareil quand le nombre de colonnes, même fixe, s'apparente à un tableau (même 1D) de grande taille, genre $aMyArray[4827]. Déclarer autant de colonnes ne se justifie qu'exceptionnellement (ici, il faudrait déjà recompiler SQLite.dll en augmentant la limite du nombre de colonnes qui est de 2000 par défaut).
En pratique, il faut aussi voir qu'un simple INSERT doit : soit passer un grand nombre de paramètres, soit reposer sur des valeurs par défaut au niveau du schéma. De la même manière, on n'a pas systématiquement besoin de toutes ces colonnes lors d'un SELECT. Rapellons que SELECT * FROM ... dépend de l'ordre de définition des colonnes et se révèle atroce si d'aventure l'expérience de la montée en volume montre qu'on devrait réorganiser cet ordre ! Bref, c'est lourdingue, inefficace et gourmand.
On peut recourir dans ce cas à un autre schéma, bien plus souple, reposant sur deux tables :
Code : Tout sélectionner
CREATE TABLE "Stock_2" (
"ItemId" INTEGER NOT NULL PRIMARY KEY,
"Designation" CHAR NOT NULL);
CREATE TABLE "Details_2" (
"ItemId" INTEGER NOT NULL CONSTRAINT "fkStockDetails" REFERENCES "Stock_2"("ItemId") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
"Attribut" CHAR NOT NULL,
"Valeur" CHAR);
Stock_2 reprend les données de base communes requises pour chaque rangée, tandis que la table Détails_2 liste toutes les caractéristiques (éventuellement uniques) de chaque item :
https://dl.dropboxusercontent.com/u/264 ... ils_2.html
On voit bien ici qu'on peut ajouter de nouvelles caractéristiques à la volée sans devoir reconsidérer le schéma et tout le SQL de l'application !
Maintenant la question qui se pose est la suivante : "
c'est certes très joli tout ça, mais comment je fais pour formuler des requêtes et obtenir des résultats exploitables ?"
Et bien, en fait tout dépend de la sémantique de vos champs. En règle générale et pour éviter de draguer un résultat de la forme $aResultSet[4827][23], on peut ne récupérer que ce qui a un sens pour nous, à ce moment-là, genre :
Code : Tout sélectionner
with items as (
select itemid from details_2
where (attribut = 'Col3' and valeur like 'abc%')
or
(attribut = 'Col2' and valeur like '-%')
)
select itemid, designation, group_concat('{' || attribut || ', ' || valeur || '}', ', ') Caractéristiques
from stock_2 natural join details_2
where itemid in items
group by itemid
Le résultat (dans le format que j'ai demandé, mais ce n'est bien sûr qu'un exemple) revient :
https://dl.dropboxusercontent.com/u/264 ... ils_2.html
Mais le bonus maintenant est qu'on peut aussi requérir des caractéristiques nouvelles (hors Col1, Col2 ... Col7) tout aussi aisément :
Code : Tout sélectionner
with items as (
select itemid from details_2
where (attribut = 'Col3' and valeur like 'abc%')
or
(attribut = 'Col2' and valeur like '-%')
or
(attribut = 'Matière' and valeur is not null)
)
select itemid, designation, group_concat('{' || attribut || ', ' || valeur || '}', ', ') Caractéristiques
from stock_2 natural join details_2
where itemid in items
group by itemid
Et le résultat n'est pas plus long a revenir :
https://dl.dropboxusercontent.com/u/264 ... ils_2.html
EDIT:
J'ai oublié de mentionner qu'il y a bien sûr de multiples possibilité de présentation des résultats. Par exemple, une façon plus "table" :
Code : Tout sélectionner
with items as (
select itemid from details_2
where (attribut = 'Col3' and valeur like 'abc%')
or
(attribut = 'Matière' and valeur is not null)
)
select itemid, designation, attribut, valeur
from stock_2 natural join details_2
where itemid in items
order by itemid, attribut
Ce qui nous donne ça :
https://dl.dropboxusercontent.com/u/264 ... ils_2.html
La clause WITH (qui définit une CTE = Common Table Expression, récursive ou non) récemment introduite est particulièrement puissante et adaptée à un grand nombre de cas. On n'a pas besoin de récursivité dans l'exemple présent, mais WITH permet très simplement de parcourir un arbre récursivement et même de faire des choses plus ou moins malsaines, comme le Mandelbrot (assez simple) ou, (bien pire encore) le solveur de sudokus que j'ai posté sur le fofo US.
Bien sûr, dans l'exemple (idiot) proposé, on peut toujours se confectionner une VIEW temporaire et taper dedans avec un SELECT ultérieur, mais l'usage de WITH simplifie considérablement le code et une fois qu'on s'y est fait on a bien du mal à le lâcher.
Un puriste ou un autre va bien penser que stocker à chaque fois l'attribut en clair est un sérieux défaut de normalisation et il aura bien raison. Je n'ai pas présenté une table Attributs séparée, qui serait normalement de bon aloi, avec une clé étrangère de (Détails_2, AttribId) vers (Attributs, AttribId), seulement par souci de clarté dans ce premier temps.
Ne pas perdre de vue que SQL n'est pas impératif mais déclaratif : on décrit le résultat escompté (pas la manière d'y parvenir) et on confie à la machinerie SQL sous-jacente le soin de se debrouiller pour le fournir. Souvent les débutants en SQL estiment à tort que la multiplication des tables impose un travail supplémentaire pour l'obtention d'un résultat donné. Mais c'est bien souvent le contraire et c'est très justement sur le design d'une base de donnée (son schéma) qu'il faut réfléchir en profondeur de prime abord.
Pour transcrire ce qui vient d'être évoqué dans un contexte plus pragmatique, considérons un répertoire international de bonne qualité. Comme je l'évoquais précédemment, une adresse complète correcte peut être d'une grande subtilité et demander un grand nombre d'éléments pour sa constitution, plus une bonne dose d'autres subtilités pour son formattage car l'ordre et la présentation des informations dépend de plusieurs facteurs. Un schéma fixe avec 6 ou 7 champs (ce qu'il est déjà rare de rencontrer) n'est pas du tout adapté à cet usage, pour le moins dans un contexte international sérieux.
La cryptographie d'aujourd'hui c'est le taquin plus l'électricité.
