Conception de votre bdd (méthodologie)

[gtsoul]

Voici un petit tuto pour vous apprendre à concevoir votre bdd de façon optimale. Il reprend les grands points de la méthode merise/uml. Je vais essayer de l'expliquer de manière simple, pour plus de détails, google et votre libraire sont vo meilleurs amis.

Je prendrais l'exemple d'un jeu d'élevage à consonance rpg tout bête. les persos tapent des monstres pour gagner des xp et des loots qu'ils peuvent revendre ou équiper. bref, c'est sans saveur mais simple

Conception générale :
Identifier :
_ les entités : les entités qui sont amenées à interagir entre elles
ex : un personnage, un monstre
_ les associations : description des ces interactions
ex : baston et soins

1er schéma
On prendra des carrés pour les entités et des cercles pour les associations.
(réalisé sous paint à la va-vite, donc vous pouvez faire mieux, dia est très bien)

_ Vous remarquerez que j'ai utilisé des verbes pour décrire les associations et des noms pour les entités.
_ Une entité interagit sur un autre via une association, jamais en direct.
_ Eviter les relations ternaires (3 entités reliés par le même association), cela peut exister mais le plus souvent, c'est une erreur de conception.

La cardinalité :
Il s'agit d'apposer des nombres sur les relations (traits) afin de voir quel acteur/processus possède le plus de poids.
du coté de chaque entité, nous allons placer un nombre(0..1, 1, 0..n) qui répondra à cette question :
Combien d'éléments A pourraient interagir avec B ?

ex : un personnage peut posséder autant d'objets qu'il souhaite (0..n) même aucun. De l'autre côté, un objet n'a forcement qu'un seul propriétaire (on parle ici d'occurence, le chapeau de jack n'est pas le même que celui de paul même si ils se ressemblent)
Bref, on obtient: (1) personnage possède (0..n) objets

Maintenant, nous allons réfléchir base de données. Un base de données sert par définition à stocker des informations qui ne peuvent être traitées de suite.
C'est le moment de faire le tri. Enlevons tout ce qui n'a pas besoin d'être sauvegardé au-délà d'une requête.
J'enlève le combat et le soin car ce sont des actions qui se réalisent au moment ou elles sont décidées. Donc à moins de faire un système de log, il est inutile de savoir que paul a tapé jack.


Réalisation des tables
Bon donc nous avons à peu près le schéma de nos tables. A chaque carré ou rond correspond 1 ou 2 tables. nous allons maintenant songer à créer des champs.
Sans souci d'optimisation, nous mettons sous nos entités, ce qu'il peut être utile de retenir.

Personnage :nom, experience, vie_max, vie, force, endurance
Monstre : nom, vie_max, vie, force, endurance
Objet : nom, bonus_force, bonus_endurance, duree_max, duree

Gestion des clés
Pour identifier chaque occurence d'objet dans notre table, il nous faudra des clés.
Pour identifier chaque occurence, il faut une clé primaire ou id. Un peu comme la carte vitale, vous êtes le 551654457 et pas marc dupont.
La clé primaire doit être :
_ unique et bijective (marc dupont peut avoir un homonyme)
_ invariable dans le temps (marc dupont peut se marier et prendre le nom de sa femme)

La clé étrangère est l'importation d'une clé primaire d'une autre table. [i]Si marc dupont se marie avec liz durand, le registre de mariage indiquera : 551654457 se marie avec 55885557. Comme ces clés se retrouvent aussi dans la table personnage; ce ne sont pas des doublons mais des liens vers cette table. Ainsi si l'on veut connaitre le nom du marié, on fait correspondre les infos de la table personnage avec la clé étrangère du registre de mariage.[/li]
L'utilisation de clé étrangère est surtout destiné à éliminer la redondance d'informations.

Je rajoute les clés primaires !!
Personnage :id_perso, nom, experience, vie_max, vie, force, endurance
Monstre : id_monstre, nom, vie_max, vie, force, endurance
Objet : id_objet, nom, bonus_force, bonus_endurance, prix

Correspondance schéma/tables
Selon la cardinalité des associations :
Relation 1..1 : la plus simple, à chaque objet A correspond 1 et 1 seul objet B. Il est inutile de faire deux tables, il faut fusionner.
Relation 1..[0..n]: la force est du côté du 1. On rajoute dans la deuxième table, la clé primaire de la deuxième comme clé étrangère.
Relation [0..n]..[0..n]: moults cas possibles. Le plus souvent, on crée une table intermédiaire qui contient les deux clés primaires des membres en clés étrangères afin de faire la jointure.

Ce qui donne dans notre exemple (ajout de clés secondaires) :
Personnage :id_perso, nom, experience, vie_max, vie, force, endurance
Monstre : id_monstre, nom, vie_max, vie, force, endurance
Objet : id_objet, id_perso, nom, bonus_force, bonus_endurance, duree_max, duree
Drop : id_objet, id_monstre

Nous avons ajouté un champ id_perso dans objet qui représente l'id du propriétaire de celui-ci et créé une nouvelle table "drop" qui réalise la jointure monstre/objet.

Choix du typage
Voici les différents types (généraux) :
int/smallint/bigint/bool : entiers
float : flottants (réels)
blob/text : grosses chaînes de caractères : pour les forum
varchar : courtes chaînes de caractères : nom, prenom
time/date : gèrer des dates, dates+heures(+secondes)
enum : enumérations (ex : 'patates','tomates','bananes')
Selon votre version de mysql et votre outil de gestion de bdd, vous pouvez en avoir d'autres, mais ils se refererront toujours à un type cité au dessus.
La gestion de chaines de caractères est couteuse; aussi ne les utilisez que si nécessaire; essayez de remplacer les petites par des varchar ou par des enum si les possibles sont très limitées.

Personnage :int id_perso, varchar nom, int experience, int vie_max, int vie, int force, int endurance
Monstre : int id_monstre, varchar nom, int vie_max, int vie, int force, int endurance
Objet : int id_objet, int id_perso, varchar nom, int bonus_force, int bonus_endurance, int duree_max, int duree
Drop : int id_objet, int id_monstre

Pour les clés primaires, nous utiliserons une option qui s'appelle "auto-increment", un id est auto-généré, donc forcement unique. Un bon moyen de vérifier si vous avez correctement identifié vos clés et de fixer l'autoincrément sur toutes vos clés primaires à valeur numérique.
Nous déclarerons les clés primaires comme telles (icone 'clé' sous phpmyadmin) afin d'acélerer la navigation.
La clé primaire de drop est en fait l'association des deux clés étrangères; séléctionnez les deux int et formez une clé primaire composée.

Ajoutons quelques champs pour la démo.
on a 3 persos, toto, titi, tata. toto et tata possède un anneau magique, titi une épée. il y a deux espèces de monstre blobs et fourmis, avec chacune 2 représentants. les blobs droppent des anneaux magiques et les fourmis des épées (comme dans tous les beaufs mmorpg)



vous imaginerez la table drop ... car en fait, nous ne pourrons pas la représenter

Erreurs de la base
Actuellement nous avons le schéma de notre base. Celle-ci pourrait être fonctionnelle. Mais nous ne l'avons pas encore optimisée.
Cette optimisation est obligatoire, car elle évite les doublons d'informations, donc prend moins de place et aussi évite les conflits.

La règle principale est qu'une information ne dit être présente qu'à un et seul endroit. Nous utiliserons les clés pour réaliser les jointures.

Notre base n'est pas du tout optimisée. Et vous pourrez vous en rendre compte en regardant le nombre de répétition des champs. Pourtant, tout semblait aller sur notre schéma.







Maintenant, on va voir quelles sont nos erreurs pour mieux concevoir notre base. Si vous arrivez à créer votre base au premier jet, soit vous êtes très forts, soit vous allez vous planter.

Comme pour une conception objet, il faut différencier un objet générique de son instance.
En fait, le schéma est beaucoup plus compliqué que décrit.

Commencons par ce qui est simple :
_ chaque personnage ne possède qu'une seule instance de lui-même car il est unique. Notre table personnage est correcte.

_ un monstre appartient à une espèce donnée (blob ou fourmi). Nous avons quatre monstres qui sont en fait deux objets espèces (blob ou fourmi) qui ont chacune 2 instances.
Pourquoi ne pas considérer que comme le personnage, les monstres sont uniques ?
C'est ce qu'on vient de faire et ca marche pas. Cela voudrait dire que pour chaque monstre que l'on voudrait ajouter sur la carte, il faudrait créer une nouvelle espèce. A la vitesse à laquelle ils se font descendre ce n'est pas gérable et d'ailleurs il faudrait un générateur de nom et d'images associés pour les rendre uniques.

Donc une espèce possède des caractéristiques propres (force, endurance, vie_max, nom), et chaque monstre (=instance de l'espèce) possède des attributs qui sont uniques (vie, car lorsqu'on attaque un monstre précis, il ne faut pas tuer toute son espèce).

_ Idem pour les objets, il faut différencier l'objet général (un anneau magique) de son instance (toto et titi en possèdent chacun un mais ce n'est pas le même).

_ Quant aux drops, le lien entre objet et monstre devra se faire en objet général et espèce. Autrement dit, n'importe quel blob drop des anneaux magiques et pas uniquement celui qui est en face de moi.

Reconception
Notre erreur provienait du fait que nous avions mal concu nos entités/associations. Voici un exemple de correction :

Nous allons maintenant examiner la redondance des données et la dépendance fonctionnelle.
redondance des données : une même donnée est présente à plusieurs endroits. occupe inutilement de la place et des incohérences peuvent apparaitre => à éviter
dépendance fonctionnelle : méthode pour mettre en évidence ces redondances

Il y a 3 niveaux de dépendances fonctionnelles :
_ dépendance fonctionnelle : A->B : à chaque valeur de A ne correspond qu'une et une seule valeur de B. c'est le niveau le plus bas et le plus évident.
On cherche les redondances entre 2 tables
_ dépendance fonctionnelle élémentaire : si A->B ET si B n'est pas fonctionnellement dépendant d'une sous liste de A
On cherche les redondances entre 2 tables si l'une d'entre elle possède une clé composée
_ dépendance fonctionnelle directe : A->B ET si il n'existe pas de C tel que A->C et C->B
Idem pour l'élémentaire mais on vérifie si il n'y a pas dépendance via une table tiers.

Voilà pour la théorie, à chaque dépendance correspond une forme normale, qui est un ensemble de règles à appliquer pour optimiser votre base. Le but est d'arriver à passer votre base en 3e forme normale.

1ere forme normale :
Une relation R est en première forme normale si et si seulement chacun des constituants qui ne fait pas partie de la clé est en dépendance fonctionnelle avec la clé.
explications :si votre table fonctionne, c'est que vous êtes à ce niveau au moins. cela veut dire que si vous ne pouvez pas accéder directement à un champ, vous pouvez y accéder via sa clé. Mais aussi qu'on ne peut pas accéder directement à un champ ET y accéder via sa clé (redondance!).

La solution est d'utiliser des id pour les objets/monstres/personnage comme c'est déjà fait.

2eme forme normale :
Une relation R est en deuxième forme normale si et seulement si, les constituants qui ne font pas partie de la clé sont en dépendance fonctionnelle élémentaire avec la clé.
Explications :
Même chose que précedemment, mais on porte l'attention sur l'utilisation de clés composées.
Pour rappel, une clé composée est un ensemble de champs qui ne peuvent former une clé séparement, mais qui forme une clé si ils sont assemblés. exemple : pour définir une case d'une bataille navale, on prend l'abcisse (x) et l'ordonnée et on fusionne le tout pour donner une case (J9, A3 ...)

Nous n'utilisons pas de clé composée donc c'est bon.

3eme forme normale :
Une relation R est en 3eme forme normale si et seulement si, les constituants qui ne font pas partie de la clé sont en dépendance fonctionnelle élémentaire et directe par rapport à la clé.
Explications
3FN <=> 2FN && tout attribut non-clé ne dépend pas d'un attribut non-clé.

correction de la table monstre :
Et c'est ici que ca coince
Si vous avez compris, tout ce qui n'est pas une clé doit être modifiable sans que cela ne crée un conflit avec un autre attribut non-clé.
Prenons la table monstre et modifions la force et l'endurance du premier blob, sans toucher au deuxième. Que se passe-t-il ? nous avons un blob transgénique qui sera plus fort que l'autre. Si nous restons dans le concept de monstre stéréotypé, c'est grave !!

solution :
Nous allons créer une table espèce qui regroupera toutes les informations communes à une espèce de monstre, et nous ne mettrons dans la table monstre, toutes les informations particulières à un monstre précis.
table espèce :

nouvelle table monstre :

Vous remarquerez que j'ai conservé autant d'informations,mais :
1. la création d'un nouveau monstre est plus aisée
2. toute modification sur une espèce se répercute sur l'ensemble de ses membres
3. ca prend moins de place et aucun risque d'incohérence


correction de la table objet :
De même que pour la table monstre, nous allons différencier les instances d'objet d'un objet générique. toto et tata ont le même anneau, il a le même nom et les mêmes carac. Quel besoin de répéter celles-ci autant de fois qu'il y a de porteurs ?
Si l'on suit la règle de normalisation 3FN, on obtient :
nouvelle table objet :

table inventaire : table jointure personnage+objet

dans cette solution, je différencie chaque instance d'une autre, car j'inclue la notion de durée de vie. Sans ca, on pourrait tout aussi regrouper tous les objets d'un même type et d'un même proprio
ex: inventaire : id_objet, id_perso, quantite_possedee
Nous aurions alors une clé primaire composée (id_objet+id_perso).

Finalement, nous allons faire la table drop, ce qui était auparavant impossible :
L'astuce est de mettre en relation, un type d'objet avec une espèce de monstre.

Les blobs droppent des anneaux et les fourmis des épées.





Remarques diverses :
_ Ne pas confondre optimisation d'une base avec confort superflu. Ce genre de démarche est obligatoire; car contrairement à du code, votre base évolue, les temps d'accès et l'espace de stockage aussi.
_ Oui il n'y a pas de méthode miracle toute faite. La méthode exposée est assez longue et vague. Je précise quand même que mon poly de cours traitant ce sujet fait 600 pages; résumer les points essentiels en un post tient du miracle.
_ Concretement que faire ? définir vos règles métiers, puis fournir le modèle entité-association correspondant, essayer d'appliquer les règles de normalisation. Si ca marche, c'est bon, on crée la base, sinon on cherche les erreurs et on recommence.
_ Comme toute méthode de conception, c'est long, c'est chiant, et on passe beaucoup de temps avant de faire quelque chose de bien. Mais c'est mieux que tout faire à l'arrach', même si c'est plus facile.
_ Si vous n'avez pas compris, achetez un bouquin, potassez-le et revenez ici pour essayer de mieux expliquer ...

[orditeck]

L'aide pour ce tutoriel ce trouve à cette adresse :
http://www.jeuweb.org/board/showthread.php?tid=560