Comment concevoir une structure de base de données ?

Une base de données bien structurée :

• libère de l'espace disque en éliminant les données redondantes ;
• préserve l'exactitude et l'intégrité des données ;
• permet d'accéder efficacement aux données.

Pour concevoir une base de données efficace et utile, vous devez suivre le bon processus, qui comprend les phases suivantes :

1. Analyse des besoins, c'est-à-dire l'identification de l'objet de votre base de données
2. Organisation des données en tables
3. Spécification des clés primaires et analyse des relations
4. Normalisation des tables

Regardons chaque étape d'un peu plus près. Notez que ce guide est basé sur le modèle SQL de base de données relationnelle d'Edgar Codd (et non pas sur les modèles de données hiérarchiques, de réseau ou d'objets). Pour en savoir plus sur les modèles de bases de données, lisez notre guide ici.

La compréhension de l'objet de votre base de données guidera vos choix tout au long du processus de conception. Assurez-vous de réfléchir à votre base de données sous tous les angles. Par exemple, si vous décidiez de créer une base de données pour une bibliothèque municipale, il vous faudrait tenir compte des possibilités d'accès aux données pour la clientèle comme pour les bibliothécaires.

Voici quelques moyens de recueillir des informations avant de créer la base de données :

• Interroger les personnes qui vont l'utiliser
• Analyser des formulaires d'entreprise, tels que des factures, des feuilles de présence, des enquêtes
• Passer en revue tout système de données existant (en incluant tous les fichiers physiques et numériques)

Commencez par rassembler toutes les données existantes qui seront intégrées à la base de données. Ensuite, faites une liste des types de données que vous souhaitez conserver et des entités – ou des personnes, objets, lieux et événements – que ces données décrivent, comme suit :

Clients

• Nom
• Adresse
• Ville, État, code postal
• Adresse e-mail

Produits

• Nom
• Montant
• Quantité en stock
• Quantité commandée

Commandes

• Numéro de commande
• Représentant commercial
• Date
• Produit(s)
• QUANTITÉ
• Montant
• Total

Plus tard, ces informations feront partie du dictionnaire des données, qui détermine les tables et les champs présents au sein de la base de données. Assurez-vous d'avoir décomposé les informations jusqu'aux fragments les plus petits possible. Par exemple, séparez l'adresse postale de la mention du pays pour que vous puissiez par la suite filtrer les personnes en fonction de leur pays de résidence. Évitez également de placer le même élément d'information dans plus d'une table, pour ne pas complexifier inutilement la structure de la base de données.

Une fois que vous savez quels types de données comprendra votre base de données, d'où proviendront les données et comment elles seront utilisées, vous êtes prêt à commencer à concevoir votre base de données à proprement parler.

L'étape suivante consiste à créer une représentation visuelle de votre base de données. Pour ce faire, vous devez comprendre exactement comment les bases de données relationnelles sont structurées.

Dans une base de données, les données apparentées sont regroupées dans des tables, chacune étant composée de lignes (également appelées tuples) et de colonnes, comme une feuille de calcul.

Pour convertir vos listes de données en tables, commencez par créer une table pour chaque type d'entité, comme les produits, les ventes, les clients et les commandes. Voici un exemple :

Chaque ligne d'une table est appelée enregistrement. Les enregistrements contiennent des données sur un objet ou une personne, par exemple un client donné. En revanche, les colonnes (également connues sous le nom de champs ou d'attributs) contiennent un seul type d'information qui apparaît dans chaque enregistrement, par exemple les adresses de tous les clients figurant dans la table.

Prénom	Nom	Âge	Code postal
Roger	Williams	43	34760
Jerrica	Jorgensen	32	97453
Samantha	Hopkins	56	64829

Pour faire en sorte que les données restent cohérentes d'un enregistrement à l'autre, assignez le bon type de donnée à chaque colonne. Les types de données les plus courants sont :

• CHAR : texte d'une longueur spécifique
• VARCHAR : texte de longueur variable
• TEXT : grandes quantités de texte
• INT : nombre entier positif ou négatif
• FLOAT, DOUBLE : types de variables permettant aussi stocker des nombres à virgule flottante
• BLOB : données binaires

Certains systèmes de gestion de base de données proposent également le type de données AutoNumber, qui génère automatiquement un numéro unique pour chaque ligne.

Pour créer une vue d'ensemble de la base de données, appelée diagramme entité-association, n'incluez pas les tables. En effet, chaque table devient une boîte dans le diagramme. Le titre de chaque boîte doit indiquer ce que les données de cette table décrivent, tandis que les attributs sont listés en dessous, comme illustré ci-après :

Enfin, vous devez décider quel(s) attribut(s) seront utilisés comme clé primaire pour chaque table, s'il y en a une. Une clé primaire est un identifiant unique pour une entité donnée, ce qui signifie que vous pourriez par exemple identifier un client en ne disposant que de cette information.

Les attributs choisis comme clés primaires doivent être uniques, immuables et toujours présents (jamais NULL ou vides). C'est pour cette raison que les numéros de commande et les noms d'utilisateurs font de bonnes clés primaires, contrairement aux numéros de téléphone ou aux adresses. Vous pouvez aussi associer plusieurs champs dans une clé primaire (ce que l'on appelle une clé composite).

Lorsque viendra le moment de créer la véritable base de données, vous intégrerez à la fois la structure de données logiques et la structure de données physiques dans le langage de définition des données pris en charge par votre système de gestion de base de données. À ce stade, vous devrez également estimer la taille de la base de données pour vous assurer de disposer du niveau de performance et de l'espace de stockage nécessaires.

Vos données étant maintenant converties en tables, vous êtes prêt à analyser les relations entre ces tables. La cardinalité fait référence à la quantité d'éléments qui interagissent entre deux tables connexes. L'identification de la cardinalité permet de vous assurer que vous avez réparti les données dans des tables de la façon la plus efficace possible.

Chaque entité peut avoir une relation avec toutes les autres, mais ces relations appartiennent généralement à l'une des trois catégories suivantes :

Relations un-à-un

Lorsqu'il n'existe qu'une seule instance de l'entité A pour chaque instance de l'entité B, on dit qu'elles ont une relation biunivoque ou un-à-un (souvent écrite 1:1). Vous pouvez représenter ce type de relation dans un diagramme entité-association par une ligne se terminant par un trait à chaque extrémité :

Sauf si vous avez une bonne raison de ne pas le faire, une relation 1:1 indique généralement qu'il est préférable de combiner les données des deux tables en une seule table.

Cependant, la création de tables disposant d'une relation 1:1 peut se justifier dans certaines circonstances. Si vous disposez d'un champ facultatif, comme « description » qui est vide dans la plupart des enregistrements, vous pouvez déplacer toutes les descriptions dans leur propre table, ce qui supprime l'espace vide et améliore les performances de la base de données.

Pour faire en sorte que les données correspondent bien, il vous faudra alors inclure au moins une colonne identique dans chaque table, très probablement la clé primaire.

Relations un-à-plusieurs

Ces relations se produisent lorsqu'un enregistrement d'une table est associé à plusieurs enregistrements d'une autre table. Par exemple, un client donné peut avoir passé plusieurs commandes, ou un usager peut avoir emprunté de nombreux livres simultanément à la bibliothèque. Les relations un-à-plusieurs (1:M) sont représentées par ce que l'on appelle une notation en « patte de corbeau », comme dans l'exemple ci-dessous :

Pour instaurer une relation 1:M lorsque vous mettez en place une base de données, il vous suffit d'ajouter la clé primaire du côté « un » de la relation en tant qu'attribut dans l'autre table. Lorsqu'une clé primaire est listée dans une autre table de cette façon, on l'appelle clé étrangère. La table du côté « un » de la relation est considérée comme un parent de la table enfant de l'autre côté.

Relations plusieurs-à-plusieurs

Lorsque plusieurs entités d'une table peuvent être associées à plusieurs entités d'une autre table, on dit qu'elles ont une relation plusieurs-à-plusieurs (M:N). Cette situation pourrait se produire dans le contexte d'une base associée à un système scolaire, car un étudiant peut assister à plusieurs cours et un cours peut compter plusieurs étudiants.

Dans un diagramme entité-association, ces relations sont représentées à l'aide des lignes suivantes :

Malheureusement, il n'est pas directement possible de mettre en place ce genre de relation dans une base de données. Au lieu de cela, vous devez la diviser en deux relations un-à-plusieurs.

Pour ce faire, créez une entité entre ces deux tables. Si la relation M:N existe entre ventes et produits, vous pouvez par exemple appeler cette nouvelle entité « produits_vendus », car elle montrera le contenu de chaque vente. Les tables ventes et produits auront toutes deux une relation 1:M avec produits_vendus. Ce type d'entité intermédiaire est appelée table de lien, entité associative ou table de jonction selon les modèles.

Chaque enregistrement de la table d'association correspondrait à deux des entités des tables voisines (et pourrait potentiellement inclure des informations supplémentaires). Par exemple, une table d'association entre des élèves et des cours pourrait ressembler à ceci :

Obligatoire ou non ?

Une autre façon d'analyser les relations consiste à déterminer quel côté de la relation doit exister pour que l'autre existe. Le côté facultatif peut être marqué d'un cercle sur la ligne où se trouverait le tiret. Par exemple, un pays doit exister pour être représenté à l'ONU, mais l'inverse n'est pas vrai :

Deux entités peuvent dépendre l'une de l'autre (l'une ne pourrait exister sans l'autre).

Relations récursives

Parfois, une table pointe vers elle-même. Ce serait le cas pour une table contenant des employés et disposant d'un attribut « responsable » faisant référence à une autre personne dans la même table. Il s'agit alors d'une relation récursive.

Relations redondantes

Une relation redondante est une relation qui est exprimée plus d'une fois. En règle générale, vous pouvez supprimer l'une des relations sans perdre aucune information importante. Par exemple, si une entité « étudiants » a une relation directe avec une autre appelée « enseignants », mais a aussi une relation avec les enseignants de façon indirecte par le biais des « cours », supprimez la relation entre les « étudiants » et les « enseignants ». Il est préférable de supprimer cette relation, car la seule façon dont les étudiants sont « attribués » aux enseignants est par le biais des cours.

Une fois que vous disposez d'un design préliminaire pour votre base de données, vous pouvez appliquer les règles de normalisation pour vous assurer que les tables sont structurées correctement. Ces règles sont en quelque sorte les normes appliquées dans le domaine.

Cela dit, toutes les bases de données ne se prêtent pas à la normalisation. En général, les bases de données OLTP (traitement transactionnel en ligne), dans lesquelles les utilisateurs travaillent sur la création, la lecture, la mise à jour et la suppression d'enregistrements, doivent être normalisées.

Les bases de données OLAP (traitement analytique en ligne), qui favorisent l'analyse et les rapports, peuvent être plus efficaces avec un certain degré de dénormalisation, étant donné que l'accent est mis sur la vitesse de calcul. Elles comprennent des applications d'aide à la décision dans lesquelles les données doivent être analysées rapidement, mais pas modifiées.

Chaque forme, ou niveau de normalisation, comprend les règles associées aux formes inférieures.

Première forme normale

La première forme normale (en abrégé 1FN ou 1NF en anglais) précise que chaque cellule de la table ne peut avoir qu'une seule valeur, et jamais une liste de valeurs. Une table comme celle qui suit n'est donc pas conforme :

IDproduit	Couleur	Montant
1	marron, jaune	15 $
2	rouge, vert	13 $
3	bleu, orange	11 $

Vous pourriez être tenté de contourner ce problème en divisant les données en colonnes supplémentaires, mais cette solution est elle aussi contraire aux règles : une table comportant des groupes d'attributs répétés ou proches les uns des autres n'est pas conforme à la première forme normale. La table ci-dessous, par exemple, n'est pas conforme :

Répartissez plutôt les données dans plusieurs tables ou enregistrements jusqu'à ce que chaque cellule ne contienne qu'une valeur et qu'il n'y ait plus de colonnes superflues. On dit alors que les données sont atomiques, c'est-à-dire qu'elles ont été décomposées jusqu'à la plus petite taille utile possible. Pour la table ci-dessus, vous pourriez créer une table supplémentaire intitulée « Détails des ventes » qui ferait correspondre chaque produit avec les ventes. « Ventes » aurait alors une relation 1:M avec « Détails des ventes ».

Deuxième forme normale

La deuxième forme normale (2FN) exige que chacun des attributs dépende entièrement de la clé primaire. Cela signifie que chaque attribut doit dépendre directement de la clé primaire, et non indirectement par l'intermédiaire d'un autre attribut.

Par exemple, un attribut « âge » qui dépend de « date de naissance », qui dépend à son tour de l'« identifiant étudiant » est considéré comme ayant une dépendance fonctionnelle partielle. Une table contenant ces attributs ne serait pas conforme à la deuxième forme normale.

En outre, une table ayant une clé primaire composée de plusieurs champs ne respecte pas la deuxième forme normale si un ou plusieurs des autres champs ne dépendent pas entièrement de la clé.

Ainsi, une table avec ces champs ne respecterait pas la seconde forme normale, car l'attribut « nom produit » dépend de l'ID produit, mais pas du numéro de commande.

• Numéro de commande (clé primaire)

• Identifiant du produit (clé primaire)

• Nom du produit

 Troisième forme normale

La troisième forme normale (3NF) ajoute à ces règles la nécessité que chaque colonne non-clé soit indépendante de toutes les autres colonnes. Si la modification d'une valeur dans une colonne non-clé change une autre valeur, cette table ne correspond pas à la troisième forme normale.

Cela vous empêche de stocker des données dérivées dans la table, comme la colonne « taxe » ci-dessous, qui dépend directement du montant total de la commande :

Commande	Montant	Taxe
14325	40,99 $	2,05 $
14326	13.73 $	0,69 $
14327	24,15 $	1,21 $

D'autres formes de normalisation ont été proposées, notamment la forme normale Boyce-Codd, les formes normales 4 à 6, et la forme normale domaine-clé, mais les trois premières sont les plus courantes.

Ces formes expliquent les meilleures pratiques à appliquer de manière générale, mais le degré de normalisation dépend du contexte de la base de données.

Certains utilisateurs veulent pouvoir accéder à plusieurs dimensions d'un certain type de données, particulièrement dans le cas des bases de données OLAP. Par exemple, ils peuvent vouloir connaître les ventes par client, par région ou par mois. Dans ce cas, le mieux est de créer une table centrale à laquelle les tables client, région et mois peuvent se référer, comme ceci :

Vous devez aussi configurer votre base de données pour qu'elle valide les données selon les règles appropriées. De nombreux systèmes de gestion de base de données, comme Microsoft Access, appliquent certaines de ces règles automatiquement.

La règle d'intégrité des entités stipule que la clé primaire ne peut jamais être NULL. Si la clé est composée de plusieurs colonnes, aucune d'elles ne peut être NULL. Sinon, elle ne pourrait pas identifier de manière unique chaque enregistrement.

La règle de l'intégrité référentielle exige que chaque clé étrangère listée dans une table corresponde à une clé primaire de la table à laquelle elle se réfère. Si la clé primaire change ou est supprimée, ces modifications devront être répercutées partout où cette clé est référencée dans la base de données.

Les règles d'intégrité de la logique métier permettent de garantir que les données correspondent à certains paramètres logiques. Par exemple, une heure de rendez-vous doit se situer pendant les horaires de bureau.

Un index est une copie triée d'une ou plusieurs colonnes, dont les valeurs ont été classées en ordre croissant ou décroissant. L'ajout d'un index permet aux utilisateurs de trouver des enregistrements plus rapidement. Au lieu de répéter le tri pour chaque requête, le système peut ainsi accéder aux enregistrements dans l'ordre spécifié par l'index.

Bien que les index accélèrent la récupération des données, ils peuvent aussi ralentir l'insertion, la mise à jour et la suppression, car l'index doit être recréé à chaque fois qu'un enregistrement est modifié.

Une vue est tout simplement une requête enregistrée sur les données. Elle peut relier utilement des données provenant de plusieurs tables ou encore montrer une partie d'une table.

Une fois la configuration de base terminée, vous pouvez affiner la base de données avec des propriétés étendues, comme du texte d'instruction, des masques de saisie et des règles de formatage applicables à un schéma , une vue ou une colonne. Ces règles étant stockées dans la base de données elle-même, la présentation des données sera cohérente dans les multiples programmes qui accèdent aux données.

Le langage de modélisation unifié (UML) est un autre moyen visuel d'exprimer des systèmes complexes créés dans un langage orienté objet. Plusieurs des concepts mentionnés dans ce guide sont connus dans UML sous des noms différents. Par exemple, une entité est appelée classe dans le langage UML.

De nos jours, le langage UML n'est plus aussi utilisé qu'auparavant. Son utilisation est aujourd'hui plus théorique et il sert souvent dans les communications entre les concepteurs de logiciels et leurs clients.

Bon nombre des choix de design que vous ferez dépendront du système de gestion de base de données que vous utilisez. Les systèmes les plus courants sont :

• Oracle DB

• MySQL

• Microsoft SQL Server

• PostgreSQL

• IBM DB2

Lorsque vous le pouvez, basez notamment votre choix sur le coût, les systèmes d'exploitation pris en charge et les fonctions proposées.