😊 👶 ⚕️ Un guide modeste des schémas de base de données 🧕🏼 🌵 ✍️

Géométrie des fleurs par Mookiezoolook

Pour les applications qui évolueront en fonction du trafic et de la complexité, il est extrêmement important de concevoir initialement un schéma de base de données compétent. Si vous faites un mauvais choix, vous devrez dépenser beaucoup d'efforts pour que ce mauvais modèle ne se propage pas aux services et aux contrôleurs de backend et, finalement, au frontend.

Mais comment évaluer quel circuit est le meilleur? Et que signifie «mieux» lorsque nous parlons d'architecture de base de données? L'équipe Mail.ru Cloud Solutions vous invite à suivre les recommandations de Mike Alcha , consultant en développement logiciel. Il nous semble qu'il a résumé assez succinctement quelques principes d'une architecture compétente.

Directeur: " Je pense que nous devrions construire une base de données SQL . " Développeur (comprend-il même de quoi il parle, ou a-t-il simplement vu une sorte de publicité dans un magazine professionnel? ..): « De quelle couleur voulez-vous la base de données? ". Réalisateur: " Peut-être que le lilas a le plus de mémoire . "

Quelques conseils de base

Il est donc important de viser deux choses principales :

Lorsque vous divisez des informations en tableaux, toutes les informations sont stockées.
La redondance de stockage est minime.

Quant au deuxième point: voulons-nous réduire la redondance uniquement en raison d'un problème de taille de stockage? Non, nous le faisons principalement parce que la présence de données redondantes entraîne des problèmes d'incohérence si vous ne mettez pas à jour tous les champs représentant les mêmes informations lors de la mise à jour.

Voici quelques conseils pour se rapprocher d'une bonne architecture :

Utilisez au moins un troisième formulaire normal (dans lequel chaque attribut non clé "doit fournir des informations sur la clé, la clé complète et rien que la clé", selon le libellé de Bill Kent).
Créez la dernière ligne de défense sous forme de restrictions.
Ne stockez jamais des adresses entières dans un seul champ.
Ne stockez jamais le prénom et le nom dans un seul champ.
Définissez des conventions pour les noms de table et de champ et respectez-les.

- Sur quoi travailles-tu?

" Optimiser cette requête SQL." Cela ralentit et les utilisateurs commencent à se plaindre.

- Et un langage obscène dans les commentaires est nécessaire pour l'optimisation?

- Si vous avez vu le code d' origine , vous ne demanderiez pas.

Examinons ces recommandations plus en détail.

1. Utilisez au moins une troisième forme normale

L'architecture de la base de données peut être divisée dans les catégories suivantes:

La première forme normale.
La deuxième forme normale.
Troisième forme normale.
La forme normale de Boyce-Codd.

Ces catégories représentent une classification par qualité. Nous passerons brièvement en revue toutes les catégories et verrons pourquoi au moins une troisième forme normale est nécessaire.

Première forme normale

Pour la première forme normale, chaque valeur de chaque colonne de chaque table de la base de données doit être atomique. Que signifie atomique? Bref, la valeur atomique est une «chose unique».

Par exemple, nous avons un tableau comme celui-ci:

Prénom	nom de famille	âge	domaines
Jhon	Biche	27	{«Conception de site Web», «Recherche de clientèle»}
Marie	Jeanne	33	{«Planification stratégique à long terme», «Recrutement»}
À M	Forgeron	35	{"Commercialisation"}

Ici, la colonne des zones contient des valeurs qui ne sont pas atomiques. Par exemple, dans une ligne John Doe, le champ stocke deux entités: la conception de sites Web et la recherche de clients.

Ce tableau n'est donc pas dans la première forme normale.

Pour l'amener à ce formulaire, une seule valeur doit être stockée dans chaque champ .

Deuxième forme normale

Dans la deuxième forme normale, aucune colonne qui ne fait pas partie de la clé primaire (ou qui peut faire partie d'une autre clé primaire) ne peut être dérivée de la plus petite partie de la clé primaire .

Qu'est-ce que ça veut dire?

Supposons que vous ayez une telle architecture de base (j'ai souligné les champs correspondant à la clé primaire dans ce tableau):

employé_id	project_id	Heures	Nom de l'employé	nom du projet
1	1	dix	John	«Conception de site Web»
2	1	vingt	Marie	«Conception de site Web»

Dans ce projet, le nom de l'employé peut être directement déduit de employeee_id, car l'idée est que le nom de l'employé est uniquement déterminé par son identifiant.

De même, le nom du projet est identifié de manière unique par l'identifiant project_id.

Ainsi, nous avons deux colonnes qui peuvent être déduites de la partie clé primaire.

Chacun de ces exemples suffirait à jeter ce tableau hors de la deuxième forme normale.

Une autre conclusion est que si la table était dans la première forme normale et que toutes les clés primaires sont des colonnes uniques, la table est déjà dans la deuxième forme normale.

Troisième forme normale

Pour que la table corresponde à la troisième forme normale, elle doit être dans la deuxième forme normale, alors qu'il ne doit y avoir aucun attribut (colonnes), à l'exception du principal, qui dépend transitoirement de la clé primaire.

Qu'est-ce que ça veut dire?

Disons que vous avez l'architecture suivante (ce qui est loin d'être idéal):

Nom de l'employé	employé_id	âge	numéro_département	Nom du département
John	1	27	123	"Commercialisation"
Marie	2	33	456	"Opérationnel"
À M	3	35	123	"Commercialisation"

Dans ce tableau, department_number peut être déduit de employee_id et department_name peut être déduit de department_number. Donc, nom_département dépend transitoirement de l'employé_id!

S'il existe une telle dépendance transitive: id_employé → numéro_département → nom_département, alors cette table n'est pas sous la troisième forme normale.

Quels problèmes surviennent à cause de cela ?

Si le nom du service peut être dérivé de son numéro, le stockage de ce champ pour chaque employé entraîne une redondance excessive.

Imaginez que le service marketing change de nom pour "Marketing et ventes". Pour maintenir la cohérence, vous devrez mettre à jour la cellule de chaque ligne du tableau pour chaque employé de ce département! Dans la troisième forme normale, cela ne serait pas arrivé.

De plus, voici ce qui se passe si Mary décide de quitter l'entreprise: nous devons supprimer sa ligne du tableau, mais si elle était la seule employée du service des opérations, alors le service devra également être supprimé.

Tous ces problèmes peuvent être complètement évités sous une troisième forme normale.

Les exploits de maman . Le nom de sa fille est Help! Je suis forcé de truquer les passeports

2. Créer la dernière ligne de défense sous forme de restrictions

La base de données avec laquelle vous travaillez est plus qu'un simple groupe de tables. Certaines fonctionnalités y sont intégrées. Beaucoup de ces fonctionnalités contribuent à garantir la qualité et la précision des données.

Les restrictions définissent les règles, les valeurs pouvant être saisies dans les champs de la base de données.

Lors de la définition de relations dans une base de données, veillez à définir des contraintes de clé étrangère.

Veillez à spécifier ce qui doit se produire lors de la suppression et de la mise à jour d'une ligne associée à d'autres lignes dans d'autres tables (règles ON DELETE et ON UPDATE).

Veillez à utiliser NOT NULL pour tous les champs qui ne doivent jamais être annulés. Il peut être judicieux de définir une vérification sur le backend, mais n'oubliez pas que les plantages se produisent toujours, donc l'ajout de ce type de restriction ne nuira pas.

Définissez les limites de vérification CHECK pour vous assurer que les valeurs du tableau sont dans la plage acceptable, par exemple, le prix d'un produit a toujours une valeur positive.

Un fait intéressant : en avril 2020, c'est une telle restriction dans les logiciels qui a empêché la négociation sur le MICEX de Moscou car le prix des contrats à terme sur le pétrole WTI est tombé en dessous de zéro. Contrairement à la bourse de Moscou, le New York Mercantile Exchange NYMEX a mis à jour le logiciel une semaine avant l'incident , de sorte qu'il a pu mener à bien les transactions à un prix négatif, c'est-à-dire avec un supplément à l'acheteur du vendeur - env. trans.

Toutes les limitations de PostgreSQL peuvent être trouvées ici .

3. Ne stockez jamais des adresses entières dans un seul champ

Si votre application ou votre site Web a un formulaire avec un champ où l'utilisateur entre son adresse, cela sent mauvais. Il est très probable que dans ce cas, vous disposiez également d'un champ dans la base de données pour stocker l'adresse sous la forme d'une chaîne simple.

Mais que faire si vous devez combiner les achats des clients par ville afin de voir quelle ville quel produit est le plus populaire? pouvez-vous le faire?

Ce sera très dur!

Étant donné que l'adresse complète est stockée sous forme de chaîne dans le champ de la base de données, vous devrez d'abord déterminer la proportion de cette chaîne dans la ville! Et c'est une tâche presque impossible, étant donné tous les formats d'adresse possibles dans ce domaine.

Par conséquent, assurez-vous de diviser le champ universel "Adresse" en champs spécifiques: rue, numéro de maison, ville, région, code postal, etc.

Un autre problème d'adresse - Champs anonymes

Voici une illustration tirée du livre de Michaels Blach, The Copper Bullet pour améliorer la qualité des logiciels:

Quels problèmes potentiels sont visibles ici? Pouvez-vous facilement distinguer la ville de Chicago des rues de Chicago? Probablement pas.

Par conséquent, n'oubliez pas de toujours donner des noms de colonne clairs à chaque unité d'information.

Comment rédiger un CV

- Avez-vous de l'expérience en SQL?

- Non (non).

- Alors écrivez: expert NoSQL.

4. Ne stockez jamais le prénom et le nom dans un seul champ

Similaire à la situation avec les adresses: le nombre de variations du nom et du prénom est trop important pour les distinguer clairement.

Bien sûr, vous pouvez séparer le nom du nom de famille, s'il y a un espace entre eux.

Par exemple, «Mike Alche» → le nom «Mike» et le nom de famille «Alche».

Mais que se passe-t-il si l'utilisateur entre un deuxième prénom? Ou a-t-il un double nom de famille? Mais qu'en est-il s'il y a un deuxième prénom et un double nom de famille?

Comment déterminer où est le nom et où est le nom de famille pour diviser la chaîne? Les erreurs sont inévitables.

Un moyen d'éviter de nombreux problèmes consiste à créer des champs distincts (dans les formulaires) pour les noms d'utilisateur prénom et nom. De cette façon, vous autorisez les utilisateurs à partager leurs propres noms et pouvez stocker des données de manière cohérente.

Remarque: Je ne dis pas que les espaces sont interdits dans les champs de la base de données. Par exemple, pour des noms tels que Juan Martin Del Potro, la première partie de Juan Martin est dans le champ first_name et Del Potro est dans le champ last_name. Bien sûr, ce n'est pas parfait . Vous pouvez éventuellement avoir les colonnes middle_name et second_last_name. Pour en savoir plus sur les variations possibles des prénoms et des noms de famille, consultez la liste « Idées fausses des programmeurs sur les noms » et l'article « Idées fausses des programmeurs sur les noms - avec des exemples » Vous devez vous mettre d'accord sur une sorte de compromis entre précision et praticité.

5. Définissez des conventions pour les noms de table et de champ et respectez-les

C'est assez ennuyeux de travailler avec des données qui ressemblent à user.firstName, user.lst_name, user.birthDate et ainsi de suite.

Je vous conseillerais d'établir des règles de dénomination de soulignement, car tous les moteurs SQL ne gèrent pas les lettres majuscules de la même manière, et tout mettre entre guillemets est très fastidieux.

Choisissez la même chose que d'appeler les tables - au pluriel ou au singulier (par exemple, les utilisateurs au pluriel ou l'utilisateur au singulier). J'aime plus le singulier, mais tous les frameworks backend semblent être pluriels par défaut. Vous devez suivre le modèle et utiliser le pluriel.

Quoi d'autre à lire :

Un guide modeste des schémas de base de données