OK, plus simple que je le pensais.Envoyé par fsmrel
Oui, c'est bien conforme avec mon système de gestion de forfaits.
Je le fais en anglais moi:
Merci.
Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter
OK, plus simple que je le pensais.
Oui, c'est bien conforme avec mon système de gestion de forfaits.
Je le fais en anglais moi:
Merci.
Au sujet des promos
Bonsoir,
On est donc d’accord pour les forfaits.
En ce qui concerne les promos, la gestion de l’attribut « used » de la table CLIENT_PROMO pose un problème. L’idée est bonne, mais à la réflexion, faire basculer disons de l’état 0 (promo non encore utilisée) à 1 (promo utilisée) ne doit pas être fait manuellement, mais de façon automatique, à l’instant même de l’utilisation de la promo par le client, à l’occasion d’une réservation ou d’une mise à disposition.
Autrement dit, il est de loin préférable de mettre en œuvre un trigger, vérifiant que, lorsque Fernand veut utiliser la promo P1 pour une réservation, il ne s’en n’est pas déjà servi pour une mise à disposition (même chose pour l’autre cas de figure).
La bascule de l’attribut « used » est une charge supplémentaire, inutile, car concrètement cet attribut n’apporte rien : on évitera de le mettre en oeuvre.
Voici un exemple de triggers (opérations d’INSERT), avec jeu d’essai et tentative d’infraction :
USE temp ; DROP TABLE IF EXISTS CLIENT_PROMO_TRANSFER_BOOKING ; DROP TABLE IF EXISTS CLIENT_PROMO_DISPOSAL_BOOKING ; DROP TABLE IF EXISTS TRANSFER_BOOKING ; DROP TABLE IF EXISTS DISPOSAL_BOOKING ; DROP TABLE IF EXISTS CLIENT_PROMO ; DROP TABLE IF EXISTS PROMO ; DROP TABLE IF EXISTS CLIENT ; DROP TABLE IF EXISTS DRIVER ; CREATE TABLE DRIVER ( driver_id INT NOT NULL , driver_first_name VARCHAR(32) NOT NULL , CONSTRAINT DRIVER_PK PRIMARY KEY (driver_id) ) ; CREATE TABLE CLIENT ( client_id INT NOT NULL , client_first_name VARCHAR(32) NOT NULL , CONSTRAINT CLIENT_PK PRIMARY KEY (client_id) ) ; CREATE TABLE PROMO ( promo_id INT NOT NULL , promo_code VARCHAR(10) NOT NULL , promo_date DATE NOT NULL , CONSTRAINT PROMO_PK PRIMARY KEY (promo_id) , CONSTRAINT PROMO_CODE_AK UNIQUE (promo_code) ) ; CREATE TABLE CLIENT_PROMO ( client_id INT NOT NULL , promo_id INT NOT NULL , CONSTRAINT CLIENT_PROMO_PK PRIMARY KEY (client_id, promo_id) , CONSTRAINT CLIENT_PROMO_CLIENT_FK FOREIGN KEY (client_id) REFERENCES CLIENT (client_id) ON DELETE CASCADE , CONSTRAINT CLIENT_PROMO_PROMO_FK FOREIGN KEY (promo_id) REFERENCES PROMO (promo_id) ) ; CREATE TABLE TRANSFER_BOOKING ( client_id INT NOT NULL , id_transfer_booking INT NOT NULL AUTO_INCREMENT , driver_id INT NOT NULL , col_date DATE NOT NULL , CONSTRAINT TRANSFER_BOOKING_PK PRIMARY KEY (id_transfer_booking) , CONSTRAINT TRANSFER_BOOKING_SK UNIQUE (client_id, id_transfer_booking) , CONSTRAINT TRANSFER_BOOKING_CLIENT_FK FOREIGN KEY (client_id) REFERENCES CLIENT (client_id) , CONSTRAINT TRANSFER_BOOKING_DRIVER_FK FOREIGN KEY (driver_id) REFERENCES DRIVER (driver_id) ) ; CREATE TABLE DISPOSAL_BOOKING ( client_id INT NOT NULL , id_disposal_booking INT NOT NULL AUTO_INCREMENT , driver_id INT NOT NULL , col_date DATE NOT NULL , CONSTRAINT DISPOSAL_BOOKING_PK PRIMARY KEY (id_disposal_booking) , CONSTRAINT DISPOSAL_BOOKING_SK UNIQUE (client_id, id_disposal_booking) , CONSTRAINT DISPOSAL_BOOKING_CLIENT_FK FOREIGN KEY (client_id) REFERENCES CLIENT (client_id) , CONSTRAINT DISPOSAL_BOOKING_DRIVER_FK FOREIGN KEY (driver_id) REFERENCES DRIVER (driver_id) ) ; CREATE TABLE CLIENT_PROMO_TRANSFER_BOOKING ( client_id INT NOT NULL , promo_id INT NOT NULL , id_transfer_booking INT NOT NULL , CONSTRAINT CLIENT_PROMO_TRANSFER_BOOKING_PK PRIMARY KEY (client_id, promo_id) , CONSTRAINT CLIENT_PROMO_TRANSFER_BOOKING_CLI_PRO_FK FOREIGN KEY (client_id, promo_id) REFERENCES CLIENT_PROMO (client_id, promo_id) , CONSTRAINT CLIENT_PROMO_TRANSFER_BOOKING_TRANS_FK FOREIGN KEY (client_id, id_transfer_booking) REFERENCES TRANSFER_BOOKING (client_id, id_transfer_booking) ) ; CREATE TABLE CLIENT_PROMO_DISPOSAL_BOOKING ( client_id INT NOT NULL , promo_id INT NOT NULL , id_disposal_booking INT NOT NULL , CONSTRAINT CLIENT_PROMO_DISPOSAL_BOOKING_PK PRIMARY KEY (client_id, promo_id) , CONSTRAINT CLIENT_PROMO_DISPOSAL_BOOKING_CLI_PRO_FK FOREIGN KEY (client_id, promo_id) REFERENCES CLIENT_PROMO (client_id, promo_id) , CONSTRAINT CLIENT_PROMO_DISPOSAL_BOOKING_DISPO_FK FOREIGN KEY (client_id, id_disposal_booking) REFERENCES DISPOSAL_BOOKING (client_id, id_disposal_booking) ) ; COMMIT ; DELIMITER GO CREATE TRIGGER CLIENT_PROMO_TRANSFER_BOOKING_INSERT AFTER INSERT ON CLIENT_PROMO_TRANSFER_BOOKING FOR EACH ROW BEGIN IF new.client_id IN (SELECT client_id FROM CLIENT_PROMO_DISPOSAL_BOOKING WHERE client_id = new.client_id AND promo_id = new.promo_id) THEN SET @erreur = CONCAT('client_id = ', new.client_id, ' ; promo_id = ', new.promo_id, ' : promo déjà utilisée pour une mise à disposition du client !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; END GO CREATE TRIGGER CLIENT_PROMO_DISPOSAL_BOOKING_INSERT AFTER INSERT ON CLIENT_PROMO_DISPOSAL_BOOKING FOR EACH ROW BEGIN IF new.client_id IN (SELECT client_id FROM CLIENT_PROMO_TRANSFER_BOOKING WHERE client_id = new.client_id AND promo_id = new.promo_id) THEN SET @erreur = CONCAT('client_id = ', new.client_id, ' ; promo_id = ', new.promo_id, ' : promo déjà utilisée pour réservation du client !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; END GO DELIMITER ; INSERT INTO DRIVER (driver_id, driver_first_name) VALUES (1, 'Fernand'), (2, 'Raoul'), (3, 'Paul'), (4, 'Antoine'), (5, 'Pascal') ; INSERT INTO CLIENT (client_id, client_first_name) VALUES (1, 'Tryphon'), (2, 'Hégésippe'), (3, 'Maxime'), (4, 'Aristide') ; INSERT INTO PROMO (promo_id, promo_code, promo_date) VALUES (1, 'promo 01', '2015_12_22'), (2, 'promo 02', '2016_01_14'), (3, 'promo 03', '2016_01_21'), (4, 'promo 04', '2016_01_24') ; INSERT INTO CLIENT_PROMO (client_id, promo_id) VALUES (1, 1), (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 4) ; INSERT INTO TRANSFER_BOOKING (client_id, driver_id, col_date) VALUES (1, 1, '2016-02-01'), (1, 1, '2016-02-02'), (2, 3, '2016-02-01') ; INSERT INTO DISPOSAL_BOOKING (client_id, driver_id, col_date) VALUES (1, 4, '2016-02-01'), (1, 5, '2016-02-02') , (2, 1, '2016-02-06') , (3, 4, '2016-02-01'), (4, 5, '2016-02-02') ; INSERT INTO CLIENT_PROMO_TRANSFER_BOOKING (client_id, promo_id, id_transfer_booking) VALUES (1, 1, 1), (1, 2, 1), (1, 3, 2) , (2, 1, 3) ; INSERT INTO CLIENT_PROMO_DISPOSAL_BOOKING (client_id, promo_id, id_disposal_booking) VALUES (1, 1, 1), (1, 3, 1) , (2, 1, 3) ; INSERT INTO CLIENT_PROMO_DISPOSAL_BOOKING (client_id, promo_id, id_disposal_booking) VALUES (1, 4, 2) ; INSERT INTO CLIENT_PROMO_TRANSFER_BOOKING (client_id, promo_id, id_transfer_booking) VALUES (1, 4, 1) ; SELECT * FROM CLIENT_PROMO ; SELECT * FROM CLIENT_PROMO_TRANSFER_BOOKING ; SELECT * FROM CLIENT_PROMO_DISPOSAL_BOOKING ;
Je vous laisse le soin de la mise en œuvre des triggers liés aux opérations d’UPDATE.
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
Bonjour fsmrel,
Je comprends pour le trigger que c'est mieux que ca soit automatique. Ce TRIGGER doit-il être ajouté à chaque fois qu'on fait des INSERT, UPDATE ou DELETE sur les codes promo ou suffit-il de l'ajouter uniquement dans le code SQL avant l'importation des tables dans la base de donnée pour qu'il se déclenche automatiquement à chaque moment où fait appel aux codes promos?
Plus haut dans la discussion je vous ai parlé aussi de rajouter un système de payment dans un future proche pour nos clients, mais je pense qu'il doit être intége dans la base de donnée avant le développement.
Le client pourra réserver gratuitement sur le site pendant deux ou trois mois. Passé cette période il devra choisir entre payer une commission puis payer le chauffeur en main propre lors du transfert OU de payer la totalité du transfert en ligne, c'est à dire "prix du chauffeur" + "commission de réservation".
[TRANSFERT_BOOKING]------ est associé à 1,1 -------[BOOKING_PAYMENT]------chaque paiement sont associés à 0,1-------[TRANSFERT_BOOKING]
car ils peuvent aussi être associés à un [DISPOSAL_BOOKING], donc pour chaque paiement il peut y avoir soit au minimum 0 lien vers BOOKING_PAYMENT ou soit au minimum 0 lien vers TRANSFERT_BOOKING.
[COMMISSION_PAYMENT]------ est associé à 0,N -------[BOOKING_PAYMENT]------chaque paiement sont associés à 0,1 -------[COMMISSION_PAYMENT]
(cardinalité 0 minimum car dans les deux premiers mois il n'y aura pas de commission sur le site)
Vous en pensez quoi de mon raisonnement?
EDIT:
J'ai oublié de préciser que :
- on ajoutera la commission dans "commission_payment_rate" dans COMMISSION_PAYMENT : on mettra la valeure 0 pendant les trois premiers mois où ne prendra pas de commission.
- dans "total_booking_online" dans BOOKING_PAYMENT on ajoute "1" si le paiement se fait dans la totalité en ligne (commission + prix du chauffeur) et "0" si le paiement se fait directement au chauffeur avec la commission en ligne. (la commission se calcule ainsi "prix du chauffeur" + "taux de commission")
- dans "commission_price" dans BOOKING_PAYMENT on aurait la valeur en euro du taux de commission. Par exemple si la course est à 50€ et la commission à 5% on ajoutera 2,5€ dans "commission_price".
Selon mon exemple ci-dessus, quand nous appliquerons la commission sur chaque réservation:
- Si le client choisie de payer au chauffeur en main propre il paiera 2,5€ en ligne (par CB ou PayPal) et 50€ au chauffeur.
- Si le client choisie de payer la totalité en ligne, il paiera 52,5€ en ligne (par CB ou PayPal).
À noter que le code promo s'appliquera sur le prix du chauffeur uniquement et que la commission sera calculé après application du code promo sur le prix du chauffeur.
Après ceci c'est fini et je ne vous embête plus !
Merci pour votre aide!
Je vous souhaite une agréable journée.
Bonsoir,
Les triggers doivent exister avant qu’on ne commence à mettre à jour les tables. Vous exécutez d’abord les CREATE TABLE, puis les CREATE TRIGGER dans la foulée. Autrement dit, on n’a pas à recréer les trigger avant les INSERT, UPDATE, DELETE : une fois créé, un trigger fait partie du système et ne disparaîtra que le jour où vous exécuterez une instruction DROP TRIGGER le concernant.
Je ne suis pas sûr de bien comprendre. Faut-il interpréter les associations ainsi ? :
Une réservation fait référence à au moins et au plus un paiement ;
Une mise à disposition fait référence à au moins et au plus un paiement ;
Un paiement est référencé soit par une réservation, soit par une mise à disposition.
Vu les attributs qui en composent l’en-tête, la table BOOKING_PAYMENT est manifestement dédiées aux commissions. Ce que je comprends :
Si la réservation représente 50 euros pour le chauffeur (attribut Price de la table TRANSFERT_BOOKING et réduction sur promo), et si le client est chez vous depuis moins de 3 mois, il paiera seulement 50 euros. Même raisonnement si au lieu d’une réservation, il s’agit d’une mise à disposition.
Si le client est chez vous depuis au moins 3 mois, il réglera en plus le montant d’une commission selon un taux, lequel dans votre exemple est de 5%.
L’attribut commision_price (table BOOKING_PAYMENT) est une redondance, puisque la valeur qu’il prend est un résultat de calcul, il ne devrait donc pas exister.
L’attribut total_booking_online est aussi une redondance, puisque là encore on sait en déterminer la valeur : il ne devrait donc pas exister.
Par ailleurs, si ce que je comprends du total à régler par le client est avéré, pourquoi s’encombrer de la table BOOKING_PAYMENT et ne pas directement associer la table TRANSFERT_BOOKING et la table COMMISSION_PAYMENT ? (et bien sûr directement associer la table DISPOSAL_BOOKING et la table COMMISSION_PAYMENT). L’attribut correspondant au choix par le client du mode de paiement (en ligne ou au chauffeur) serait alors à rapatrier dans les tables TRANSFERT_BOOKING et DISPOSAL_BOOKING.
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
Bonjour,
Oui, c'est ce que je voulais dire.
Je comprends donc qu'il faut laisser les résultats de calcul hors de la BDD.
J'ai fait comme vous voyez sur le schéma ci-dessous. J'ai rapatrié le mode de paiement (en ligne ou au chauffeur) dans les deux tables avec l'attribut "payment_online". Si le paiement se fait en ligne il sera égal à 1, sinon 0.
Commissions
Bonsoir,
D’accord.
Attention au digramme MySQL Workbench. Vous avez modélisé ceci :
[TRANSFER_BOOKING]-||-----------||-[COMMISION_PAYMENT]-||-----------||-[DISPOSAL_BOOKING]
Vu les cardinalités, il y a bijection, d’une part entre TRANSFER_BOOKING et COMMISION_PAYMENT, et d’autre part entre DISPOSAL_BOOKING et COMMISION_PAYMENT : par transitivité, il y a donc bijection entre TRANSFER_BOOKING et DISPOSAL_BOOKING, ce qui revient à dire que de ces deux tables on peut n’en faire qu’une !
Pour le moment, je vois les cardinalités ainsi :
[TRANSFER_BOOKING]->0----------||-[COMMISION_PAYMENT]-||----------0<-[DISPOSAL_BOOKING]
C'est-à-dire :
Une réservation fait référence à au moins et au plus un taux de commission ;
Un taux de commission peut être référencé par 0 à plusieurs réservations ;
Une mise à disposition fait référence à au moins et au plus un taux de commission ;
Un taux de commission peut être référencé par 0 à plusieurs mises à disposition.
S’agit-il bien des règles de gestion des données à appliquer ?
Si oui, le diagramme devient :
Où vous remarquerez que les tables TRANSFER_BOOKING et DISPOSAL_BOOKING sont chacune dotées d’une clé étrangère {commission_payment_id} référençant la clé primaire {commission_payment_id} de la table COMMISION_PAYMENT.
De votre côté, vous avez fait l’inverse, en dotant la table COMMISION_PAYMENT d’une clé étrangère {id_transfer_booking} référençant la clé primaire {id_transfer_booking} de la table TRANSFER_BOOKING, et en dotant en plus la table COMMISION_PAYMENT d’une clé étrangère {id_disposal_booking} référençant la clé primaire {id_disposal_booking} de la table DISPOSAL_BOOKING...
Quand la modélisation sera stabilisée, Il faudra qu’on fasse une revue serrée de votre script de création des tables...
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
Ce sont bien les règles à appliquer oui.
J'ai donc modifié le moyen de paiement:
La modélisation est terminé et je veux bien une revue serrée de la création des tables, car si les tables ne sont pas créées en ordre "parent-enfant" il risque d'y avoir des problèmes... Où es-ce que je peux poster le script SQL?
Script SQL (CREATE TABLE)
Bonsoir,
MySQL Workbench sait ordonner les CREATE TABLE en fonction des relations parent-enfant. On peut déjà essayer ceci :
1. Vous générez le script en suivant la manip que je décris dans le chapitre 8 de mon article.
2. A l’occasion de votre prochain message, vous transmettrez le fichier « .sql » produit par MySQL Workbench. Inutile de nettoyer le fichier, je m’en arrangerai.
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
Bonjour,
Le script a bien était généré à l'aide de votre manip.
Veuillez trouver le script ci-joint:
choosedriver.sql
Le script
Bonsoir Nike,
Je mets le nez dans le script. Soyez patient, je suis bombardé de partout...
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
Les places
Bonsoir Nike,
Quelle est votre version de MySQL ? (Pour la part j’utilise la 5.7).
Je vous transmets ci-dessous la partie « PLACES », avec jeu d’essai.
Pour éviter de répéter la référence à InnoDB pour chaque CREATE TABLE, je code au départ :
SET default_storage_engine=InnoDB ;
Je donne un nom à chaque contrainte (clé primaire, unique, étrangère). En cas de problème avec une clé, cela rend moins pénible l’identification de celle-ci.
Par exemple, pour la table place :
« PRIMARY KEY (place_id) » devient « CONSTRAINT place_pk PRIMARY KEY (place_id) »
Pour le nom de la contrainte, je concatène le nom de la table et la constante "_pk".
On supprime les index qui font double emploi (je les ai mis en commentaires dans le script) :
Par exemple, l’index « type_place_id_UNIQUE » qui ne sert à rien, car le SGBD utilisera toujours l’index primaire.
Idem pour les index qui ne sont normalement pas utilisés par MySQL. Exemple :
table place : k_place_type_place1_idx (type_place_id ASC)
Table type_place, colonne type_place_name : je vire la clause « GENERATED ALWAYS AS () VIRTUAL », à moins qu’elle ne vous soit utile. Qu’en est-il ?
Table country : à quoi correspond la colonne country_code ? Pourriez-vous donner des exemples de valeurs ? Est-ce une clé alternative ?
Tables region et departement : comme ces deux tables sont dotées des attributs latitude et longitude, j’ai fait migrer ces deux attributs dans la table region_departement.
Table region : à quoi correspond la colonne region_code ? Pourriez-vous donner des exemples de valeurs ? Est-ce une clé alternative ?
Table departement : je suppose que la table departement ne vaut que pour la France, auquel cas l’attribut dept_code fait l’objet d’une clé alternative (cf. contrainte departement_ak).
Les CREATE TABLES et CREATE TRIGGER (pour s’assurer qu’une place soit bien d’une seule catégorie) :
USE nike7414 ; SET default_storage_engine=InnoDB ; DROP TABLE IF EXISTS city ; DROP TABLE IF EXISTS departement ; DROP TABLE IF EXISTS region ; DROP TABLE IF EXISTS region_departement ; DROP TABLE IF EXISTS country ; DROP TABLE IF EXISTS place ; DROP TABLE IF EXISTS type_place ; -- ----------------------------------------------------- -- Table type_place -- ----------------------------------------------------- CREATE TABLE type_place ( type_place_id INT NOT NULL AUTO_INCREMENT, type_place_name VARCHAR(45) NOT NULL, -- GENERATED ALWAYS AS () VIRTUAL, CONSTRAINT type_place_pk PRIMARY KEY (type_place_id) -- ---------- , UNIQUE INDEX type_place_id_UNIQUE (type_place_id ASC) -- inutile, fait double emploi ) ; insert into type_place (type_place_name) VALUES ('country') ; insert into type_place (type_place_name) VALUES ('region') ; insert into type_place (type_place_name) VALUES ('departement') ; insert into type_place (type_place_name) VALUES ('city') ; -- ----------------------------------------------------- -- Table place -- ----------------------------------------------------- CREATE TABLE place ( place_id INT NOT NULL AUTO_INCREMENT, place_name VARCHAR(45) NOT NULL, type_place_id INT NOT NULL, CONSTRAINT place_pk PRIMARY KEY (place_id), -- UNIQUE INDEX place_id_UNIQUE (place_id ASC), -- à virer CONSTRAINT place_type_place_fk FOREIGN KEY (type_place_id) REFERENCES type_place (type_place_id) ON DELETE NO ACTION ON UPDATE NO ACTION, INDEX place_type_place_idx (type_place_id ASC) ) ; -- ----------------------------------------------------- -- Table country -- ----------------------------------------------------- CREATE TABLE country ( country_id INT NOT NULL, country_code varchar(3) NOT NULL, -- Quelles valeurs ? Clé alternative ? CONSTRAINT country_pk PRIMARY KEY (country_id), -- INDEX country_place_idx (country_id ASC), -- double emploi CONSTRAINT country_place_fk FOREIGN KEY (country_id) REFERENCES place (place_id) ON DELETE CASCADE ON UPDATE NO ACTION , INDEX country_code_idx (country_code ASC) ) ; -- ----------------------------------------------------- -- Table region_departement -- ----------------------------------------------------- CREATE TABLE region_departement ( region_dept_id INT NOT NULL, longitude DECIMAL(12,9) NOT NULL default 0, latitude DECIMAL(12,9) NOT NULL default 0, CONSTRAINT region_departement_pk PRIMARY KEY (region_dept_id), CONSTRAINT region_departement_place_fk FOREIGN KEY (region_dept_id) REFERENCES place (place_id) ON DELETE CASCADE ON UPDATE NO ACTION -- ---------------- , INDEX region_departement_place_idx (region_dept_id ASC) ) ; -- ----------------------------------------------------- -- Table region -- ----------------------------------------------------- CREATE TABLE region ( region_id INT NOT NULL, region_code varchar(8) NOT NULL, country_id INT NOT NULL, CONSTRAINT region_PK PRIMARY KEY (region_id), -- ---------- INDEX fk_region_region_departement1_idx (region_id ASC), CONSTRAINT region_region_departement_fk FOREIGN KEY (region_id) REFERENCES region_departement (region_dept_id) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT region_country_fk FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE NO ACTION ON UPDATE NO ACTION, INDEX region_country_idx (country_id ASC) ) ; -- ----------------------------------------------------- -- Table departement -- ----------------------------------------------------- CREATE TABLE departement ( dept_id INT NOT NULL, dept_code INT NOT NULL, region_id INT NOT NULL, CONSTRAINT departement_pk PRIMARY KEY (dept_id), CONSTRAINT departement_ak UNIQUE (dept_code), CONSTRAINT departement_region_departement_fk FOREIGN KEY (dept_id) REFERENCES region_departement (region_dept_id) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT departement_region_fk FOREIGN KEY (region_id) REFERENCES region (region_id) ON DELETE NO ACTION ON UPDATE NO ACTION, INDEX departement_region_idx (region_id ASC) ) ; -- ----------------------------------------------------- -- Table city -- ----------------------------------------------------- CREATE TABLE city ( city_id INT NOT NULL, longitude DECIMAL(12,9) NOT NULL, latitude DECIMAL(12,9) NOT NULL, postal_code VARCHAR(8) NOT NULL, region_dept_id INT NOT NULL, CONSTRAINT city_pk PRIMARY KEY (city_id), CONSTRAINT city_place_fk FOREIGN KEY (city_id) REFERENCES place (place_id) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT city_region_departement_fk FOREIGN KEY (region_dept_id) REFERENCES region_departement (region_dept_id) ON DELETE NO ACTION ON UPDATE NO ACTION, INDEX city_region_departement_idx (region_dept_id ASC) ) ; COMMIT ; DELIMITER GO CREATE TRIGGER country_INSERT AFTER INSERT ON country FOR EACH ROW BEGIN IF new.country_id IN (SELECT region_dept_id FROM region_departement WHERE region_dept_id = new.country_id) THEN SET @erreur = CONCAT('country_id = ', new.country_id, ' : code déjà utilisée pour une région (ou un département) !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; IF new.country_id IN (SELECT city_id FROM city WHERE city_id = new.country_id) THEN SET @erreur = CONCAT('country_id = ', new.country_id, ' : code déjà utilisée pour une ville !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; END GO CREATE TRIGGER region_departement_INSERT AFTER INSERT ON region_departement FOR EACH ROW BEGIN IF new.region_dept_id IN (SELECT country_id FROM country WHERE country_id = new.region_dept_id) THEN SET @erreur = CONCAT('region_dept_id = ', new.region_dept_id, ' : code déjà utilisée pour un pays !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; IF new.region_dept_id IN (SELECT city_id FROM city WHERE city_id = new.region_dept_id) THEN SET @erreur = CONCAT('region_dept_id = ', new.region_dept_id, ' : code déjà utilisée pour une ville !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; END GO CREATE TRIGGER city_INSERT AFTER INSERT ON city FOR EACH ROW BEGIN IF new.city_id IN (SELECT country_id FROM country WHERE country_id = new.city_id) THEN SET @erreur = CONCAT('city_id = ', new.city_id, ' : code déjà utilisée pour un pays !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; IF new.city_id IN (SELECT region_dept_id FROM region_departement WHERE region_dept_id = new.city_id) THEN SET @erreur = CONCAT('city_id = ', new.city_id, ' : code déjà utilisée pour une region (ou un département) !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; END GO DELIMITER ;
Un jeu d’essai :
=>insert into place (place_name, type_place_id) VALUES ('France', 1) , ('Belgique', 1) , ('Allemagne', 1) , ('Espagne', 1) , ('Luxembourg', 1) , ('Italie', 1) , ('Suisse', 1) , ('Relationland', 1) ; insert into place (place_name, type_place_id) VALUES ('Île-de-France', 2) , ('Alsace', 2) , ('Lorraine', 2) , ('Bretagne', 2) , ('Bourgogne', 2) , ('Provence', 2) , ('Picardie', 2) , ('Artois', 2) , ('Auvergne', 2) ; insert into place (place_name, type_place_id) VALUES ('Paris', 3) , ('Hauts-de-Seine', 3) , ('Essonne', 3) , ('Finistère', 3) , ('Bas-Rhin', 3) , ('Haut-Rhin', 3) , ('Meurthe-et-Moselle', 3) , ('Bouches-du-Rhône', 3) , ('Somme', 3) , ('Pas-de-Calais', 3) , ('Puy-de-Dôme', 3) , ('Cantal', 3) ; insert into place (place_name, type_place_id) VALUES ('Bavière', 2) , ('Bade-Wurtemberg', 2) , ('Munich', 4) , ('Stuttgart', 4) , ('Reutlingen', 4) ; insert into place (place_name, type_place_id) VALUES ('Paris', 4) , ('Brest', 4) , ('Quimper', 4) , ('Strasbourg', 4) , ('Colmar', 4) , ('Longwy', 4) , ('Arles', 4) , ('Nancy', 4) , ('Calais', 4) , ('Amiens', 4) ; insert into country (country_id, country_code) VALUES (1, 'FRA') , (2, 'BEL') , (3, 'GER') , (4, 'ESP') , (5, 'LUX') , (6, 'ITA') , (7, 'SUI') , (8, 'REL') ; /* insert into country (country_id, country_code) VALUES (1243, '!!!') ; -- doit planter (erreur intégrité référentielle) */ insert into region_departement (region_dept_id, latitude, longitude) VALUES (9, 0, 0) , (10, 0 ,0) , (11, 0 ,0) , (12, 0 ,0) , (13, 0 ,0) , (14, 0 ,0) , (15, 0 ,0) , (16, 48.8534100, 2.3488000) , (17, 48.8285080, 2.2188068) , (18, 0 ,0) , (19, 0 ,0) , (20, 0 ,0) , (21, 0 ,0) , (22, 0 ,0) , (23, 0 ,0) , (24, 0 ,0) , (25, 0 ,0) , (26, 0 ,0) , (27, 0 ,0) , (28, 0 ,0) , (29, 0 ,0) , (30, 0 ,0) , (31, 0 ,0) ; insert into region (region_id, region_code, country_id) VALUES (9, 'IDF', 1) , (10, 'ALSA', 1) , (11, 'LORR', 1) , (12, 'BRET', 1) , (13, 'BOURG', 1) , (14, 'PROV', 1) , (15, 'PICAR', 1) , (16, 'ART', 1) , (17, 'AUV', 1) , (30, 'BAV', 3) , (31, 'BAD', 3) ; insert into departement (dept_id, dept_code, region_id) VALUES (18, '75', 9) , (19, '92', 9) , (20, '91', 9) , (21, '29', 12) , (22, '67', 10) , (23, '68', 10) , (24, '54', 11) , (25, '13', 14) , (27, '62', 16) , (26, '80', 15) , (28, '63', 17) , (29, '15', 17) ; insert into city (city_id, latitude, longitude, postal_code, region_dept_id) VALUES (32, 48.1448000 ,11.558000, '', 30) , (33, 48.7754000 ,9.1818000, '', 31) , (34, 48.5069000, 9.2038000, '', 31) , (35, 48.8534100, 2.3488000, '75000', 18) , (36, 48.3904000, -4.4861000, '29200', 21) , (37, 47.9975000, -4.0979000, '29000', 21) , (38, 48.5852000, 7.7343000, '67000', 22) , (39, 48.0794000, 7.3585000, '68000', 23) , (40, 49.5199000, 5.7618000, '54400', 24) , (41, 43.6767000, 4.6275000, '13004', 25) , (42, 48.6921000, 6.1844000, '54000', 24) , (43, 50.9513000, 1.8587000, '62100', 27) , (44, 49.8941000, 2.2958000, '80000', 26) ; insert into place (place_name, type_place_id) VALUES ('Normandie', 2) , ('Manche', 3) , ('Calvados', 3) , ('Alpes-Maritimes', 3) , ('Caen', 4) , ('Cannes', 4) ; insert into region_departement (region_dept_id, latitude, longitude) VALUES (45, 0, 0) , (46, 0, 0) , (47, 0, 0) , (48, 0, 0) ; insert into region (region_id, region_code, country_id) VALUES (45, 'NORM', 1) ; insert into departement (dept_id, dept_code, region_id) VALUES (46, '50', 45) , (47, '14', 45) , (48, '06', 14) ; insert into city (city_id, latitude, longitude, postal_code, region_dept_id) VALUES (49, 49.1829000, -0.3707000, '14000', 47) , (50, 43.5528000, 7.0174000, '06400', 48) ; -- Les départements select b.place_name as departement, d.place_name as region, f.place_name as country from departement AS a JOIN place AS b ON a.dept_id = b.place_id JOIN region AS c ON a.region_id = c.region_id JOIN place AS d ON c.region_id = d.place_id JOIN country AS e ON c.country_id = e.country_id JOIN place AS f ON e.country_id = f.place_id ; -- Les villes select b.place_name as city, d.place_name as departement, f.place_name as region, h.place_name as country from city AS a JOIN place AS b ON a.city_id = b.place_id JOIN departement AS c ON a.region_dept_id = c.dept_id JOIN place AS d ON c.dept_id = d.place_id JOIN region AS e ON c.region_id = e.region_id JOIN place AS f ON e.region_id = f.place_id JOIN country AS g ON e.country_id = g.country_id JOIN place AS h ON g.country_id = h.place_id UNION ALL select b.place_name as city, ' ' as departement, f.place_name as region, h.place_name as country from city AS a JOIN place AS b ON a.city_id = b.place_id JOIN region AS e ON a.region_dept_id = e.region_id JOIN place AS f ON e.region_id = f.place_id JOIN country AS g ON e.country_id = g.country_id JOIN place AS h ON g.country_id = h.place_id where not exists (select '' from departement as x where a.region_dept_id = x.dept_id) ;
city departement region country ---------- ------------------ --------------- ---------- Paris Paris Île-de-France France Brest Finistère Bretagne France Quimper Finistère Bretagne France Strasbourg Bas-Rhin Alsace France Colmar Haut-Rhin Alsace France Longwy Meurthe-et-Moselle Lorraine France Nancy Meurthe-et-Moselle Lorraine France Arles Bouches-du-Rhône Provence France Amiens Somme Picardie France Calais Pas-de-Calais Artois France Caen Calvados Normandie France Cannes Alpes-Maritimes Provence France Munich Bavière Allemagne Stuttgart Bade-Wurtemberg Allemagne Reutlingen Bade-Wurtemberg Allemagne
Je regarderai la suite.
Si vous avez des observations à faire...
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
triggers pour les places
Bonsoir Nike,
J’ai aménagé les triggers pour empêcher qu’on insère dans un pays autre chose qu’un pays, même principe pour les régions, les départements et les villes :
CREATE TRIGGER country_INSERT AFTER INSERT ON country FOR EACH ROW BEGIN IF new.country_id IN (SELECT region_dept_id FROM region_departement WHERE region_dept_id = new.country_id) THEN SET @erreur = CONCAT('country_id = ', new.country_id, ' : code déjà utilisée pour une région (ou un département) !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; IF new.country_id IN (SELECT city_id FROM city WHERE city_id = new.country_id) THEN SET @erreur = CONCAT('country_id = ', new.country_id, ' : code déjà utilisée pour une ville !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; IF new.country_id NOT IN (SELECT x.place_id FROM place as x JOIN type_place as y ON x.type_place_id = y.type_place_id WHERE y.type_place_name = 'country') THEN SET @erreur = CONCAT('country_id = ', new.country_id, ' : type de place <> ''country'' !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; END GO CREATE TRIGGER region_departement_INSERT AFTER INSERT ON region_departement FOR EACH ROW BEGIN IF new.region_dept_id IN (SELECT country_id FROM country WHERE country_id = new.region_dept_id) THEN SET @erreur = CONCAT('region_dept_id = ', new.region_dept_id, ' : code déjà utilisée pour un pays !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; IF new.region_dept_id IN (SELECT city_id FROM city WHERE city_id = new.region_dept_id) THEN SET @erreur = CONCAT('region_dept_id = ', new.region_dept_id, ' : code déjà utilisée pour une ville !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; END GO CREATE TRIGGER region_INSERT AFTER INSERT ON region FOR EACH ROW BEGIN IF new.region_id NOT IN (SELECT x.place_id FROM place as x JOIN type_place as y ON x.type_place_id = y.type_place_id WHERE y.type_place_name = 'region') THEN SET @erreur = CONCAT('region_id = ', new.region_id, ' : type de place <> ''region'' !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; END GO CREATE TRIGGER departement_INSERT AFTER INSERT ON departement FOR EACH ROW BEGIN IF new.dept_id NOT IN (SELECT x.place_id FROM place as x JOIN type_place as y ON x.type_place_id = y.type_place_id WHERE y.type_place_name = 'departement') THEN SET @erreur = CONCAT('dept_id = ', new.region_id, ' : type de place <> ''departement'' !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; END GO CREATE TRIGGER city_INSERT AFTER INSERT ON city FOR EACH ROW BEGIN IF new.city_id IN (SELECT country_id FROM country WHERE country_id = new.city_id) THEN SET @erreur = CONCAT('city_id = ', new.city_id, ' : code déjà utilisée pour un pays !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; IF new.city_id IN (SELECT region_dept_id FROM region_departement WHERE region_dept_id = new.city_id) THEN SET @erreur = CONCAT('city_id = ', new.city_id, ' : code déjà utilisée pour une region (ou un département) !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; IF new.city_id NOT IN (SELECT x.place_id FROM place as x JOIN type_place as y ON x.type_place_id = y.type_place_id WHERE y.type_place_name = 'city') THEN SET @erreur = CONCAT('city_id = ', new.city_id, ' : type de place <> ''city'' !') ; SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @erreur ; END IF ; END GO
Je passe aux tarifs.
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
Bonjour fsmrel,
Pour ma part j'utilise MySQL 5.5.
J'utiliserai Google Places API pour que les chauffeurs enregistrent leurs lieux d'activité. Avec Google Places on a "administrative_area_level_1" qui correspond à des "sous-régions" pour certains pays ou des départements pour la France et "administrative_area_level_2" qui correspond aux régions dans tous les pays. Je ne pense pas avoir besoin de region_code et de dept_code, car ils changeraient pour chaque pays de toute facon. Il vaut mieux utiliser latitude et longitude.
Pour country_code c'est une clé alternative oui, car on a country_id.
Je ne comprends pas pourquoi " UNIQUE INDEX type_place_id_UNIQUE" fait double emploie.
Pour le country_place_idx, sur MySQL workbench il correspond bien à ce que j'ai sélectionné dans la capture d'écran ci-dessous qu'il faudrait supprimer?
C'est parfait pour l'insertion des lieux! C'est une bonne idée de mettre les longitude et latitude dans la REGION_DEPARTEMENT.
Donc oui, enfaite les "id" de "region", "departement", "city" et "country" ne sont jamais les même. C'est toujours une succession.
Merci pour les excelentes corrections!
Bonne journée!
Never complain, never explain?
Bonsoir Nike,
Je me rends compte que j’ai écrit :
Il y a évidemment une coquille que je viens de corriger dans le message en cause, il fallait bien sûr lire :
« Table region : à quoi correspond la colonne region_code ? »
D’accord pour supprimer ces deux attributs ? Pas de regret ?
Prenons le cas de la requête suivante :
select * from type_place where type_place_id = 1 ;
On peut demander à MySQL comment il envisage de traiter cette requête, notamment en termes de performance (domaine où le rôle des index est crucial). A cet effet, on utilise l’instruction EXPLAIN :
explain select * from type_place where type_place_id = 1 ;
La réponse faite par MySQL :
Ce qui se lit : pour accéder aux données demandées de la table type_place :
1) Selon la colonne « possible_keys », il existe deux index permettant de booster l’accès, à savoir l’index primaire (PRIMARY) et sa copie exacte, l’index type_place_id_UNIQUE ;
2) Selon la colonne « key », l’index retenu est l’index primaire.
La conclusion s’impose : l’index type_place_id_UNIQUE est un doublon, MySQL ne s’en servira jamais, donc cet index parasite peut être éliminé. N’oublions jamais que lorsqu’on met à jour une table (INSERT, UPDATE, DELETE), cela consomme beaucoup de ressources concernant les index, ça ralentit les opérations et j’en passe : il faut à tout prix éliminer les index inutiles.
Corollaire : Pour s’assurer de la bonne performance du système, même si ça n’est pas toujours suffisant, il est nécessaire que chaque requête fasse l’objet d’un EXPLAIN si l’on veut en connaître objectivement le comportement.
Partons du CREATE TABLE country suivant :
Là encore, l’index country_place_idx est un doublon de l’index primaire et doit disparaître.CREATE TABLE country ( country_id INT NOT NULL, country_code varchar(3) NOT NULL, CONSTRAINT country_pk PRIMARY KEY (country_id), CONSTRAINT country_ak UNIQUE (country_id), -- ----- INDEX country_place_idx (country_id ASC), -- double emploi : virer ! CONSTRAINT country_place_fk FOREIGN KEY (country_id) REFERENCES place (place_id) ON DELETE CASCADE ON UPDATE NO ACTION , UNIQUE INDEX country_code_idx (country_code ASC) ) ;
Examinons maintenant le cas de l’accès à la table country en fonction de l’attribut country_code :
select * from country where country_code = 'FRA' ;
Et soumettons la requête à EXPLAIN.
1) Présence de l’index country_code_idx :
Manifestement, MySQL utilise l’index.
2) Absence de l’index country_code_idx :
Cette fois-ci, MySQL n’utilise aucun index et balaie donc la table country pour y chercher les lignes répondant à la requête.
Vous me direz que la table country sera d’un très faible volume, donc que le temps de réponse ne sera pas pénalisé. Mais dans le cas d’une table de quelques centaines de milliers de lignes, ça serait catastrophique, car MySQL devrait crapahuter dans des milliers, ou dizaines de milliers de pages, et à raison de 10 à 20 millisecondes par page, ça cuberait ! Alors qu’avec l’index, en 3 ou 4 lectures de pages l’affaire est réglée...
Puisqu'il fait double emploi, il faut effectivement le supprimer.
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
Aucun regret ! Supprimons region_code et dept_code.
OK grâce à cette instruction en effet c'est plus clair.
Prenons le cas de la requête suivante :
select * from type_place where type_place_id = 1 ;
On peut demander à MySQL comment il envisage de traiter cette requête, notamment en termes de performance (domaine où le rôle des index est crucial). A cet effet, on utilise l’instruction EXPLAIN :
explain select * from type_place where type_place_id = 1 ;
La réponse faite par MySQL :
Ce qui se lit : pour accéder aux données demandées de la table type_place :
1) Selon la colonne « possible_keys », il existe deux index permettant de booster l’accès, à savoir l’index primaire (PRIMARY) et sa copie exacte, l’index type_place_id_UNIQUE ;
2) Selon la colonne « key », l’index retenu est l’index primaire.
La conclusion s’impose : l’index type_place_id_UNIQUE est un doublon, MySQL ne s’en servira jamais, donc cet index parasite peut être éliminé. N’oublions jamais que lorsqu’on met à jour une table (INSERT, UPDATE, DELETE), cela consomme beaucoup de ressources concernant les index, ça ralentit les opérations et j’en passe : il faut à tout prix éliminer les index inutiles.
OK, je comprends. "country_place_idx" n'y apparaît même pas dans les "possible_keys".
Examinons maintenant le cas de l’accès à la table country en fonction de l’attribut country_code :
select * from country where country_code = 'FRA' ;
Et soumettons la requête à EXPLAIN.
1) Présence de l’index country_code_idx :
Manifestement, MySQL utilise l’index.
2) Absence de l’index country_code_idx :
Cette fois-ci, MySQL n’utilise aucun index et balaie donc la table country pour y chercher les lignes répondant à la requête.
Vous me direz que la table country sera d’un très faible volume, donc que le temps de réponse ne sera pas pénalisé. Mais dans le cas d’une table de quelques centaines de milliers de lignes, ça serait catastrophique, car MySQL devrait crapahuter dans des milliers, ou dizaines de milliers de pages, et à raison de 10 à 20 millisecondes par page, ça cuberait ! Alors qu’avec l’index, en 3 ou 4 lectures de pages l’affaire est réglée...
Merci pour ces explications!
Bonne soirée.
Pour compléter les excellentes explications qui précèdent, n'oublions pas que même si la table qui ne possède pas d'index éligible est de très faible volumétrie, les conséquences peuvent être spectaculaires si cette table est utilisée en jointure avec une autre, qui elle est volumineuse
Exemple la requête suivante :
Dans ce cas, la table des pays sera parcourue séquentiellement autant de fois qu'il y aura d'individus sélectionnés par le WHERE
Code : Sélectionner tout - Visualiser dans une fenêtre à part
2
3
4
5
6
7
8
9Select INDV.Col1 ,INDV.Col2 ,... ,INDV.Coln ,PAYS.Libelle From individus as INDV inner join PAYS as PAYS ON PAYS.CDPY = INDV.CDPY Where....
Même si les données fréquemment utilisées sont chargées en mémoire, il n'en reste pas moins qu'on effectue ici un travail répétitif inutile et qui plombe les perfs
J'en termine ici avec cette digression technique un peu en marge de la modélisation conceptuelle
Bonsoir,
Nike, êtes vous obligé de rester avec MySQL ? Plus j’examine ce qu’il y a sous le capot, plus je suis sidéré. Par exemple, toutes les tables tiennent dans des pages dont la taille est unique pour une instance MySQL (donc pour l’ensemble des bases de données qui s’y rattachent, et cette taille n’est pas modifiable, j’ai l’impression de revenir à la préhistoire...)
Je me bats notamment, avec les contraintes imposées au clés primaires, là où avec d’autres SGBD il n’y a pas de problème...
En attendant, une modification du diagramme MySQL Workbench me paraît s’imposer. Sont concernées : les tables CATEGORY, PRICE, DRIVER, DRIVER_PRICE et DRIVER_VEHICLE_CATEGORY. En effet, rien n’empêche actuellement que, pour un chauffeur donné, la table DRIVER_PRICE fasse référence à des catégories, tandis que pour ce chauffeur, la table DRIVER_VEHICLE_CATEGORY fasse référence à d’autres catégories...
Même principe pour les forfaits (table DRIVER_PRICE_FIXED).
Pour blinder l’édifice, je propose la mise en oeuvre d’une table DRIVER_CATEGORY où sont répertoriées les catégories pour chaque chauffeur, et qui soit une référence pour les tables porteuses des attributs driver_id et category_id. La partie concernée du diagramme deviendrait la suivante :
Dans le cas général, certes, mais dans le cas particulier que j'ai évoqué, je ne le pense pas. En effet, si j’ai bien compris, la base de données ne concernera que des pays européens, et en plus, chaque ligne de la table country occupe 10 octets ou moins : pour quelques dizaines de lignes, une page suffit. Mais n’étant pas DBA MySQL (je le fus plutôt DBA DB2, de la V1 à V6), comme Nike, j’accepte toutes les bonnes remarques permettant d’optimiser les temps de réponse des requêtes portant sur sa base de données !
Je poursuis mes investigations quant à la structure des tables...
@escartefigue,
Est-ce bien vous, là ?
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
Damned, je suis repéré !@escartefigue,
Est-ce bien vous, là ?
En effet, la bonhomie et la naïveté de ce personnage de Pagnol (ici revu par Uderzo), m'a beaucoup plu, et notamment dans la fameuse scène de la partie de cartes où il "fend le cœur" de César !
Par contre, son Homonyme https://fr.wikipedia.org/wiki/Marius_Escartefigue est beaucoup moins sympathique !
Bonsoir Fsmrel,
J'ai l'habitude d'utiliser MySQL depuis toujours et je ne saurais peut-être pas comment utiliser un autre SGBD pour le développer de mon site... C'est mon problème. Quand vous dites qu'avec d'autres SGBD il n'y a pas de problème vous pensez aux quels?
OK je vois oui.
Voici ce que ca donne ci-dessous avec DRIVER_CATEGORY en plus. Es-ce bien correct?
J'ai joint le nouveau fichier SQL: choosedriver.sql
Et aussi le locksize Row ou Table, pas de locksize page
+ les nombreuses fonctions non disponibles ou mal implémentées
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Répondre avec citation
Partager