Données dynamiques dans une table

Bonjour,

J'ai une tableA qui contient
id: 1
titre : test

J'ai cette tableB liée à la tableA
id: 2
id_tableA : 1
value = 45

id: 2
id_tableA : 1
value = "Chaine test"


Comme on peut le voir, la colonne "value" de tableB n'a pas forcément toujours le même type et je ne peux pas tout stocker en chaîne car lorsque j'aurais besoin de requêter il faudra que les filtres/tris/groupes fonctionnent avec les bons types de donnée.

En fait j'ai déjà une idée et j'ai commencé mes tests en stockant "tableB" dans un index ElasticSearch (j'explique ici grossièrement, j'ai fait d'autres choses bien entendu). Tout est dynamique... Par contre les requêtes à l'avenir seront bcp plus compliqués qu'en Linq classique (car je dév mon appli en .net core).

Avant de continuer dans cette direction, l'un d'entre vous aurait déjà-t-il eu ce besoin pour une appli ? et avez-vous trouvé une meilleure solution ? pourquoi pas en structurant les tables différemment ?

Merci pour vos réponses,

Bonjour,

Je ne connais pas ce que vous appelez "index ElasticSearch" mais voici ce que je ferais.

J'ai utilisé cette façon de faire dans le passé, surement pas la meilleure solution mais pour moi cela a résolu ma problématique.
Dans votre tableB, je rajouterais une colonne qui identifierait le Type de donnée ainsi qu'une série de colonnes des différents types pour traiter l'ensemble de vos cas.

Structure TableB
-------------------
id
id_tableA
type_value
value_int
value_varchar
value_Date
....

bonjour,

La question est : pourquoi avoir modélisé ainsi ? y a-t-il une raison particulière ?

Ce type de modélisation offre une certaine souplesse, mais également de piètres performances, et il est compliqué d'en assurer l'intégrité référentielle...

un peu de lecture :https://sqlpro.developpez.com/cours/...n/metadonnees/

il existe des alternatives, mais il faudrait en savoir plus sur le contexte pour mieux vous aider...

Merci pour vos réponses

@LapinClair : c'est la solution que j'avais trouvé au début. Mais après j'ai besoin de tout rassemble sur une ligne de la tableB . Et PIVOT ne permet pas de faire ça avec un type dynamique.

Pourquoi je veux faire ça ?

En fait je prépare une application (une plateforme) qui va permettre de créer ses propres données dynamiquement mais désolé je rentre pas trop dans les détails car j'aimerais commercialiser cette appli.

Bref, le but :
L'utilisateur va dire par exemple qu'il va créer 3 colonnes : colA : varchar(50), colB : (integer), colC : (boolean)

dans table A je vais stocker une "ligne" avec des colonnes communes.
Dans tableB je vais stocker les valeurs pour ces colonnes.

En gros; avec la technique de @LapinClair :

tableA
id 4


tableB
id 1
id_tableA 4
value_varchar mavaleur de colA
value_integer null
value_boolean null

tableB
id 2
id_tableA 4
value_varchar null
value_integer 45 (valeur de colB)
value_boolean null


tableB
id 3
id_tableA 4
value_varchar null
value_integer null
value_boolean true (valeur de colC)


Mais là se pose un gros souci.... Comment réussir, via une procédure stockée ou une view à avoir ce résultat :

MaVue, sur une seule ligne :
id : 1
colA : mavaleur de colA
colB : 45
colC : true


MaVue, une autre ligne :
id : 2
colA : nouvelle valeur
colB : 12
colC : false

etc...

Je suis pas assez bon en sql et procédure pour réussir à faire (si tant est que ce soit possible...) et sans trop tuer les perfs.
Donc ma solution aujourd'hui (qui rendra les requêtes plus complexes par la suite malheureusement) : ElasticSearch

Voilà, j'espère que j'ai bien expliqué mon souci


EDIT

@aieeeuuuuu je me demande si en effet je ne devrais pas revoir l'analyse... En fait il n'y aura pas de relation. On va créer une nouveau type : "MonType" et dire dedans qu'il y a 3 champs.
Quoiqu'il arrive dans mon projet .Net je ne pourrais pas utiliser Linq puisque tout sera dynamique.
Est-ce qu'au bout du compte je devrais pas "générer" des tables à la volée. Au pire dans mon projet qui sera multi tenant je n'aurais jamais plus de 200 tables environ. Soyons fous : 500 tables....

Je vais regarder si en .net il n'y aurait pas des librairies qui me faciliterait un peu la tâche. Sur le principe est-ce complètement fou ou idiot selon vous de créer des tables à la volée ? ça me fait un peu "bizarre" perso mais bon, pourquoi pas ?

Une colonne de type XML pourrait être une piste explorer, potentiellement plus efficace que le modèle actuel, c'est à tester.
ces colonnes dynamiques entreront-elles en jeu dans des filtres, voire des jointure ?

Difficile d'en dire plus, sans en savoir plus

Il n'y aura pas de jointure.

Mais les données seront affichées dans des grilles (composants Grid Angular Kendo UI) ou l'on pourra en effet filtrer/trier et grouper.
Plus que ça, je vais donner la possiblité de "fabriquer" ses graphiques : tu vas choisir un "Type" que tu auras crée, x aggregations, les différentes "séries" et à la fin ça fera une requête utilisable dans un chart (là aussi je vais utiliser les charts de Kendo UI).

La colonne XML va me compliquer bcp les choses. Le principal problème c'est qu'en mode grille je dois "paginer" absolument. Dans ces données dynamiques il peut rapidement y avoir des milliers données et je peux pas tout charger. Les performances comme tu me l'as précisé au début sont importantes pour cette appli.

Bref, j'ai fait des tests hier soir, j'ai notamment trouvé la librairie SqlKata pour me simplifier les choses. Je vais mettre le sujet en résolu car je pense vraiment créer des tables à la volée. C'est pas très académique mais avec cette solution tout devient beaucoup plus facile à développer.
Je pourrais aussi via une API pourquoi pas proposer l'accès à ces tables et en faire une utilisation externe.

Merci en tous cas pour ton intérêt à ma question