[PostgreSQL] Format d'attribut pour auto-jointure récursive

Version imprimable

12/06/2019, 15h23
MaitrePylos

[PostgreSQL] Format d'attribut pour auto-jointure récursive

Bonjour,
Dans ma conceptualisation, j'aurais un attribut reprenant un ensemble de id de la table elle même.
Quelle est le meilleur type d'attribut afin de stocker (array,json....) ces id et ensuite faire une auto jointure pour retrouver l'ensemble de mes records.

D'avance merci.
12/06/2019, 16h02
al1_24

J'aurais plutôt vu une table complémentaire :

Citation:

table LIAISON
( ID foreign key references MATABLE(ID)
, ID_LIE foreign key references MATABLE(ID)
, primary key (ID, ID_LIE)
)
12/06/2019, 16h09
MaitrePylos

Hum, je me disais que j'aurais plutôt pu partir sur un format json et faire une cte.
Je ne vois pas ce qui est le plus simple.
12/06/2019, 18h05
al1_24

Quel que soit le format sous lequel elle est stockée, une liste d'identifiants dans une colonne c'est à terme une source de problèmes :roll:
Lourd pour faire une recherche à l'intérieur, pas de contrôle d'intégrité, besoin d'une procédure pour utiliser dans une requête, etc... :aie:

En fait , avec la requête suivante j'obtient ma donnée , mais je vais l'utiliser pour peupler une table intermédiaire comme vous me le conseillez, cela me semble le plus logique.
Code:

1 2 3 4 5 6 7 8 9 10 with h as ( SELECT id_cours,libelle, regexp_split_to_table(cours_ue, ';') as aa FROM cours where niveau = 'U' -- and id_cours = 12591 ) select h.libelle as ue,c.libelle as aa from h left join cours c on c.id_cours::text = h.aa

14/06/2019, 08h50
SQLpro

Citation:

Envoyé par MaitrePylos

Hum, je me disais que j'aurais plutôt pu partir sur un format json et faire une cte.
Je ne vois pas ce qui est le plus simple.

C'est un viol de la première forme normale…. Un attribut ne doit contenir que des valeurs atomique !

A +
14/06/2019, 08h55
MaitrePylos

Citation:

Envoyé par SQLpro

C'est un viol de la première forme normale…. Un attribut ne doit contenir que des valeurs atomique !

A +

Certe, mais les dettes techniques nous obligent à certaine contorsion pour survivre en attendant une nouvelle normalisation.
14/06/2019, 19h09
escartefigue

Les dettes techniques ? J'avoue ne pas comprendre et je suis du même avis que al1_24, une table en liaison 1,n est préférable à tout point de vue :)
14/06/2019, 20h52
StringBuilder

Je ne vois pas d'où vient cet entêtement à vouloir absolument mettre en place une usine à gaz pour gérer un truc aussi simple...

Une table de relation, point barre.

Les avantages sont multiples :
- Requêtage simplifié
- Intégrité des données garantie
- Evolutivité pour stocker des informations sur la relation
- Mise à jour de masse aisée (genre si un ID doit dispaître, c'est plus facile de faire un "delete matable where id2 = X" plutôt que de décoder toutes les valeurs JSON stockées dans une colonne, vérifier si la valeur est dedans, puis resérialiser les lignes modifiées..)
14/06/2019, 21h06
MaitrePylos

Alors, pour la dette techniques.
Disons, que je dispose d'une DB, absolument pas normalisé, qui date du siècle dernier.
Exemple de date 1011223 ou 971223 qui respectivement est 2001/12/23 ou 97/12/23
Et pour ma demande c'est parce que j'ai dans une table une ligne qui comprend un attribut avec ce genre de donnée en texte ';565;9876;6543;89987; qui chaque nombre représente un id de la table elle même.
Alors oui, nous avons plusieurs dizaine d'applications qui utilisent cette db, alors je peux faire la révolution, mais doucement.
Si pour vous c'est un truc simple....mon existant ne l'est pas trop...et je passe les détails de valeurs inexistantes et tout le toutim....donc oui, un Json dans lequel je pouvais faire une recherche me semblais une piste....c'est d'ailleurs la raison de ma demande ici, pour avoir vos retours
15/06/2019, 15h16
StringBuilder

A mon sens, le JSON est inadapté ici.

Le JSON sera intéressant pour stocker des données structurées, homogènes ou non, dont les attributs sont des informations qui sont rarement filtrées, rarement mises à jour, et qui n'ont pas de contraintes d'intégrité (c'est à dire dont les données ne dépendent pas d'autres données stockées dans la base).

Par exemple, pour un dossier médical, on pourrait stocker les résultats n'analyses médicales des patients sous forme JSON pour pouvoir les ressortir aisément le taux moyen d'albumine dans les urines de tel patient entre telle date et telle date.
En revanche, il ne faudrait surtout pas faire des requêtes du type "dans quelle région avons-nous le plus souvent un taux trop élevé d'albumine dans les urines".

Dans votre cas, la liste en question est une liste d'identifiants d'une table.
Ceci implique que cette liste servira régulièrement lors de requête de jointures, et sera potentiellement souvent mise à jour.
Les performances seront alors fortement dégradées, et la complexité du code sera lourdement impactée. Sans oublier les risques énormes de non respect des contraintes (présence d'ID inexistants dans la liste).

Je vous conseille grandement de partir sur une table séparée, quitte à conserver une colonne calculée, ou une vue, permettant de continuer à disposer et utiliser l'ancienne colonne.
Un peu de lecture ici, qui pourrait vous donner des pistes pour améliorer votre base de données tout en révolutionnant sans trop révolutionner ;)
https://www.developpez.net/forums/bl...-base-donnees/

Et c'est bien vers une nouvelle table que j'ai orienté ma réflexion finale.
J'ai donc créé la table suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
 
-- object: brain.ue | type: TABLE --
-- DROP TABLE IF EXISTS brain.ue CASCADE;
CREATE TABLE brain.ue(
	id_ue serial NOT NULL,
	ue integer,
	aa integer,
	CONSTRAINT ue_pk PRIMARY KEY (id_ue)
 
);
 
-- object: "cours.id_cours" | type: CONSTRAINT --
-- ALTER TABLE brain.ue DROP CONSTRAINT IF EXISTS "cours.id_cours" CASCADE;
ALTER TABLE brain.ue ADD CONSTRAINT "cours.id_cours" FOREIGN KEY (ue)
REFERENCES brain.cours (id_cours) MATCH FULL
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
-- ddl-end --
 
-- object: "cours.id_cours1" | type: CONSTRAINT --
-- ALTER TABLE brain.ue DROP CONSTRAINT IF EXISTS "cours.id_cours1" CASCADE;
ALTER TABLE brain.ue ADD CONSTRAINT "cours.id_cours1" FOREIGN KEY (aa)
REFERENCES brain.cours (id_cours) MATCH FULL
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
-- ddl-end --

Remplis avec la requête suivante :

Code:

1
2
3
4
5
6
 
insert into brain.ue (ue,aa)
select id_cours,cast(regexp_split_to_table(cours_ue, ';') as int) as aa
from brain.cours 
where niveau = 'U'
and cours_ue != ''

Merci de votre aide et réflexion.