La conception d'une base de données consiste à spécifier la structure et donc les aspects logiques d'une base de données. Le but de la conception d'une base de données est de faire une représentation de certains "univers de discours" - les types de faits, les règles commerciales et autres exigences que la base de données est censée modéliser.

Le résultat de la conception de base de donnéesest un plan pour la construction d'une base de données en tant que modèle de l'Univers du discours (l '"entreprise domaine "ou domaine sur lequel des informations seront enregistrées dans la base de données).

La plupart des bases de données qui capturent et gèrent des données semi-permanentes fonctionnent sous le contrôle d'un système de gestion de base de données (SGBD). Les principaux produits de SGBD sont SQL Server de Microsoft, Oracle DBMS et DB2 d'IBM. Il y en a des dizaines d'autres. De nombreuses questions et réponses que vous trouverez sous cette balise concernent l'un de ces produits SGBD, mais certains problèmes de conception sont indépendants du SGBD.

La quantité de préparation et d'éducation dont vous aurez besoin avant de créer votre première base de données réussie varie considérablement en fonction de nombreux facteurs. Entre autres facteurs, cela dépend de l'ampleur de votre projet de base de données et de l'expérience préalable que vous apportez au projet. Les programmeurs très expérimentés sous-estiment parfois la quantité de matériel disponible pour en savoir plus sur la conception de bases de données.

Parfois, les programmeurs apprennent bien par essais et erreurs, ou en reportant l'apprentissage formel jusqu'à leur deuxième ou troisième projet. D'autres fois, les néophytes de conception de bases de données prennent des décisions de conception qui conduisent à des pièges très difficiles à inverser.

Il existe de nombreuses façons de mesurer la qualité d'une conception de base de données. Les programmeurs qui construisent leur première base de données se préoccupent souvent principalement des performances. Il ne fait aucun doute que les performances sont importantes. Une mauvaise conception peut facilement entraîner des opérations de base de données qui prennent dix à cent fois plus de temps qu'elles le devraient.

Mais ne laissez pas les problèmes de performances vous aveugler sur d'autres aspects d'une bonne conception. En particulier, l'épreuvage futur d'une base de données est extrêmement important. Ne pas le faire peut entraîner une base de données qui piège ses utilisateurs au premier niveau et empêche leurs données d'évoluer à mesure que leurs besoins évoluent.

Un autre aspect consiste à séparer les fonctionnalités cachées d'une base de données (parfois appelées conception physique) des fonctionnalités publiques visibles sur l'interface de l'application (parfois appelées conception logique). Une séparation nette de ces fonctionnalités peut entraîner une base de données qui peut être ajustée et ajustée un peu sans aucune modification du code d'application. Une mauvaise séparation de ces fonctionnalités peut entraîner une base de données qui fait un cauchemar du développement d'applications ou de l'administration de la base de données.

Une autre considération est de savoir si la base de données proposée sera intégrée dans une seule application, ou si elle sera un centre d'information qui répond aux besoins de plusieurs applications. Certaines décisions de conception seront prises très différemment dans ces deux cas.

Encore une autre considération est de savoir si l'application va exécuter toutes les fonctions de gestion des données au nom de ses clients, ou si la responsabilité de la garde de la base de données et de ses données va être confiée à un ou plusieurs DBA (administrateurs de base de données).

De plus, il est très important de voir la conception de la base de données SQL et la conception de la base de données No-SQL séparément car de nombreux concepts sont différents et doivent être pris en compte dans la phase initiale de la conception de la base de données.


Quels types de questions apparaîtront dans la balise de conception de base de données?

Vous verrez beaucoup de questions sur la conception des tables, la normalisation des données, la conception des index, l'optimisation des requêtes, les déclarations de contraintes et les clés. Un grand nombre de questions et de nombreuses réponses porteront sur des problèmes de vitesse ou de performances. Il y aura beaucoup de questions sur la sélection des clés.

La plupart des questions concernent les bases de données relationnelles, y compris les bases de données SQL communément appelées relations. Quelques questions portent sur les bases de données "vraiment relationnelles" ou sur les bases de données "non relationnelles" ou "post-relationnelles". Quelques-uns concernent des données semi-structurées ou non structurées.

Un grand nombre de questions étiquetées «conception de base de données» seront également étiquetées «modélisation de données». Il y a un énorme chevauchement entre les deux sujets.

Vous verrez beaucoup de questions sur le sujet de la composition et de la décomposition des tableaux. Le concept de normalisation des données est étroitement lié à la décomposition des tableaux. En effet, de nombreux répondants traitent la décomposition des tableaux et la normalisation des données comme s'ils étaient synonymes. Ils ne sont pas tout à fait synonymes. Presque toutes les améliorations de la normalisation des données entraînent la décomposition des tables, mais il existe de nombreuses façons de décomposer les tables qui n'ont rien à voir avec la normalisation.

La normalisation des données est un tout nouveau sujet pour de nombreux concepteurs de bases de données néophytes. Cela vaut la peine d'apprendre les rudiments de la normalisation des données, même si la base de données que vous créez est petite et simple. Il est également parfois utile de ne pas tenir compte des règles de normalisation des données, mais vous devez vraiment savoir ce que vous faites.

Vous verrez également de nombreuses questions sur le thème de la conception d'index. L'optimisation des requêtes est étroitement liée à la conception des index. De nombreuses questions sur la conception d'index ou la conception de requête ont à voir avec l'effort que le programmeur doit consacrer pour obtenir le meilleur résultat de l'optimiseur.

Trois choses méritent d'être gardées à l'esprit. Premièrement, l'optimisation est souvent une question de compromis. Parfois, organiser les choses pour une requête rapide ralentira les mises à jour des données. Parfois, la vitesse compte vraiment dans certaines opérations de base de données, mais pas dans d'autres.

Deuxièmement, vous devez vraiment faire attention à ces choses qui ralentissent les opérations de quelques secondes à quelques minutes, ou de quelques minutes à des heures, avant de vous inquiéter des améliorations de 10%.

Troisièmement, les retards dans les bases de données varient énormément à mesure que le volume de données augmente et que le nombre d'utilisateurs simultanés augmente. Des tests simples avec un seul utilisateur et des exemples de données peuvent vraiment vous induire en erreur sur la vitesse dans un environnement de production.


Quelles sont les erreurs courantes de développement de bases de données?

1. Ne pas utiliser d'indices appropriés

C'est relativement facile, mais ça arrive tout le temps. Les clés étrangères doivent avoir des index dessus. Si vous utilisez un champ dans un WHERE, vous devriez (probablement) avoir un index dessus. Ces index doivent souvent couvrir plusieurs colonnes en fonction des requêtes que vous devez exécuter.

2. Ne pas appliquer l'intégrité référentielle

Votre base de données peut varier ici, mais si votre base de données prend en charge l'intégrité référentielle - ce qui signifie que toutes les clés étrangères sont garanties pour pointer vers une entité qui existe - vous devez l'utiliser.

Il est assez fréquent de voir cet échec sur les bases de données MySQL.

Plus ici:

3. Clés manquantes ou mal choisies

Les clés sont un élément fondamental de la conception de la base de données et de l'intégrité des données. Des clés mal choisies, l'impossibilité de mettre en œuvre des clés ou des malentendus sur les clés sont des problèmes et des sujets de discussion très courants. Les mérites relatifs et l'utilisation de clés de substitution et naturelles ne sont qu'un aspect. Les clés de substitution et les clés naturelles peuvent toutes deux être des outils utiles lorsqu'elles sont appliquées correctement, mais elles ont des caractéristiques et des fonctions distinctes et une clé ne peut pas nécessairement être considérée comme un substitut ou une alternative à une autre. Voici quelques questions et réponses pertinentes:

4. Écriture de requêtes qui nécessitent que DISTINCT fonctionne

Vous le voyez souvent dans les requêtes générées par ORM. Regardez la sortie du journal de Hibernateet vous verrez toutes les requêtes commencer par:

SELECT DISTINCT ...

C'est un peu un raccourci pour vous assurer de ne pas renvoyer de lignes en double et donc d'obtenir des objets en double. Vous verrez parfois des gens faire cela aussi. Si vous le voyez trop, c'est un vrai drapeau rouge. Ce n'est pas que DISTINCT est mauvais ou n'a pas d'applications valides. C'est le cas (sur les deux points), mais ce n'est pas un substitut ou un arrêt pour écrire des requêtes correctes.

Depuis Pourquoi je déteste DISTINCT:

À mon avis, les choses commencent à mal tourner quand un développeur crée une requête substantielle, joint des tables, et soudain, il se rend compte qu'il semble qu'il obtienne des lignes en double (ou même plus) et sa réponse immédiate ... sa «solution» à ce «problème» est de jeter sur le mot-clé DISTINCT et POOF tous ses ennuis disparaissent.

5. Favoriser l'agrégation plutôt que les jointures

Une autre erreur courante est de ne pas réaliser combien d'agrégation plus coûteuse (c'est-à-dire la clause GROUP BY) peut être comparée aux jointures.

Par exemple:

De Instruction SQL - "rejoindre" vs "grouper et avoir":

Première requête:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

Temps de requête: 0,312 s

Deuxième requête:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

Temps de requête: 0,016 s

C'est vrai. La version join que j'ai proposée est vingt fois plus rapide que la version agrégée.

6. Ne simplifie pas les requêtes complexes via les vues

Tous les fournisseurs de bases de données ne prennent pas en charge les vues, mais pour ceux qui le font, ils peuvent grandement simplifier les requêtes s'ils sont utilisés judicieusement. Par exemple, considérons un modèle de partie génériquepour CRM. Il s'agit d'une technique de modélisation extrêmement puissante et flexible, mais elle peut conduire à de nombreuses jointures. Dans ce modèle, il y avait:

  • Parti: personnes et organisations;
  • Rôle du parti: ce que ces partis ont fait, par exemple l'employé et l'employeur;
  • Relation entre les rôles des parties: comment ces rôles sont liés les uns aux autres.

Exemple:

  • Ted est une personne, étant un sous-type de parti;
  • Ted a de nombreux rôles, dont l'un est employé;
  • Intel est une organisation, étant un sous-type d'une partie;
  • Intel a de nombreux rôles, dont l’employeur;
  • Intel emploie Ted, ce qui signifie qu'il existe une relation entre leurs rôles respectifs.

Il y a donc cinq tables jointes pour relier Ted à son employeur. Nous supposons que tous les employés sont des personnes (pas des organisations) et fournissons cette vue d'aide:

CREATE VIEW vw_employee AS
SELECT p.title, p.given_names, p.surname, p.date_of_birth, p2.party_name employer_name
FROM person p
JOIN party py ON py.id = p.id
JOIN party_role child ON p.id = child.party_id
JOIN party_role_relationship prr ON child.id = prr.child_id AND prr.type = 'EMPLOYMENT'
JOIN party_role parent ON parent.id = prr.parent_id = parent.id
JOIN party p2 ON parent.party_id = p2.id

Et soudain, nous avons une vue très simple des données que vous voulez, mais sur un modèle de données très flexible.

7. Entrée non désinfectante

C'est énorme. Si vous ne savez pas ce que vous faites, il est très facile de créer des sites vulnérables aux attaques. Rien ne résume mieux que l'histoire des petites tables Bobby.

Les données fournies par l'utilisateur au moyen d'URL, de données de formulaire et de cookies doivent toujours être traitées comme hostiles et nettoyées. Assurez-vous d'obtenir ce que vous attendez.

8. Ne pas utiliser de déclarations préparées

Les instructions préparées sont lorsque vous compilez une requête moins les données utilisées dans les insertions, les mises à jour et les clauses WHERE, puis que vous les fournissez plus tard. Par exemple:

SELECT * FROM users WHERE username = 'bob'

Contre

SELECT * FROM users WHERE username = ?

Ou

SELECT * FROM users WHERE username = :username

En fonction de votre plateforme.

Fondamentalement, chaque fois qu'une base de données moderne rencontre une nouvelle requête, elle doit la compiler. S'il rencontre une requête déjà vue, vous donnez à la base de données la possibilité de mettre en cache la requête compilée et le plan d'exécution. En effectuant beaucoup de requêtes, vous donnez à la base de données la possibilité de comprendre et d'optimiser en conséquence (par exemple, en épinglant la requête compilée en mémoire).

L'utilisation d'instructions préparées vous donnera également des statistiques significatives sur la fréquence d'utilisation de certaines requêtes.

Les instructions préparées vous protégeront également mieux contre les attaques SQL.

9. Pas assez normalisé

La Normalisation de la base de donnéesest le processus d'optimisation de la conception de la base de données ou de la façon dont vous organisez vos données en tableaux.

Par exemple, considérons le code où quelqu'un implose un tableau et l'insère dans un seul champ d'une base de données. Normaliser cela reviendrait à traiter chaque élément du tableau comme une ligne distincte dans une table enfant (c'est-à-dire une relation un-à-plusieurs).

Cela est apparu dans la meilleure méthode de stockage d'une liste d'ID utilisateur:

J'ai vu dans d'autres systèmes que la liste est stockée dans un tableau PHP sérialisé.

Mais le manque de normalisation se présente sous plusieurs formes.

Plus:

10. Normaliser trop

Cela peut sembler une contradiction avec le point précédent, mais la normalisation, comme beaucoup de choses, est un outil. C'est un moyen pour une fin et non une fin en soi. De nombreux développeurs oublient cela et commencent à traiter un "moyen" comme une "fin". Les tests unitairesen sont un excellent exemple.

Une dénormalisation minutieuse et réfléchie peut avoir d'énormes avantages en termes de performances, mais vous devez être très prudent lorsque vous effectuez cette opération.

Plus:

Le problème avec les conseils suivants pour "dénormaliser" est qu'il ne vous dit pas quoi faire. C'est comme essayer de se rendre à Los Angelesen s'éloignant de Chicago. Vous pourriez vous retrouver presque n'importe où. Un meilleur plan consiste à trouver une autre discipline de conception qui fonctionnera comme une alternative à la normalisation, avec des objectifs de conception différents. Une de ces alternatives est la conception du schéma en étoile. La conception de schémas en étoile est largement utilisée dans l'entreposage de donnéeset les bases de données de rapports, où la rapidité et la facilité d'interrogation l'emportent sur la simplicité de mise à jour. Il existe une autre alternative, appelée conception de flocons de neigequi ressemble un peu à un compromis entre le schéma en étoile et la conception normalisée.

11. Arcs exclusifs mal mis en œuvre

Une relation "exclusive ou" est une relation dans laquelle l'existence d'une instance de relation est censée s'exclure mutuellement avec une autre instance de relation. Parfois, cela se fait à l'aide de deux clés étrangères ou plus, dont l'une est annulable, mais il existe généralement de meilleures alternatives.

12. Ne pas faire du tout d'analyse des performances sur les requêtes

Le pragmatisme règne en maître, en particulier dans le monde des bases de données. Si vous vous en tenez aux principes au point qu'ils sont devenus un dogme, vous avez probablement commis des erreurs. Prenons l'exemple des requêtes agrégées ci-dessus. La version agrégée peut sembler "agréable", mais ses performances sont lamentables. Une comparaison des performances aurait dû mettre fin au débat (mais ce ne fut pas le cas), mais plus précisément: jaillir de telles opinions mal informées est en premier lieu ignorant, voire dangereux.

13. Dépendance excessive à l'égard de UNION ALL et en particulier des constructions UNION

Une UNION en termes SQL concatène simplement des ensembles de données congrus, ce qui signifie qu'ils ont le même type et le même nombre de colonnes. La différence entre eux est que UNION ALL est une simple concaténation et devrait être préférée dans la mesure du possible alors qu'un UNION fera implicitement un DISTINCT pour supprimer les tuples en double.

Les UNIONS, comme DISTINCT, ont leur place. Il y a des applications valides. Mais si vous vous retrouvez à en faire beaucoup, en particulier dans les sous-requêtes, vous faites probablement quelque chose de mal. Cela pourrait être un cas de mauvaise construction de requête ou d'un modèle de données mal conçu vous obligeant à faire de telles choses.

Les UNIONs, en particulier lorsqu'ils sont utilisés dans des jointures ou des sous-requêtes dépendantes, peuvent paralyser une base de données. Essayez de les éviter autant que possible.

14. Utilisation des conditions OR dans les requêtes

Cela peut sembler inoffensif. Après tout, les ET sont OK. OU devrait être OK trop à droite? Faux. Fondamentalement, une condition ET restreint l'ensemble de données, tandis qu'une condition OU le fait croître, mais pas d'une manière qui se prête à l'optimisation. En particulier lorsque les différentes conditions OR peuvent se croiser, forçant ainsi l'optimiseur à effectuer efficacement une opération DISTINCT sur le résultat.

Mauvaise:

... WHERE a = 2 OR a = 5 OR a = 11

Mieux:

... WHERE a IN (2, 5, 11)

Maintenant, votre optimiseur SQL peut transformer efficacement la première requête en seconde. Mais ce n'est peut-être pas le cas. Ne le fais pas.

15. Ne pas concevoir leur modèle de données pour se prêter à des solutions performantes

C'est un point difficile à quantifier. Il est généralement observé par son effet. Si vous vous retrouvez à écrire des requêtes compliquées pour des tâches relativement simples ou que les requêtes pour trouver des informations relativement simples ne sont pas efficaces, alors vous avez probablement un mauvais modèle de données.

À certains égards, ce point résume tous les précédents, mais il s'agit plus d'un récit édifiant que faire des choses comme l'optimisation des requêtes se fait souvent en premier, alors qu'il devrait l'être en deuxième. Avant tout, vous devez vous assurer d'avoir un bon modèle de données avant d'essayer d'optimiser les performances. Comme Donald Knuthl'a dit:

L'optimisation prématurée est la racine de tout Mal

16. Utilisation incorrecte des transactions de base de données

Toutes les modifications de données pour un processus spécifique doivent être atomiques. Autrement dit, si l'opération réussit, elle le fait pleinement. S'il échoue, les données restent inchangées. Il ne devrait pas y avoir de possibilité de modifications «à moitié effectuées».

Dans l'idéal, la manière la plus simple d'y parvenir est que la conception complète du système s'efforce de prendre en charge toutes les modifications de données via des instructions INSERT / UPDATE / DELETE uniques. Dans ce cas, aucune gestion de transaction spéciale n'est nécessaire, car votre moteur de base de données doit le faire automatiquement.

Cependant, si des processus nécessitent que plusieurs instructions soient exécutées en tant qu'unité pour conserver les données dans un état cohérent, un contrôle de transaction approprié est nécessaire.

  • Commencez une transaction avant la première instruction.
  • Validez la transaction après la dernière instruction.
  • En cas d'erreur, annulez la transaction. Et très débutant! N'oubliez pas de sauter / abandonner toutes les instructions qui suivent après l'erreur.

Portez également une attention particulière aux subtilités de la façon dont votre couche de connectivité de base de données et le moteur de base de données interagissent à cet égard.

17. Ne pas comprendre le paradigme «basé sur les ensembles»

Le langage SQL suit un paradigme spécifique adapté à des types de problèmes spécifiques. Nonobstant diverses extensions spécifiques au fournisseur, le langage a du mal à gérer les problèmes triviaux dans des langages tels que Java, C #, Delphi, etc.

Ce manque de compréhension se manifeste de plusieurs manières.

  • Imposer de manière inappropriée trop de logique procédurale ou impérative à la base de données.
  • Utilisation inappropriée ou excessive des curseurs. Surtout quand une seule requête suffit.
  • Supposant à tort que les déclencheurs se déclenchent une fois par ligne affectés dans les mises à jour à plusieurs lignes.

Déterminez une répartition claire des responsabilités et efforcez-vous d'utiliser l'outil approprié pour résoudre chaque problème.