Champs personnalisés dans une base SQL

Hello,

Je me pose une question quant à l’organisation d’une fonctionnalité du produit sur lequel je bosse.

J’aimerai trouver un compromis correct pour les performances en priorité, mais aussi faire en sorte que ce soit assez simple d’usage/compréhension.

Globalement on veut permettre la définition de champs personnalisés (sur des contacts par exemple, mais on va appliquer le même principe sur d’autres entités) via une interface d’administration. Sachant que chaque organisation (groupe d’utilisateurs) partagera ces définitions, mais que plusieurs organisations sont sur la même base de données.

Pour l’instant on est parti sur l’idée de gérer les définitions de façon plutôt classique : une table à part (pour chaque entité avec des champs custom) avec (id, orgId, label, description, type…) pour faire simple.

On se dit par contre que pour stocker les valeurs (validées à chaque mise à jour de l’entité) on va stocker ça dans un champ JSON de l’entité concernée par ces champs.

J’ai donc deux questions principales :

1. Est-ce pour vous une implémentation raisonnable (sachant qu’on pas forcément prévu de faire de recherche hyper poussée sur ces champs pour le moment, mais que c’est pas exclu à l’avenir non plus)
2. Est-ce qu’il vaut mieux stocker sous la forme [ { customFieldId: "<foreign_key>", "value": "<la_valeur_du_champ>"} ] ou plutôt directement [ { "<foreign_key>": "<la_valeur_du_champ>" } ] selon vous ?

Pour info, à l’affichage on part surtout sur du formulaire, avec l’ensemble des champs custom affichés à la fois, qu’ils aient une valeur définie pour l’entité cible ou non.

Salut,

Je me suis beaucoup posé cette question, moi aussi, dernièrement. Est-ce que tu vas utiliser un ORM, et si oui, est-ce qu’il gère cette possibilité ?

Je m’étais posé la question avec Django, mais il ne permet pas du tout d’utiliser de système dynamique. Même avec un champ json, il faut hardcoder à un moment les noms de champ dans les requêtes.

Par contre, j’ai appris que PostgreSQL propose un champ json et supporte les index sur ce champ (mais je n’en sais pas plus), C’est assez intéressant à savoir.

On utilise typeorm oui, mais c’est pas trop la question vu qu’on peut toujours gérer les requêtes nous-mêmes s’il le faut.

On utilise effectivement Postgre donc le JSON devrait fonctionner mais je me demande ce qui est plus efficace entre avec les id dans une propriété "statique" du JSON, ou l’utiliser directement comme clé de la valeur qu’on veut stocker (qui peut avoir différents types suivant la définition du champ custom associé).

À moins que les versions récentes de PostgreSQL n’aient sérieusement amélioré les choses, les fonctions avancées (recherche, indexation…) sur les champs JSON existent mais sont pénibles d’utilisation et pas très performantes. En tous cas c’était comme ça y’a 3 ans. Conséquence : ça dépanne bien et c’est très pratique pour les cas d’usages que tu décris, tant que tu n’as pas besoin de hautes performances (hors lecture/écriture du champ JSON en entier) ni que tu as besoin de requêtes sur le contenu des JSON.

En tout état de cause, je partirais directement sur [ { "<foreign_key>": "<la_valeur_du_champ>" } ], ça me parait beaucoup plus logique que de rajouter un niveau d’indirection comme dans [ { customFieldId: "<foreign_key>", "value": "<la_valeur_du_champ>"} ] – à moins que tu aies une raison majeure d’utiliser cette forme, mais là comme ça je ne vois pas.

Enfin et surtout, ce qui me fait tiquer, c’est ça :

Sachant que chaque organisation (groupe d’utilisateurs) partagera ces définitions, mais que plusieurs organisations sont sur la même base de données.

Ça m’a l’air d’être un système multi-tenants. Si c’est le cas, autant que possible on essaie de séparer les tentants au maximum, par exemple en isolant chacun d’entre eux dans un schéma séparé. C’est un peu plus compliqué en général (et ça demande de maintenir un pool de connexions à N schémas, avec possibilité de taper dynamiquement dans un schéma), mais ça a plein d’avantages ensuite : isolation des données (évite les risques de fuite entre tenants), facilité de traiter les données d’un tenant en particulier (export, migration sur une autre instance…). Et, dans le cas qui nous intéresse, possibilité de créer des structures dédiées pour chaque tenant (par exemple pour contenir leurs champs personnalisés) sans impacter les autres tenants.

Bien sûr c’est à étudier selon tes besoins en particuliers. De même que la question des performances des champs JSON : peut-être qu’elles seront largement suffisantes pour ton application dans tout futur crédible.

Tu parles du type JSON ou bien du type (recommandé) JSONB ? En principe le type JSONB supporte bien l’indexation (sur l’accès à une clé particulière ou un index inversé général) et ses performances devraient être correctes. Je suspecte que l’ancien type JSON ne soit pas si optimisé quand je lis la doc :

The json and jsonb data types accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

Merci pour les réponses !

On fait effectivement du multi-tenant, mais on compartimente rien depuis le début donc un peu trop tard pour changer (j’ai pris le projet en marche, compliqué de tout refaire dans l’immédiat, mais ce sera peut-être un chantier pour quand on grossira).

Je note par contre je bien faire attention au type JSONB ! Les autres champs JSON qu’on utilise déjà sont déjà au bon format, je pense que Typeorm fait le choix pour nous (les migrations sont générées automatiquement depuis nos déclarations d’entités).

J’essayais de chercher des cas où la notation "longue" poserait problème aussi, mais à part la lecture de prime abord qui est pas très explicite je vois pas non plus.