Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#1 Fri 23 November 2018 09:06

EmilieCCBE
Participant actif
Date d'inscription: 22 Nov 2018
Messages: 80

PostgresSql - Enumeration, domaine ou table relationnelle ?

Bonjour à tous,
je suis en train de construire une base de données, sous PostgresSQL/Postgis au sein d'une petite collectivité. Je me confronte à une question de structuration des données que l'on pourrait qualifier de 'référentielles'.

Mon "problème" est le suivant. Dans beaucoup de tables, je dois gérer des champs dont les modalités doivent être restreintes à un certain nombre de modalités. Pour illustrer le propos, j'ai par exemple une table 'ass_regard' qui est composée, entre autre, du champ 'typ_fonte' dont les valeurs doivent être comprise dans la liste : 'av200', 'tv650', 'WV680' (que les puristes de l'eau et de l'assainissement ne m'en veuille pas si les exemples sont fictifs), un champ 'typ_res' qui peut prendre les valeurs 'eu', 'eplu', 'ep'. Et cela se produit pour la plupart de mes tables. Certains domaines de valeurs sont communs à plusieurs tables mais beaucoup sont très spécifiques. J'ai une autre contrainte majeure. Ces données entrent dans les menus déroulants préparés pour de la saisie dans QGIS. Je voudrais donc ne pas avoir à modifier liste de valeurs des champs de un ou plusieurs formulaires à chaque fois que les valeurs sont modifiées.

J'entrevois plusieurs options pour gérer le problème.
1. Passer par un système de tables 'référentielles' (par ex. des tables 'ref_typ_fonte' et 'ref_typ_res') avec un système de clés étrangères vers les tables 'objet' (par exemple ma table 'ass_regard'). Cela me permet d'avoir des modifications dynamiques de mes formulaires mais cela complexifie mon MCD avec un nombre très important de tables.
2. Créer des domaines ou des types énumération pour simplifier mon MCD. Mais je perd le lien dynamique avec mes formulaires QGIS, ce qui à terme peut me poser des problèmes non négligeables pour la maintenance globale du système et des postes de travail.

Bref je suis plutôt dans l'optique de partir pour la création de tables référentielle pour conserver l'aspect dynamique des formulaires. Mais j'aimerais avoir des avis éclairés sur les bonnes pratiques et d'éventuelles solutions auxquelles je n'aurais pas penser.

Merci d'avance pour vos lumières, Emilie

Hors ligne

 

#2 Fri 23 November 2018 09:36

Jean-Yves G
Membre
Lieu: toulouse
Date d'inscription: 12 Oct 2005
Messages: 516

Re: PostgresSql - Enumeration, domaine ou table relationnelle ?

Bonjour,

bienvenue dans le monde de la modélisation BD ... Je dirais que votre premier choix est le bon ... Faire des tables référentielles ... ce n'est pas grave d'avoir bcp de tables , c'est normal ... La plupart des gros SI ont plusieurs centaines de tables.  Mais ensuite , vous pouvez créer des vues , tables virtuelles résultant d'une expression SQL ... QGIS adressera ces vues

Cordialement
JY

Hors ligne

 

#3 Fri 23 November 2018 14:24

p.jeremie
Participant assidu
Lieu: Valence
Date d'inscription: 10 Sep 2017
Messages: 375

Re: PostgresSql - Enumeration, domaine ou table relationnelle ?

Il est également possible de passer par une seul grosse table référentielle qui contient les valeurs, avec une colonne qui permet de différencier le type. Ca fait moins de table mais peut aussi amener des contraintes.

Hors ligne

 

#4 Mon 26 November 2018 08:55

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 Sep 2005
Messages: 3163
Site web

Re: PostgresSql - Enumeration, domaine ou table relationnelle ?

Bonjour,

Votre première idée est la bonne. Pourquoi ?

Le gain de place : en effet une valeur de type texte 'av_200' va comprendre (suivant l'encodage) 6*2 octets, soit 12 octets.
Si vous avez 1000 enregistrements qui reprennent cette valeur vous aurez 12 ko (en simplifiant).
Si vous optez pour votre idée vous aurez :
dans une table référence une ligne de type entier , texte soit : 4 octets + 12 octets , soit 16 octets, et 1000* 4 octets (dans ass_regard) soit 4 016 octets au lieu de la première solution à 12 000 octets.

L'indépendance des attributs : si demain matin la valeur 'av_200' est changée, elle devient 'av_201', avec la solution 'une table' vous avez 1000 mise à jour à faire, avec la solution 'tables référentielles' une seule mise à jour.

La solution de p.Jeremie est valable aussi mais comme il le souligne cela amène d'autres contraintes et cela complexifie les requêtes.


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#5 Mon 26 November 2018 10:38

EmilieCCBE
Participant actif
Date d'inscription: 22 Nov 2018
Messages: 80

Re: PostgresSql - Enumeration, domaine ou table relationnelle ?

Bonjour.

Merci pour vos réponses argumentées. Elles me sont d'une grande aide dans ma réflexion. Je vais donc rester sur ma première option.
Belle journée.

Emilie

Hors ligne

 

#6 Tue 27 November 2018 12:35

MathieuB
Membre du bureau
Lieu: Montpellier
Date d'inscription: 18 Jan 2006
Messages: 1220
Site web

Re: PostgresSql - Enumeration, domaine ou table relationnelle ?

Bonjour à tous,

le sujet est intéressant. Pour ma part j'aime, quand c'est possible, que les clés soient porteuses d'information quitte à ce que la taille sur disque de ma table soit impactée, mais il faut dire que je ne gère pas non plus de bases gigantesques.
Les tables remplies de clés étrangères numériques me font peur (c'est peut-être aussi peu rationnel peut-être que la peur des fantômes :-) )

La place gagnée par l'utilisation d'une clé de type entier est à envisager dans l'ensemble de la base et de son utilisation, je veux dire que si vous devez mettre en place une vue matérialisée pour l'exploitation des données vous ne gagnez pas de place.

Dans votre cas et dans mon contexte de travail, si vous ne souhaitez pas utiliser de type énuméré, j'utiliserai aussi une table "référentielle" type_fonte mais contenant comme clé primaire les valeurs 'av200', 'tv650', 'WV680'. Vous contrôlez ainsi l'intégrité de vos données, sans pour autant devoir faire de jointure lors de l’exploitation de vos données.

Mathieu


Mathieu BOSSAERT
Association GeoRezo

Hors ligne

 

#7 Tue 27 November 2018 14:16

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: PostgresSql - Enumeration, domaine ou table relationnelle ?

La solution de Mathieu est un moyen terme intéressant.
Tout cela pour dire que cela dépend des besoins et des contraintes,
en vrac :
Est ce que la table est destinée à être lue/modifiée par un humain ?
Est ce que c'est une table sur laquelle on va effectuer des requêtes
pour explorer/analyser les données ?
Est ce que le volume est un problème ?
Est ce que ma base va croitre rapidement ?
Est ce que les valeurs des colonnes concernées changent, et si oui,
à quel rythme ?

Une fonctionnalité de QGIS qui permettrait d'alimenter des formulaires
avec les énumérations Postgres serait intéressante à implémenter,
même si d'expérience, j'ai le souvenir que maintenir des enums sous PG
était un peu fastidieux (mais cela a pu faire des progrès depuis).

De ce que j'ai pu lire, On réserve souvent les enums à des ensemble de valeurs
dont on est sur qu'elles ne varieront jamais.

Dernière modification par tumasgiu (Tue 27 November 2018 14:19)

Hors ligne

 

#8 Tue 27 November 2018 14:36

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: PostgresSql - Enumeration, domaine ou table relationnelle ?

Avec la solution de Mathieu, selon moi il serait préférable de rendre
la contrainte de clef étrangère deferrable, dans le cas ou
il faudrait modifier une valeur dans la table référence.

Hors ligne

 

#9 Tue 27 November 2018 15:39

MathieuB
Membre du bureau
Lieu: Montpellier
Date d'inscription: 18 Jan 2006
Messages: 1220
Site web

Re: PostgresSql - Enumeration, domaine ou table relationnelle ?

tumasgiu a écrit:

Une fonctionnalité de QGIS qui permettrait d'alimenter des formulaires avec les énumérations Postgres serait intéressante à implémenter


+1

même si d'expérience, j'ai le souvenir que maintenir des enums sous PG était un peu fastidieux (mais cela a pu faire des progrès depuis).


Non ça reste peu pratique.

De ce que j'ai pu lire, On réserve souvent les enums à des ensemble de valeurs dont on est sur qu'elles ne varieront jamais.


+1


Mathieu BOSSAERT
Association GeoRezo

Hors ligne

 

#10 Wed 28 November 2018 08:18

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 Sep 2005
Messages: 3163
Site web

Re: PostgresSql - Enumeration, domaine ou table relationnelle ?

Bonjour,

MathieuB a écrit:

Les tables remplies de clés étrangères numériques me font peur


C'est pas une histoire de fantômes wink
Et je comprends parfaitement.

Pour ma part je dissocie toujours la partie logique du MCD des autres. Si on respecte quelques règles simples de nommage, la traduction en langage naturel est plus simple. La règle que j'applique :
nom_table avec id_nom_table : PK, si nécessaire ptr_nom_table : FK
Il faut pas essayer d'y voir des nombres (des instance des classes) mais une relation entre noms (relation entre classes).

Un regard contient un réseau de type X et une fonte de type Y  (j'y connais rien)
regard coté n
réseau coté 1
fonte coté 1
deux liaisons : réseaux 1 -> n regard ET fonte 1 -> n regard

Sélectionne l'identifiant, des attributs, le type de fonte, le type de réseau de tous les regards :

SELECT id_regard,toto, type_fonte.valeur,type_res.valeur FROM eau.regard,eau.type_fonte,eau.type_res
WHERE id_regard=type_fonte.ptr_regard AND type_id_regard=res.ptr_regard;


Après je suis parfaitement d'accord sur la façon d'envisager les choses en codant des infos dans les clefs c'est une autre façon de voir les choses tout aussi valable.


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#11 Wed 28 November 2018 10:33

MathieuB
Membre du bureau
Lieu: Montpellier
Date d'inscription: 18 Jan 2006
Messages: 1220
Site web

Re: PostgresSql - Enumeration, domaine ou table relationnelle ?

Ah non ce n'est pas le fait d'avoir une table qui ne contient que des clés étrangères, ni de  gérer ça dans les requêtes, qui me fait peur au contraire.

Mais d'avoir une table qui ne contient que des clés étrangères qui sont des entiers "vides" de sens, comme ici pour dire que j'ai une voiture rouge de marque renault, qui roule au sans plomb.

id_vehicule (pk) | nom_vehicule | couleur_objet (FK) | marque_objet (FK) | carubrant (FK)
--------------------+-----------------+------------------------+-----------------------+------------------
         12              | ma voiture      |             3                  |             1                 |           2

Encore une fois ce n'est pas très rationnel comme peur.


Mathieu BOSSAERT
Association GeoRezo

Hors ligne

 

Pied de page des forums

Powered by FluxBB