Discussion :

[Diogon]

Salut à tous,

J'ignore si je suis dans le bon forum. Techniquement je travaille sous MS SQL-Server mais dans la pratique mon problème n'est pas spécifique à un type de SGBD.

Petit litige entre un de mes amis et moi concernant la meilleure manière de mettre en place un système de caractéristiques dynamique.

Je m'explique: nous essayons de mettre en place une sorte de webshop (ou plutôt une banque de données de produits). Nous présentons différents produits en fonction de leur caractéristiques cependant nous nous trouvons face à un problème: comment stocker ces caractéristiques de façon dynamique au sein d'une base de données? sachant qu'une caractéristique peut-être au format numérique (la largeur par exemple), string (la couleur) ou autre. D'autant qu'il faut pouvoir comparer les données d'un même type (il faudrait que je puisse trier les produits en fonction de leur prix, de leur couleur, etc.)

Mon ami est partisan d'utiliser une table au format:

•Id (Identifiant de la valeur)
•XId_caract (Clé étrangère vers la caractéristique en question)
•Type (type de la valeur permettant de savoir dans quelle colonne chercher la valeur)
•Valeur_Entière (contient la valeur si celle-ci est int et null sinon)
•Valeur_Decimale (contient la valeur si celle-ci est decimal et null sinon)
•Valeur_String (contient la valeur si celle-ci est varchar et null sinon)
•Valeur_Date (contient la valeur si celle-ci est datetime et null sinon)
•Valeur_Duree (contient la valeur si celle-ci est timestamp et null sinon)

Il propose en alternative de remplacer les Valeur_[Type] par une clé étrangère qui renvoie vers des tables différentes selon le type (pour éviter de perdre de l'espace) néanmoins j'y suis globalement opposé en raison de la perte d'intégrité référentielle que cela génererait.

Pour ma part, je suis plutôt partisan d'un stockage sous cette forme:

•Id (Identifiant de la valeur)
•XId_caract (Clé étrangère vers la caractéristique en question)
•Type (type de la valeur permettant de savoir le type de la valeur)
•Valeur (contient la valeur sous la forme d'un varchar formaté de manière à permettre la comparaison de type similaires)
Ma méthode souffre bien entendu de défauts elle aussi (entre autre, le fait de placer les données dans un format personnalisé empêche leur accès par des personnes extérieures).

N'arrivant pas à nous décider, je me permet de faire appel à l'équipe pour vous demander quelle est selon vous la meilleure des deux méthode ou, encore mieux, si vous en avez une qui soit meilleure encore.

Je précise que le programme lui-même travaille en orienté objet ce qui restreint partiellement les possibilités.

Un tout grand merci à tous.

[Donpi]

Je vote pour votre solution pour les même raisons que vous.

Cependant avez vous évalué les base de données Objet ?
Je pense que ça réglerait votre litige dans l'oeuf.

A+

[Diogon]

C'est déjà un avis ^^

Pour ce qui est des bases de données objet, bien que je pense que cela corresponde mieux à nos besoins, cela n'est malheureusement pas une option. Nous travaillons en effet dans un envirronnement fermé qui ne nous laisse pas vraiment le choix de nos outils.

[Donpi]

C'est déjà un avis ^^

Pour ce qui est des bases de données objet, bien que je pense que cela corresponde mieux à nos besoins, cela n'est malheureusement pas une option. Nous travaillons en effet dans un envirronnement fermé qui ne nous laisse pas vraiment le choix de nos outils.

Ouais j'en connais un rayon.. je travaille en windev... béééee

Bonne chance pour la suite

[aieeeuuuuu]

Bonjour,

Avoir le modèle complet, ou au moins les tables tournant autour de votre problématique permettrait de mieux répondre à votre question.

Par exemple, vous semblez avoir une table des caractèristiques. :

XId_caract (Clé étrangère vers la caractéristique en question)

Ce qui est une bonne chose. Mais dès lors, pourquoi ne pas y mettre le type ?

J'opterais également pour une seule colonne quel que soit le type.
en complément, un peu de lecture : http://sqlpro.developpez.com/cours/m...n/metadonnees/

[StringBuilder]

Et sinon, pourquoi ne pas prendre la solution de votre amis à l'envers ?

Caractéristique
--------------
Id
Nom (Hauteur, Largeur, Couleur, Poids à vide, Charge utile, etc.)
Type (Dimension, Couleur, Masse)

CaractéristiqueProduit
--------------
Id
Id_Produit

Dimension
---------------
Id_caracterisqueproduit
Valeur

Masse
----------------
Id_caracterisqueproduit
Valeur

Couleur
----------------
Id_caracterisqueproduit
Valeur

[...]

Ainsi :
- Aucune perte d'intégrité référentielle
- Plutôt que d'avoir des types "simples" (nombre, date, texte) je préconise des types "complexes" (largeur ou au moins dimension, couleur, poids, etc.)

En effet, cela vous permettra de comparer des caractéristiques entre produits hétérogènes (tous les produits qui ont une "largeur" de X cm), ou tous les produits bleus.

Cela permet aussi de :
- Réutiliser les mêmes tables de correspondance (énumération des couleurs, etc.)
- Réutiliser dans votre code les mêmes objets pour le rendu des caractéristiques similaires (un sélecteur de couleur sera le même quel que soit le produit, du moment qu'il s'agit d'une couleur)

L'utilisation de valeurs textuelles, je suis contre. Impossible de faire de comparaisons (101 > 2), problèmes de transtypage ("100 cm" n'est pas un nombre), etc.
Ou alors à ce moment, vous stockez vos caractéristiques dans un type XML, et tant pis pour les performances, au moins vous avez quelque chose de propre et utilisable. Ce sera de toute façon pas pire que de faire des cast dans tous les sens.

[Babyneedle]

L'option B.

Si vous regardez comment les CMS (comme Umbraco) accomplissent la même chose, vous verrez qu'ils utilisent une façon de faire assez similaire.

L'approche de votre ami, en introduisant une quantité indécente de valeurs nulles, brise la toute première règle de normalisation. Cette règle n'existe pas pour rien.

[Diogon]

Merci pour vos réponses,

@aieeeuuuuu :
Je n'ai pas mis le reste du modèle parce qu'il va beaucoup changer en fonction de la manière dont les valeurs seront stockées. Ceci dit plus d'infos seraient effectivement utile. Voici donc la structure des données telles qu'imaginées:

TCaracteristiques
Id
Libelle
Description
XIdUnite
[Contraintes]

TUnites
Id
Libelle
Symbole
[Contraintes]

Les [Contraintes] représentes les règles à respecter pour que les valeurs soient valides. Elles doivent être placées à la fois au niveau des unités (on ne peut avoir une valeur négative en kg) et au niveau des caracteristiques (la taille d'un écran ne dépasse normalement pas 3m)

Merci pour la lecture, je vais regarder ça

[Diogon]

@Stringbuilder:

C'était la solution intialement envisagée mais elle est impraticable sachant que notre besoin de dynamisme surpasse même notre besoin de performance. De fait nous ne pouvons absolument pas prévoir quel genre d'informations il nous faudra stocker car les produits ont des caractéristiques et des genre extrêmement variables. Dans ce cadre, mon ami pensait générer les tables de façon dynamique en fonction des besoins mais je m'y suis opposé à cause du trop grand risque de perte de contrôle et l'impossibilité de générer un dao efficace (nous travaillons en orienté objet).

Pour ce qui est du stockage en chaine de caractère, je rappelle que l'idée est d'utiliser un formattage qui permette une comparaison efficace:
Par exemple, les dates seraient stockées au format 'yyyyMMddHHmmss' ce qui permet de les comparer.
Dans le même idée, sachant que nous ne devrions jamais flirter avec des valeurs de l'ordre du million et que la précision des nombres à utiliser ne devrait pas dépasser les 3 décimales, nous stockerions les nombres au format XXXXXXXXXXXX.XXXXXXXXXXXX ou chaque X représente un nombre (102,85 deviendrait 000000000102.850000000000 ce qui rétablirait la possibilité de comparer)

[Donpi]

Si le besoin de dynamisme prévaut sur tout le reste, pourquoi pas le type binary ?

Là vous pouvez stocker absolument tout ce qui vous passe pas la tête.

Pour les comparaisons on pourrait imaginer un objet côté applicatif.
Sachant que jusqu’à 1 million de données une recherche lineraire est encore très rapide.

A+

[Diogon]

Si le besoin de dynamisme prévaut sur tout le reste, pourquoi pas le type binary ?

Là vous pouvez stocker absolument tout ce qui vous passe pas la tête.

Pour les comparaisons on pourrait imaginer un objet côté applicatif.
Sachant que jusqu’à 1 million de données une recherche lineraire est encore très rapide.

A+

Le besoin de dynamisme supplante nos autres besoins mais ça ne veut pas dire que nous n'en avons pas d'autre. Dans l'ordre d'importance, je dirais que nos besoins sont
1. Dynamisme
2. Performance
3. Facilité d'intégration (il ne s'agit que d'une toute petite partie d'un modèle qui est destiné à fortement évoluer)
4. Facilité de maintenance
5. Gain d'espace de stockage (à priori, et à moins de vraiment modéliser comme des pieds, il est peu probable que nous nous retrouvions avec une quantité de données dépassant la moitié de la capacité de notre db)

Notre besoin de performance, bien que moins important que le dynamisme, est bien là. Surtout sachant que, à l'heure actuelle (et bien que cela doive changer) toutes nos reqêtes se font par l'intermédiaire d'un vpn en aller retour (nous -> serveur vpn -> nous) sur une connexion internet assez lente donc on espère tout de même grapiller autant de performance que possible sans nuire au dynamisme

[darkelend]

Sinon il y a le type XML qui est très pratique coté développement. Pour les performances c'est autre chose...

[Diogon]

Pendant un moment j'avais envisagé l'usage du xml ou du json malheureusement ils ne permettent pas la comparaison.

@aieeeuuuuu: merci pour la lecture. Ca ressemble assez à ce que je voulais faire néanmoins j'ai une petite question: qu'est ce qui est le plus rapide? la comparaison sur un string où le cast? parce que avec ta solution, si je veux effectuer une recherche, je suis obligé de caster toute les données de ma colonne valeur vers le type de la valeur de comparaison tandis que mon idée c'était de faire l'inverse, caster la valeur de comparaison vers un string formatté de la même façon que la colonne (format destiné à permettre la comparaison) au sein de l'application puis faire la requête de manière à ne devoir effectuer qu'un seul cast et non plusieurs. Mais si la comparaison de varchar est plus longue, alors peut-être dois-je changer de méthode?
Je précise également que rien ne m'empêche de mettre en place un index sur la valeur

[StringBuilder]

Le type XML permet parfaitement les comparaisons avec SQL Server.
Il y a un type dédié (c'est pas du varchar) qui permet justement de se balader dans les noeuds du fragment XML et faire des opérations dessus.

Exemple :

Code sql :

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
 
drop table produit;
go
 
create table produit
(
	id int not null identity primary key,
	nom varchar(50) not null,
	node xml not null
);
go
 
insert into produit (nom, node) values ('Commode', '<caracteristiques><largeur>125</largeur><hauteur>70</hauteur><profondeur>20</profondeur><couleur>blanc</couleur></caracteristiques>');
insert into produit (nom, node) values ('Stylo', '<caracteristiques><couleur>rouge</couleur></caracteristiques>');
insert into produit (nom, node) values ('Buffet', '<caracteristiques><largeur>200</largeur><hauteur>80</hauteur><profondeur>50</profondeur><couleur>noir</couleur></caracteristiques>');
 
select id, nom, node.value('(/caracteristiques/couleur)[1]', 'varchar(50)'), node.exist('/caracteristiques/largeur')
from produit
where node.value('(/caracteristiques/largeur)[1]', 'int') < 150 or node.exist('/caracteristiques/largeur') = 0;

Récupère tous les produits qui ont une largeur de moins de 150 cm ou qui n'ont pas de largeur.

Résultat :

Code :

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

1
2
3
4
5
6
id          nom                                                                                                   
----------- -------------------------------------------------- -------------------------------------------------- -----
1           Commode                                            blanc                                              1
2           Stylo                                              rouge                                              0
 
(2*ligne(s) affectée(s))

[StringBuilder]

Après, niveau performances, c'est loin d'être génial.
Mais ce sera de toute façon pas spécialement pire que de faire des cast à toutes les sauces, assortis de jointures en nombre important.

A noter aussi que lors de la lecture des noeuds, tu as accès à toute la syntaxe XPath, qui peut tout à fait permettre de simplifier la partie SQL (genre ne comparer la valeur de la largeur que si hauteur et profondeur sont aussi présents, on peut le coder en XPath plutôt qu'en SQL, ce qui sera bien plus rapide et lisible).

[Diogon]

Après, niveau performances, c'est loin d'être génial.
Mais ce sera de toute façon pas spécialement pire que de faire des cast à toutes les sauces, assortis de jointures en nombre important.

A noter aussi que lors de la lecture des noeuds, tu as accès à toute la syntaxe XPath, qui peut tout à fait permettre de simplifier la partie SQL (genre ne comparer la valeur de la largeur que si hauteur et profondeur sont aussi présents, on peut le coder en XPath plutôt qu'en SQL, ce qui sera bien plus rapide et lisible).

Je ne savais pas que SQL possédait un vrai type XML... Je vais tester ça pour voir ce que ça donne. Par contre, la sérialisation n'est-elle pas plus gourmande et lente que le cast?

[StringBuilder]

C'est à benchmarker.

Mais comme je disais, généralement, on ne fait pas une simple recherche "je veux tous les produits de 200cm de large", mais des combinaisons.
Et dans ce cas, entre une palanquée de jointures dynamiques et une petite requête XPath bien sentie, je ne suis pas certain que le XML ne tire pas son épingle du jeu.

[Diogon]

Une dernière question: à priori le xml me semble effectivement être une assez-bonne idée (bien qu'il faille y réfléchir pour peser le pour et le contre). Par contre je n'arrive pas à comprendre comment intégrer ce système au sein d'un dao? Je suppose que je vais devoir manipuler le xml manuellement (puisque la sérialisation n'est ici pas envisageable)?

[StringBuilder]

J'imagine que ton produit aura une liste de caractéristiques du type (nom, valeur).
Reste alors simplement à sérialiser/désérialiser manuellement ta liste en XML. Je ne vois pas de problème majeur, si ?

[hmira]

Une autre méthode consiste à utiliser le type sql_variant pour la colonne stockage des valeurs des caractéristiques (au lieu du varchar qui selon moi est une très mauvaise idée, et je préfère à la rigueur la solution proposée par votre collègue, c.à.d, 3 ou 4 colonnes, une par type de caractéristique (exemple : Integer, Decimal, Varchar, Datetime2).

Le type sql_variant a été introduit depuis SQL Server 2005 pour apporter une solution (de facilité !) à ce genre de problématique, qui rappelons le, n’est pas prévue pour être aisément gérée dans le modèle relationnel classique. Les tables relationnelles ne sont pas destinées à être flexibles en ce sens où le type de l’attribut est variable !

L’utilisation de sql_variant est cependant fortement déconseillée dans les situations "normales" où le domaine de chacun des attributs de la relation peut être déterminé sans ambiguïté.

Un des attraits du type sql_variant est qu’il stocke les données dans leur format natif. Encore faut-il que vous ayez une maîtrise totale du format natif des données que vous allez insérer dans vos tables (ces fameuses caractéristiques). Celles-ci proviennent généralement des IHM (écrans de saisie etc..).

Une bonne utilisation du type sql_variant est de lui adosser à un « Wrapper » qui sera chargé de contrôler et d’insérer les valeurs des caractéristiques. Ce "Wrapper" peut se résumer ainsi :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
BEGIN TRY 
	 BEGIN TRAN 
-- l’instruction Insert ci-dessous ne pourra être exécutée qu’en SQL Dynamique. En effet, le type natif attendu (Type_Natif de type 
-- sysname ( exemeple 'decimal(18, 6)' devra être préalablement stockée la table des caractéristiques celle que vous avez nommé TCaracteristiques) 
	    INSERT INTO  (...) 
	 Values 
	    CAST(….  As Type_Natif); 
	 COMMIT;  
 END TRY 
 BEGIN CATCH 
	ROLLBACK;  
 END CATCH

Remarque :
- L’instruction Insert ci-dessus ne pourra être exécutée qu’en SQL Dynamique. En effet, le type natif attendu Type_Natif de type sysname (exemeple 'decimal(18, 6)' ) devra être préalablement stockée la table des caractéristiques celle que vous avez nommé TCaracteristiques).
- Le plus important est l’instruction CAST(… As Type_Natif) qui assure que les données insérées sont conformes au type natif sql server attendu.


A+