De projet en projet, nous sommes malheureusement confrontés au manque de normes uniformes pour la conception de bases de données, malgré le fait que SQL existe depuis plusieurs décennies. Je soupçonne que la raison est en partie due au fait que la plupart des développeurs ne comprennent pas l'architecture de la base de données. Au cours des années de mon travail dans l'embauche de développeurs, je n'ai rencontré que plusieurs fois qui pouvaient correctement normaliser la base de données. Honnêtement, cela peut être une tâche difficile, mais bon nombre des développeurs que j'ai interviewés, même parlant couramment SQL, n'avaient pas de compétences en conception de bases de données.

Cet article ne traite pas de la normalisation des bases de données. Si vous voulez apprendre cela, ici je vous ai brièvement expliqué les bases.

Si vous disposez d'une base de données fonctionnelle, vous devez répondre à la question: «quelles normes peut-on appliquer pour faciliter l' utilisation de cette base de données?». Si ces normes sont largement utilisées, il vous sera alors facile d'utiliser la base de données, car vous n'avez pas à étudier et à mémoriser de nouveaux ensembles de normes chaque fois que vous commencez à travailler avec une nouvelle base de données.

Nommer ou souligner CamelCase?

Je rencontre constamment des bases de données dans lesquelles les tables sont nommées dans le style de CustomerOrders ou customer_orders . Quel est le meilleur à utiliser? Vous souhaitez peut-être appliquer une norme déjà établie, mais si vous créez une nouvelle base de données, je recommande d'utiliser des traits de soulignement pour augmenter l'accessibilité. L'expression «sous-valeur» a une signification différente par rapport à «sous-évaluation», mais avec un trait de soulignement, le premier sera toujours under_value et le second sera undervalue . Et lorsque nous utilisons CamelCase, nous obtenons UnderValue et UnderValue , qui sont identiques en termes de SQL insensible à la casse. De plus, si vous avez des problèmes de vision et que vous expérimentez constamment des casques et des épingles pour souligner les mots, le soulignement est beaucoup plus facile à lire.

Enfin, CamelCase est difficile à lire pour ceux pour qui l'anglais n'est pas natif.
Pour résumer, ce n'est pas une recommandation stricte, mais une préférence personnelle.

Pluriel ou singulier dans les noms de table?

Les experts en théorie des bases de données se demandent depuis longtemps si les tables doivent être singulières (client) ou plurielles (clients). Permettez-moi de couper ce nœud gordien sans aller plus loin dans la théorie, simplement avec l'aide du pragmatisme: les noms de table pluriels sont moins susceptibles d'entrer en conflit avec des mots clés réservés.

Avez-vous des utilisateurs - des users ? SQL a le mot-clé user . Avez-vous besoin d'une table de contraintes? constraint est un mot réservé. Le mot audit
réservé, mais avez-vous besoin d'une table d' audit ? Utilisez simplement la forme plurielle des noms, et la plupart des mots réservés ne vous dérangeront pas en SQL. Même PostgreSQL, qui possède un excellent analyseur SQL, est tombé sur la table des user .

Utilisez simplement le pluriel et la probabilité de conflit sera beaucoup plus faible.

Ne nommez pas la colonne avec l'ID comme "id"

J'ai moi-même péché au fil des ans. Une fois que j'ai travaillé avec un client à Paris, le DBA s'est plaint de moi quand j'ai donné le nom id la colonne id . Je pensais qu'il n'était qu'un pédant. En effet, le nom de colonne customers.id est unique, et customers.customer_id est une répétition d'informations.

Et plus tard, j'ai dû déboguer ceci:

 SELECT thread.* FROM email thread JOIN email selected ON selected.id = thread.id JOIN character recipient ON recipient.id = thread.recipient_id JOIN station_area sa ON sa.id = recipient.id JOIN station st ON st.id = sa.id JOIN star origin ON origin.id = thread.id JOIN star destination ON destination.id = st.id LEFT JOIN route ON ( route.from_id = origin.id AND route.to_id = destination.id ) WHERE selected.id = ? AND ( thread.sender_id = ? OR ( thread.recipient_id = ? AND ( origin.id = destination.id OR ( route.distance IS NOT NULL AND now() >= thread.datesent + ( route.distance * interval '30 seconds' ) )))) ORDER BY datesent ASC, thread.parent_id ASC 

Vous remarquez le problème? Si SQL utilisait des noms d'ID complets, tels que email_id , star_id ou station_id , les bugs se email_id star_id station_id ce code , et pas plus tard, lorsque j'essaierai de comprendre ce que j'ai fait de mal.

Rendez-vous service et utilisez les noms complets pour l'ID. Merci plus tard.

Noms des colonnes

Donnez aux colonnes des noms aussi descriptifs que possible. Disons que la colonne de temperature n'a rien à voir avec cela:

 SELECT name, 'too cold' FROM areas WHERE temperature < 32; 

J'habite en France, et pour nous une température de 32 degrés sera «trop froide». Par conséquent, il est préférable de nommer la colonne fahrenheit .

 SELECT name, 'too cold' FROM areas WHERE fahrenheit < 32; 

Maintenant, tout est complètement clair.

Si vous avez des restrictions de clé étrangère, donnez le même nom aux colonnes des deux côtés de la restriction autant que possible. Voici un SQL parfaitement pensé et raisonnable:

 SELECT * FROM some_table s JOIN some_other_table o ON o.owner = s.person_id; 

Ce code est vraiment bien. Mais lorsque vous regardez la définition de la table, vous verrez que some_other_table.owner a une contrainte de clé étrangère avec companies.company_id . Donc, essentiellement, ce SQL est faux. Il fallait utiliser des noms identiques:

 SELECT * FROM some_table s JOIN some_other_table o ON o.company_id = s.person_id; 

Maintenant, il est immédiatement clair que nous avons un bogue, il vous suffit de vérifier une ligne de code et de ne pas vous référer à la définition de la table.

Cependant, je tiens à noter que cela ne peut pas toujours être fait. Si vous avez une table avec un entrepôt source et une destination, vous souhaiterez peut-être comparer source_id avec destination_id avec warehouse_id . Dans ce cas, il vaut mieux donner les noms source_warehouse_id et destination_warehouse_id .

Notez également que dans l'exemple ci-dessus, le owner décrira mieux le but que company_id . Si cela vous semble déroutant, vous pouvez nommer la colonne owning_company_id . Ensuite, le nom vous dira le but de la colonne.

Évitez les valeurs NULL

Ce conseil est connu de nombreux développeurs de bases de données expérimentés, mais, malheureusement, ils n'en parlent pas assez souvent: sans raison valable, n'autorisez pas les valeurs NULL dans la base de données.
C'est un sujet important mais plutôt compliqué. Tout d'abord, nous discutons de la théorie, puis de son effet sur l'architecture de la base de données, et en conclusion, nous analyserons un exemple pratique de problèmes graves causés par la présence de valeurs NULL.

Types de bases de données

La base de données peut contenir des données de différents types : INTEGER, JSON, DATETIME, etc. Le type est associé à la colonne et toute valeur ajoutée doit correspondre à ce type.

Mais qu'est-ce qu'un type? Il s'agit d'un nom, d'un ensemble de valeurs valides et d'un ensemble d'opérations valides. Ils nous aident à éviter les comportements indésirables. Par exemple, que se passe-t-il en Java si vous essayez de comparer une chaîne et un nombre?

 CustomerAccount.java:5: error: bad operand types for binary operator '>' if ( current > threshold ) { ^ first type: String second type: int 

Même si vous ne remarquez pas que current > threshold compare les types incomparables, le compilateur le détectera pour vous.

Ironiquement, les bases de données qui stockent vos données - et sont votre dernière ligne de défense contre la corruption de données - fonctionnent terriblement avec les types! Tout simplement dégoûtant. Par exemple, si votre table customers a une clé de substitution, vous pouvez le faire:

 SELECT name, birthdate FROM customers WHERE customer_id > weight; 

Bien sûr, cela n'a aucun sens et en réalité, vous obtiendrez une erreur de compilation. De nombreux langages de programmation facilitent la détection de telles erreurs de type, mais avec les bases de données, l'inverse est vrai.

Il s'agit d'une situation normale dans le monde des bases de données, probablement parce que la première norme SQL a été publiée en 1992 . Les ordinateurs étaient lents pendant ces années, et tout ce qui compliquait la mise en œuvre ralentissait sans aucun doute les bases de données.

Et puis les valeurs NULL apparaissent sur la scène. Le standard SQL les a correctement implémentés en un seul endroit, dans les IS NOT NULL IS NULL et IS NOT NULL . Étant donné que la valeur NULL est inconnue par définition, vous ne pouvez pas avoir d'opérateurs conçus pour elle. Et donc il y a IS NULL et IS NOT NULL au lieu de = NULL et != NULL . Et toute comparaison de valeurs NULL conduit à l'apparition d'une nouvelle valeur NULL.

Si cela vous semble étrange, ce sera beaucoup plus facile si vous écrivez «inconnu» au lieu de NULL:

La comparaison de valeurs inconnues NULL donne des valeurs inconnues NULL .

Ouais, maintenant je vois!

Que signifie une valeur nulle?

Armés des miettes de la théorie, nous considérons ses conséquences pratiques.

Vous devez verser une prime de 500 $ à tous les employés dont le salaire pour l'année s'élève à plus de 50 000 $. Vous écrivez ce code:

 SELECT employee_number, name FROM employees WHERE salary > 50000; 

Et vous venez d'être licencié, parce que votre patron a gagné plus de 50000 $, mais son salaire n'est pas dans la base de données (dans la colonne des salaires.NULL), et l'opérateur de comparaison ne peut pas comparer NULL avec 50000.

Pourquoi y a-t-il NULL dans cette colonne? Peut-être que le salaire est confidentiel. Peut-être que les informations ne sont pas encore arrivées. C'est peut-être un consultant qui n'est pas payé. Peut-être qu'il a un salaire horaire, pas un salaire. Il existe de nombreuses raisons pour lesquelles des données peuvent être manquantes.

La présence ou l'absence d'informations dans la colonne suggère que cela dépend d' autre chose, et non de la dénormalisation de la clé primaire et de la base de données. Ainsi, les colonnes dans lesquelles il peut y avoir des valeurs NULL sont de bons candidats pour créer de nouvelles tables. Dans ce cas, vous pouvez avoir tables de , _ _ , un __ , etc. Vous êtes toujours renvoyé pour avoir combiné aveuglément les salaires et votre patron n'en ayant pas. Mais votre base commence alors à vous fournir suffisamment d'informations pour suggérer que le problème est plus qu'un problème de salaire.

Et oui, c'était un exemple stupide, mais c'était la dernière goutte.

Les valeurs NULL conduisent à des situations logiquement impossibles

Il peut vous sembler que je suis pédant par rapport aux valeurs NULL. Cependant, regardons un autre exemple beaucoup plus proche de la réalité.

Il y a quelques années, j'ai travaillé à Londres pour un registraire de domaine et j'ai essayé de comprendre pourquoi une requête SQL de 80 lignes renvoie des données incorrectes. Dans cette situation, des informations auraient certainement dû être rendues, mais cela ne s'est pas produit. J'ai honte d'admettre, mais il m'a fallu un jour pour comprendre que la raison en était une telle combinaison de conditions:

• J'ai utilisé OUTER JOIN.
  
• Ils pourraient facilement générer des valeurs NULL.
  
• Les valeurs NULL peuvent entraîner une réponse incorrecte de SQL.
  

De nombreux développeurs ne connaissent pas ce dernier aspect, alors examinons un exemple du livre Database In Depth . Un schéma simple de deux tableaux:

suppliers

parts


Il est difficile de trouver un exemple plus simple.

Ce code renvoie p1 .

 SELECT part_id FROM parts; 

Que fera ce code?

 SELECT part_id FROM parts WHERE city = city; 

Il ne renverra rien, car vous ne pouvez pas comparer une valeur NULL, même avec un autre NULL ou le même NULL. Cela a l'air bizarre parce que la ville sur chaque ligne devrait être la même, même si nous ne la connaissons pas, non? Alors qu'est-ce qui retournera le code suivant? Essayez de comprendre cela avant de continuer à lire.

 SELECT s.supplier_id, p.part_id FROM suppliers s, parts p WHERE p.city <> s.city OR p.city <> 'Paris'; 

Nous n'avons pas reçu de chaîne en réponse, car nous ne pouvons pas comparer la ville NULL ( p.city ), et donc aucune des branches de la WHERE ne conduira à true .

Cependant, nous savons que la ville inconnue est Paris ou non Paris. Si c'est Paris, alors la première condition sera vraie ( <> 'London' ). Si ce n'est pas Paris, alors la deuxième condition sera vraie ( <> 'Paris' ). Ainsi, la WHERE doit être true , mais elle ne l'est pas et, par conséquent, SQL génère un résultat logiquement impossible.

C'est un bug que j'ai rencontré à Londres. Chaque fois que vous écrivez du SQL qui peut générer ou contenir des valeurs NULL, vous courez le risque d'obtenir un faux résultat. Cela se produit rarement, mais il est très difficile à identifier.

Résumé

• Utilisez __ au lieu de CamelCase .
  
• Les noms de table doivent être au pluriel.
  
• Donnez des noms étendus aux champs avec des identifiants ( item_id au lieu de id ).
  
• Évitez les noms de colonne ambigus.
  
• Si possible, nommez les colonnes avec des clés étrangères de la même manière que les colonnes auxquelles elles se réfèrent.
  
• Dans la mesure du possible, ajoutez NOT NULL à toutes les définitions de colonne.
  
• Dans la mesure du possible, évitez d'écrire du SQL qui peut générer des valeurs NULL.
  

Bien qu'il ne soit pas parfait, ce guide de conception de base de données vous facilitera la vie.