Aide à la Conception d'une BDD MySQL
Bonjour,
Je travaille actuellement pour une entreprise qui doit pouvoir gérer des clients , des fiches de maintenances et des factures qui y sont associées.
J'ai déjà créer les tables de la BDD MySql :
http://imgur.com/a/iiRKi
Pour Facture , je pense que le plus simple c'est de récupérer les données dans client avec une jointure.
Mais j'ai un souci. En fait , l'entreprise a un site web où l'on peut faire une demande de maintenance en remplissant un formulaire dont les infos sont les mêmes que la table "Client". Mais il faut que l'on puisse faire une fiche maintenance au cas où une demande est faite autrement que par le site web (déplacement,mail ,téléphone ..) , qui en plus nécessite de pouvoir en créer plusieurs pour un même client.
Du coup , je ne sais pas quoi mettre en clé étrangère ou quoi et je ne vois absolument pas comment mettre en place cela
Pourriez vous m''aidez à résoudre ce souci ?
Hésitez pas à poser des questions si je n'ai pas été clair
Réponses apportées à cette discussion
Salut, je crois que c'est assez clair, mais je dois prévenir que la remise à niveau de cette structure va être vigoureuse ;)
Il est très courant de voir des bases mal faites, et malheureusement, on oublie trop souvent de considérer que si la base est bancale, l'application finira tôt ou tard par être bancale.
Je vais donc proposer un certain nombre de modifications majeures pour, à terme, simplifier le codage et permettre de s'y retrouver beaucoup plus facilement. Même un DBA qui regardera ça sera surpris et content.
Convention de nommage
On va commencer par des principes de base, la manière de nommer les tables et les colonnes.
- Chaque table comportera trois parties dans son nom : un préfixe indiquant une table normale ou une table relationnelle, son nom principal et un suffixe. Le suffixe va ensuite être repris dans les colonnes, on va voir ci-dessous;
- Chaque colonne reprendra en préfixe le suffixe de sa table d'appartenance. S'il s'agit d'une clé étrangère, elle aura alors le suffixe de la table d'origine. Ça facilite sacrément la lecture.
Renommons donc les trois tables
- client devient t_client_cli;
- maintenance devient t_maintenance_mnt;
- facture devient t_facture_fct
Les colonnes doivent également être renommées : dans la table t_client_cli, la colonne id devient alors cli_id, et ainsi de suite pour toutes les colonnes. Je vais revenir plus loin sur les clés étrangères.
Identifier les relations
D'abord, on sait qu'une maintenance est faite pour un client déterminé, mais qu'un client peut avoir plusieurs maintenances. On aura donc logiquement dans la table maintenance une clé étrangère identifiant le client. In introduit dans la table t_maintenance_mnt la colonne cli_id
Ensuite, une facture est affectée à un client donné également, donc là aussi on va introduire une clé étrangère qui se nommera également cli_id dans la table t_facture_fct
Enfin, une facture pourra concerner un ou plusieurs maintenances, donc il va falloir une colonne fct_id en clé étrangère dans la table t_maintenance_mnt.
Est-ce que c'est plus clair
À ce stade, je vais mettre en pause pour vérifier que ce que je viens de décrire est bien clair, et au besoin je pourrai préciser des détails.
Erratum : attention, je viens d'éditer, la précédente version de la réponse comportait une erreur de conception.
Merci pour toutes ces indications ! En effet cette convention de nommage m'a l'air extrêmement pratique ! Je l'adopte directement !
Par contre il me reste un point à éclaircir ! ^^'
Lors d'une création de maintenance depuis le logiciel je vais d'abord regarder si le client existe, s'il n'existe pas je le créer dans le formulaire, et s'il existe je reprends les données de la table client. Ca me semble logique , pensez vous que mon raisonnement est bon ? :)
Mais je voulais savoir , lors d'une demande de maintenance depuis le site web , j'ai juste à faire deux requêtes php différentes :
Une afin de stocker les informations de client dans la table client
Et une autre pour stocker les informations de maintenance dans la table client mais en le faisant après la création (ou non) du client , avec un Where cli_id ="x" ? :)
Principe de base
- On lit sur une ou plusieurs tables (avec des jointures);
- On écrit jamais QUE sur UNE seule table à la fois
Donc si on doit enregistrer une maintenance, on va avoir besoin de l'identifiant du client. Il est donc logique de devoir d'abord enregistrer le client s'il n'existe pas, récupérer ensuite la valeur de la clé primaire générée, et ensuite seulement enregistrer la maintenance.
Est-ce que ça répond à la question ?
Informations et suggestions supplémentaires
Quelques modifications majeures de structure
Si on regarde de quoi sont constituées les tables, on peut réaliser qu'il y a des défaut assez majeurs.
Prenons la table t_client_cli :
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| cli_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| cli_nom | varchar(255) | NO | | NULL | |
| cli_prenom | varchar(255) | NO | | NULL | |
| cli_adresse | varchar(255) | NO | | NULL | |
| cli_cp | varchar(255) | NO | | NULL | |
| cli_tel | varchar(255) | YES | | NULL | |
| cli_email | varchar(255) | YES | | NULL | |
| cli_descripanne | varchar(255) | YES | | NULL | |
+-----------------+------------------+------+-----+---------+----------------+
Je vois des problèmes futurs qui seront difficiles à corriger et qu'on peut facilement anticiper. Je suggère donc de séparer les données pour les répartir dans plusieurs tables.
On va ainsi créer cinq tables : on atomise les données en entités distinctes, à long terme, ça donne de la souplesse permettant l'évolution de la structure avec très largement moins de risques de casse.
Voici de quoi ça pourrait avoir l'air :
Table t_client_cli
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| cli_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| adr_id | int(10) unsigned | YES | MUL | NULL | |
| cli_nom | varchar(255) | NO | | NULL | |
| cli_prenom | varchar(255) | NO | | NULL | |
| cli_email | varchar(255) | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
Table t_adresse_adr
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| adr_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| adr_libelle | varchar(255) | NO | | NULL | |
| adr_complement | varchar(255) | YES | | NULL | |
| adr_cp | varchar(16) | NO | | NULL | |
| adr_commune | varchar(255) | NO | | NULL | |
+----------------+------------------+------+-----+---------+----------------+
Table t_telephone_tel
+------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+----------------+
| tel_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| tel_numero | varchar(32) | NO | | NULL | |
| tel_type | enum('fixe','mobile') | NO | | mobile | |
+------------+-----------------------+------+-----+---------+----------------+
Table r_client_has_telephone_cht
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| cli_id | int(10) unsigned | NO | PRI | NULL | |
| tel_id | int(10) unsigned | NO | PRI | NULL | |
+--------+------------------+------+-----+---------+-------+
Table t_pannes_pan
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| pan_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| cli_id | int(10) unsigned | NO | UNI | NULL | |
| pan_description | varchar(255) | NO | | NULL | |
| pan_date | date | NO | | NULL | |
+-----------------+------------------+------+-----+---------+----------------+
Ça peut avoir l'air impressionnant, il n'en est rien : ça s'explique très bien. Par exemple, un client peut avoir plusieurs numéros de téléphones, un fixe et un mobile. On peut en enregistrer autant qu'on veut.
Les pannes, c'est pareil, on va pas recréer une fiche client à chaque panne, on crée une table à part et on la relie au client.
Pour voir les relations, voici le code de création
-- Table: t_client_cli
CREATE TABLE `t_client_cli` (
`cli_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`adr_id` int(10) unsigned DEFAULT NULL,
`cli_nom` varchar(255) NOT NULL,
`cli_prenom` varchar(255) NOT NULL,
`cli_email` varchar(255) DEFAULT NULL,
PRIMARY KEY (`cli_id`),
KEY `fk_adresse_01` (`adr_id`),
CONSTRAINT `fk_adresse_01` FOREIGN KEY (`adr_id`) REFERENCES `t_adresse_adr` (`adr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table: t_adresse_adr
CREATE TABLE `t_adresse_adr` (
`adr_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`adr_libelle` varchar(255) NOT NULL,
`adr_complement` varchar(255) DEFAULT NULL,
`adr_cp` varchar(16) NOT NULL,
`adr_commune` varchar(255) NOT NULL,
PRIMARY KEY (`adr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table: t_telephone_tel
CREATE TABLE `t_telephone_tel` (
`tel_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tel_numero` varchar(32) NOT NULL,
`tel_type` enum('fixe','mobile') NOT NULL DEFAULT 'mobile',
PRIMARY KEY (`tel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table: r_client_has_telephone_cht
CREATE TABLE `r_client_has_telephone_cht` (
`cli_id` int(10) unsigned NOT NULL,
`tel_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`cli_id`,`tel_id`),
KEY `fk_client_01` (`cli_id`),
KEY `fk_telephone_01` (`tel_id`),
CONSTRAINT `fk_client_01` FOREIGN KEY (`cli_id`) REFERENCES `t_client_cli` (`cli_id`),
CONSTRAINT `fk_telephone_01` FOREIGN KEY (`tel_id`) REFERENCES `t_telephone_tel` (`tel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: t_pannes_pan
CREATE TABLE `t_pannes_pan` (
`pan_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cli_id` int(10) unsigned NOT NULL,
`pan_description` varchar(255) NOT NULL,
`pan_date` date NOT NULL,
PRIMARY KEY (`pan_id`),
UNIQUE KEY `fk_client_02` (`cli_id`),
CONSTRAINT `fk_client_02` FOREIGN KEY (`cli_id`) REFERENCES `t_client_cli` (`cli_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Ne jamais hésiter
Il faut prendre un peu de temps pour définir le modèle de données, ce n'est jamais une perte de temps.
Wow !
Merci pour ces explications extrêmement claires et précises !
C'est très gentil de m'aider à ce point !
Je vais recommander ce forum a mes contacts !
Je vais appliquer tout cela :)
Encore merci ! Je laisse le sujet ouvert si j'ai d'autres questions :)
Pas de quoi.
À noter : la manière de nommer les clés étrangères dans la table, là aussi, ça répond à une convention de nommage, avec un préfixe indiquant le type de clé (fk => clé étrangère, ix => index, un => unique, le nom de la table de référence, et un numéro autorisant du coup plusieurs clé avec le même nom de base dans d'autres tables.
Suggestion
Il faudrait appliquer le même traitement aux deux autres tables : maintenance et facture
Merci pour l'appréciation.
C'est normal avec toute l'aide que vous m'avez fourni !
Du coup pour afficher le téléphone d'un client j'aimerais mettre en priorité le tél portable.
Ca pourrait marcher si je fais un truc en php :
$reponse = $bdd->query(Select tel_numero from t_telephone_tel Where type_tel ="portable" And cli_id = "$id" )
if($donnees = $reponse -> fetch())
{
// alors j'affiche le tel portable
}
else
{
// alors je cherche le tel fixe et je l'affiche
}
Ca vous semble logique ? :)
Par exemple oui, ça pourrait être une solution.
Ce que je fais moi-même pour constituer une fiche client par exemple, c'est que je vais avoir plusieurs requêtes successives pour construire un tableau associatif. Dans une fonction, je crée les requêtes et je complète un tableau qui ressemble à ceci :
$client = array(
'nom' => '',
'prenom' => '',
'tels' => array(
0 => array(
'numero' => '',
'type' => ''
)
),
adr => array(
'numero' => '',
'libelle' => '',
'complement' => '',
'commune' => '',
'cp' => ''
)
);
Là, on voit tout de suite qu'on pourrait très bien indiquer plusieurs téléphones et identifier chaque élément de son adresse.
Travailler avec des tableaux peut faciliter pas mal la tâche.
D'accord , merci.
J'ai fais comme cela pour ma création de client , et c'est vrai que les array sont vraiment très utiles ^^
$req = $bdd->prepare('INSERT INTO client(cli_nom,cli_prenom,cli_adresse,cli_cp,cli_ville,cli_tel,cli_email) VALUES(:cli_nom,:cli_prenom,:cli_adresse,:cli_cp,:cli_ville,:cli_tel",:cli_email")');
$req->execute(array(
'cli_nom' => $nom,
'cli_prenom' => $prenom,
'cli_adresse' => $adresse,
'cli_cp' => $cp,
'cli_ville' => $ville,
'cli_tel' => $tel,
'cli_email' => $email,
));
EDIT : J'ai sorti descripanne , pour le mettre directement dans la table maintenance car je trouve ça plus pratique ^^
Du coup la table t_panne_pan se retrouve directement à l'intérieur de la table Maintenance , ca vous semble bon également ? :)
Il faudrait, pour que je puisse donner un avis valable, que je voie le modèle de données. Un truc comme la capture d'écran du post de départ serait pratique.
Ce que j'essaye de montrer, c'est comment identifier les données pour trier les entités des propriétés de ces entités. Dans l'exemple que j,ai donné plus tôt, à partir de la table client, j'ai isolé 4 entités et des relations entre ces entités, chacune de ces entités ayant ses propriétés. Ainsi, le client a un nom, un prénom etc..., un téléphone a un type et u numéro, une panne a une date et une description... Ensuite on relie les entités entre elles : une panne est rattachée à un client, c'est ce qui va nous amener à intégrer dans la pane une clé étrangère pour identifier le client. Et ainsi de suite.
Ensuite pour les insertions dans la base, on va devoir suivre un ordre logique : si par exemple on enregistre une nouvelle panne pour un nouveau client, comme j'ai besoin de l'identifiant du client pour pouvoir enregistrer la panne, je dois impérativement enregistrer le client AVANT la panne.
Pour la fusion panne/maintenance, je ne sais pas, il faudrait voir quelles sont les propriété et si il peut y avoir des relations un-à-un ou un-à-plusieurs et dans quel sens.
Ok, alors il faut inverser le sens de la relation maintenance/facture.
On peut imaginer en effet qu'une maintenance fera l'objet d'une et une seule facture, alors qu'une facture pourra concerner une à n maintenances.
Mais il y a un autre soucis plus général sur la table des factures : l'entité est monobloc et restreint pas mal les possibilités. Comment est-ce qu'on pourrait avoir plusieurs lignes sur la facture ?
L'autre défaut, c'est que cette table contient des valeurs calculées (les totaux).
Donc ce que je ferais, ce serais au moins couper ça en trois :
- la facture elle-même, avec son numéro de référence, la date, bref, l'entité facture seule;
- Les lignes facturées qui elles seront reliées chacune à une maintenance donnée (ou autre chose plus tard?);
- les paiements;
Je laisse cette idée histoire de te laisser mûrir ça soigneusement, n'hésite pas à poser des questions complémentaires, mais à terme, tu vas éviter de perdre beaucoup de temps quand on te demandera des modifications.
Toujours anticiper les évolutions
Merci beaucoup ! :)
Je vais travailler tout ça , je vous tiens au courant.
Bon Weekend à vous !
