Aller au contenu

Ressources

Administration/gestion/modélisation des bases de données

Ecosystème PostgreSQL

Documentations

Commandes SQL

Extensions dans postgreSQL

Exploitation

Bonnes pratiques en matières de base de données

Quelques conseils en matière de base de données :

Des constantes

  • Le nom des données, des schémas, des tables et des attributs doit être en minuscule, au singulier, sans caractère accentué, sans espace. Il doit être parlant sans être verbeux. L'utilisation d’abréviation est déconseillée
  • Les schémas, les tables et les colonnes porteuses d'informations doivent être commentés. Le commentaire doit citer l'unité de mesure, pour les données numériques
  • L'utilisation de script SQL doit être privilégiée. Il doit être commentés et contenir les entêtes minimum suivantes (auteur / date de création / date de dernière mise à jour / utilité). Il doit être archivé, dans un gestionnaire de version de code si possible, ou dans un système de sauvegarde au moins journalier

Des options

  • Le nom des objets et le commentaire doivent être en anglais
    • Avantage : langue internationale
    • Inconvénient : langue non maitrisée par tous (les nuances mises dans les commentaires peuvent être mal interprétées)
  • La clef primaire doit être de type "serial" et nommée "table_id".
    • Avantage : porte sur une seule colonne et plus rapide pour l'indexation
    • Inconvénient : non significative
    • Inconvénient : l'unicité métier de l'enregistrement est à la charge du développeur
  • La clef étrangère doit avoir le même nom que la clé primaire de la table référente (table_id)
    • Avantage : jointures simplifiées avec l'instruction join using (table_id)
    • Avantage : non redondance du champ (table_id) pour les select *
    • Inconvénient : il est moins évident de retrouver les tables liées à partir du nom des colonnes (pas d'information de type table_id_fk)
  • Le nom des vues doit être préfixé par "v_"
    • Avantage : plus facile de les repérer
    • Inconvénient : peu d'intérêt de les repérer
  • Le nom des attributs doit être préfixé par le nom de leur table et séparé par des _ (le préfixe est obligatoire s'il y a une ambiguïté possible avec d'autres tables)
    • Avantage : lecture des requêtes facilitée
    • Inconvénient : nom à rallonge
  • Le nom des tables doit être aliaisé par une abréviation (l'utilisation d'alias est indispensable si la même table est utilisée plusieurs fois dans la même requête)
    • Avantage : facilite la lecture de la requête
    • Inconvénient : compromis à trouver entre longueur de l'alias et signification

Comment concevoir une base de données

Outils d'administration de base de données

Il existe de très nombreux clients graphiques pour administrer une base de données PostgreSQL. Nous vous en avons sélectionné quelques uns :

pgAdmin4

  • le client "officiel" de PostgreSQL
  • une plate-forme d'administration et de développement
  • 2 modes : mono utilisateur (desktop) et multi-utilisateur (web)
  • disponible sur Linux, Mac et Windows

Avantages

  • possibilité de visualiser toutes les bases de données hébergées dans le serveur
  • fonctions d'attribution des droits (droits par défaut, et wizards d'affectation des droits a posteriori)
  • des graphes de fonctionnement un peu plus complets
  • des fonctions d'administration plus poussées (déclenchement manuel de vacuum, création des foreign data wrappers, etc)

Le logiciel est mature mais il est peut être un peu gourmand (CPU) pour une utilisation en tant que client PostgreSQL. Privilégier le plutôt pour l'administration et la maintenance (visualisation des processus, des verrous ou de la charge)

DBeaver

  • un client Postgres mais pas que (MySQL, MariaDB, SQLite, MS Access, Oracle,...)
  • disponible sur Linux, Mac et Windows
  • outil assez intuitif

Avantages

  • possibilité d'activer le chiffrement du mot de passe
  • utilisation du fichier .pgpass qui permet de mutualiser les mots de passe avec les outils en ligne de commande de PostgreSQL
  • de nombreux drivers pour se connecter à d'autres bases de données (par exemple H2)
  • visualisation du schéma Entité Relation
  • visualisation des points géographiques sur une carte
  • fonction d'import des données avec des paramètres de mapping entre la base et le fichier d'import
  • fonction d'autocomplétion lors de l'écriture de requêtes
  • possibilité d'affecter un code couleur aux connexions (développement, pré-production, production par exemple)

Inconvénients

  • parfois instable

QL Workbench/J

  • application écrite en java (donc multi-plateforme)
  • compatible avec quasiment toutes les bases de données.

Avantages

  • support natif des transactions (commit, rollback), ce qui permet de se rattraper en pas d'erreur, et de valider des scripts complexes
  • possibilité d'automatisation avec des variables injectables dans le script
  • fonctions d'exportation (presse-papier, csv, xls, ods)
  • fonctions d'importation
  • possibilité de "suivre" un enregistrement vers ses parents/enfants
  • fonctionnalité de formatage SQL

Inconvénients

  • pas de dessin de la base de données

Oracle SQL Developper Data Modeler

  • application écrite en java (donc multi-plateforme)

Outils de migration de bases de données

Migrer d'Oracle à PostgreSQL

Migrer Access vers PostgreSQL (MS Access to PostgreSQL Conversion)

Remarque : l'utilitaire ne permet pas de récupérer les relations car Access les gère de façon non standard. Sur le blog d'Ivan Cachicatari's Blog @latindev vous trouverez un script VBA "Converting MS Access to MySQL with relationships" qu'il suffit d'adapter pour PostgreSQL : remplacer les ` (prévu pour mysql) par deux double quote "" et rajouter le nom du schéma si nécessaire (Merci à Olivier Lobry pour l'adaptation). Ce script génère des requêtes SQL qu'il faut exécuter sur PostgreSQL une fois les tables générées.

Pour valider votre migration, vous pouvez utilisez l'outil Pgmodeler qui permet de faire de la rétroconception de base de données PostgreSQL

Migrer Access vers MariaDB

Outils de modélisation de bases de données

Il existe de nombreux logiciels pour modéliser sa base de données. Nous vous en avons sélectionné quelques uns.

pgModeler

  • support exclusif mais complet de Postgresql pour dessiner sa base de données

Avantages

  • outil le plus complet à notre connaissance
  • compilation possible sur toutes plates-formes
  • rétro-ingénierie

Inconvénients

  • compilation à faire soi-même (existence d'une version compilée payante)
  • scripts de génération à retoucher parfois (mais à la marge)
  • temps de prise en main (surtout pour maîtriser toutes les subtilités du programme) assez long

Looping

  • Modélisation Entité/Association et Diagramme de Classes UML
  • Logiciel gratuit et libre d’utilisation - Université Toulouse III

Avantages

  • travaille au niveau d'un modèle conceptuel de données
  • génération automatique du modèle logique de données et du script SQL
  • s'adapte à tout les SGBDR (soit prédéfinis, soit paramétrable)

Inconvénients

  • pas de rétro-ingénierie des modèles, prévu dans la version 4.1
  • lisibilité des modèles diminue avec leur complexité
  • absence de documentation présentant l'utilisation du logiciel

Autres outils

Quel SGBD conseiller en matière de pérennisation ?

Socle interministériel de logiciels libres

Documentation SILL
Les référents Socle Interministériel de Logiciels Libres (SILL) ministériels et DINUM (19/12/2023)

Support de cours

Support de cours Merise

  • Ingénierie des systèmes d'information : Merise deuxième génération, Dominique Nanci & Bernard Espinasse, 4e édition - 2001, pdf

Support de cours NoSQL

Support de cours PostgreSQL

Support de cours UML

Cas d'utilisation (Delphine Longuet / Université Paris Sud) - Diagrammes de cas d'utilisation - Scénarios détaillés et diagrammes de séquence - phase analyse

Diagrammes de classes (Delphine Longuet / Université Paris Sud) - Classes et associations - Associations particulières et héritages - Contraintes

Diagrammes de séquence (Delphine Longuet / Université Paris Sud) - Diagrammes de séquence en conception

Support de cours FileMaker

Une participante du réseau rBDD, spécialiste de FileMaker, propose un soutien aux utilisateurs pour mieux comprendre cet outil. Elle a rédigé cinq fiches pédagogiques en version FileMaker 16, abordant des notions de base en SGBDR, l'introduction au scripting, les principes de sécurité, et la publication de rapports hiérarchisés.

Webinaire (chaine CANAL U RBDD)