Du modèle ER au schéma de base de données

Ce n'est pas un homme de paille : c'est la forme qu'a un tableur, et c'est par un tableur que commencent la plupart des schémas. Il vaut la peine d'être précis sur ce qui cloche, parce que "elle n'est pas normalisée" n'explique rien :

Ce qui suit est de la modélisation de schéma de base de données au sens ordinaire du terme : décider quelles sont les tables, quelle colonne identifie chaque ligne, et où un fait a le droit d'habiter. C'est l'étape entre un diagramme ER et un DDL qu'une base acceptera, et elle mérite d'être faite sur papier, parce que chacune de ces décisions coûte cher à défaire une fois qu'il y a des données dans la table.

1. Vous ne pouvez pas stocker un client qui n'a rien commandé. Le client n'existe que sous forme de colonnes sur une commande.
2. Corriger un e-mail oblige à mettre à jour toutes les lignes où ce client apparaît - et en oublier une laisse la base avec deux réponses différentes.
3. Supprimer la dernière commande supprime le client. De l'information disparaît comme effet de bord de quelque chose qui n'a rien à voir.
4. product_names contient une liste. Désormais toute requête qui veut un seul produit analyse une chaîne, et aucun index ne peut aider.
5. total peut contredire les lignes dont il est censé être la somme.

Ces cinq-là ont des noms - anomalies d'insertion, de mise à jour et de suppression, un groupe répétitif et une valeur dérivée - et la normalisation n'est rien d'autre que la procédure qui les supprime.

Tout le reste en dépend, donc cela vient avant les tables. Une clé primaire doit être unique, jamais nulle et ne jamais changer. C'est la troisième condition qui élimine la plupart des candidats.

Il existe six formes normales et vous en avez besoin de trois. Chacune est une question unique posée à une table, et la réponse "non" vous dit quelle colonne déplacer où.

Faites passer la table plate par ces trois questions. La 1FN éclate product_names et quantities dans une table order_lines, une ligne par produit. La 2FN remarque que le nom et le prix d'un produit ne dépendent que du produit, d'où l'apparition de products. La 3FN remarque que le nom et l'e-mail du client dépendent du client et non de la commande, d'où l'apparition de customers. Quatre tables, c'est-à-dire exactement le diagramme d'ouverture - et aucune étape n'a demandé de jugement, seulement la question.

La colonne total de la table plate pose un problème différent des quatre autres : elle n'est pas mal placée, elle est redondante. Elle se calcule à partir des lignes de commande, elle peut donc les contredire, et finira par le faire.

Trois réponses légitimes, par ordre de préférence : la calculer dans la requête ; la calculer dans une vue ; la stocker et laisser la base l'entretenir - colonne générée, vue matérialisée ou déclencheur. Ce qui n'est pas légitime, c'est de la stocker en comptant sur le code applicatif pour penser à la mettre à jour : c'est la version qui produit des factures qui ne tombent pas juste.

Un modèle ER sait exprimer un surtype avec des sous-types ; une base relationnelle n'a pas cette construction, donc le modèle physique doit choisir l'une de trois dispositions. Les trois sont largement utilisées et le choix est un vrai arbitrage.

Règle grossière : peu de sous-types aux colonnes largement communes plaident pour la table unique ; beaucoup de sous-types aux colonnes divergentes et aux contraintes réelles plaident pour une table par sous-type ; et des sous-types jamais interrogés ensemble plaident pour des tables séparées. Le diagramme de classes du même domaine aura généralement choisi son héritage sans affronter rien de tout cela, et c'est pourquoi les deux modèles ont le droit de diverger ici.

Un modèle ER logique se transpose en DDL presque mécaniquement, ce qui est la récompense de l'avoir dessiné correctement :

Deux choses que le diagramme ne dit pas et que le DDL doit trancher : ce qui se passe à la suppression (CASCADE pour une relation identifiante, RESTRICT pour presque tout le reste), et quelles colonnes reçoivent des index au-delà des clés. Les deux sont des décisions de comportement et de charge, pas de modèle, et les deux méritent d'être écrites à côté du schéma plutôt que découvertes plus tard dans un journal de requêtes lentes.

1. Normaliser au-delà de l'utile. La 3FN est la destination d'un schéma transactionnel. Éclater une table parce qu'une colonne pourrait se répéter un jour n'apporte rien et coûte une jointure pour toujours.
2. Pas de clés étrangères, "pour la performance". Le coût est une lecture d'index à l'écriture ; le bénéfice est que les lignes orphelines deviennent impossibles. Presque jamais le bon arbitrage.
3. Des colonnes nullables à la place d'une table manquante. Six colonnes renseignées pour un seul type de ligne, c'est un sous-type qui réclame sa table.
4. status en texte libre. Contraignez-le - contrainte de vérification, énumération ou table de référence - sinon il contiendra shipped, Shipped et SHIPPED avant un an.
5. Des horodatages sans fuseau horaire. Justes exactement une fois, dans un seul bureau, jusqu'au premier déménagement de serveur.
6. Le diagramme abandonné après la première migration. Un diagramme de schéma en désaccord avec la base est pire que pas de diagramme du tout, parce que les gens lui font confiance.

Si les symboles de cardinalité des diagrammes ci-dessus demandent un décodage, ils sont traités dans la notation pied-de-corbeau ; la forme du modèle lui-même est dans les diagrammes ER.