Salut,

Dans un cadre professionnel (Je suis en stage dans une administration communale), je commence de réfléchir à la base de données que je vais devoir modéliser et je suis bloqué à un endroit.

En effet, je dois pouvoir enregistrer dans ma base de données les horaires d'ouverture de différents guichets (Guichets physiques à destination de la population). J'enregistre ces données dans une DB car c'est des horaires qui varient suivant la période de l'année et que mes collaborateurs (non-informaticiens) doivent pouvoir les modifier sans trifouiller dans mon code.

Les horaires devront s'afficher sous la forme d'un tableau par demi-journée. Exemple:

Lundi            9h00 - 12h00            14h00 - 17h00

Mardi            9h00 - 12h00            14h00 - 17h00

Mercredi       9h00 - 12h00            14h00 - 17h00

...

...

...

J'ai créé 3 tables (J'ai tout fait sur papier alors je le transcris du mieux que je peux) :

Petite précision:

Dans la table tb_Interval, la clé primaire (pk) sera composées de la totalité des champs de cette table (Donc heure_debut, minute_debut, heure_fin, minute_fin). Je sais bien que je peux utiliser le type DATE pour diminuer de moitié le nombre de champs dans la table tb_Intervalmais normalement, ces données seront de simples entiers et je ferai une vérif lors de la saisie/modification que les heures ne dépassent pas 23 et les minutes 60.

Est-ce que mon MCD vous paraît correcte et est-ce que vous auriez des conseils à me donner là-dessus ?

Merci d'avoir pris le temps de lire ce sujet

-
Edité par Je s'appelle Groux 21 janvier 2020 à 16:54:20

Bonjour,

Je s'appelle Groux a écrit:

différents guichets [...] des horaires qui varient suivant la période de l'année

Tu as donc des jours, des guichets et des horaires : 3 entités, donc 3 tables.

Ensuite tu as une relation ternaire (liant 3 entités).

Pour un jour, un guichet a un horaire, cela sur une période donnée, par exemple : Les lundis du 1er janvier 2020 au 15 février 2020, le guichet 1 est ouvert de 9h à midi et de 14h à 17h.

Ton MCD serait donc plutôt :

Ce qui donne le MLD suivant :

• horaire ( id_horaire, heure_debut_am, heure_fin_am, heure_debut_pm, heure_fin_pm ) avec UNIQUE( heure_debut_am, heure_fin_am, heure_debut_pm, heure_fin_pm )
• guichet ( id_guichet, nom ) avec UNIQUE( nom )
• jour ( id_jour, libelle ) avec UNIQUE( libelle ) et si possible 1=Lundi, 2= Mardi, etc. pour faciliter la lecture BDD
• planning ( id_horaire, id_jour, id_guichet, date_debut, date_fin ) avec PK( id_horaire, id_jour, id_guichet, date_debut )

Il faut également mettre en place un contrôle côté BDD pour empêcher de faire chevaucher deux horaires sur une même période pour un même guichet. Cela se fera à l'aide d'un TRIGGER BEFORE INSERT ON planning, qui retournera une erreur que tu utiliseras pour informer l'utilisateur, genre "Un horaire est déjà défini pour ce guichet sur cette période".

-
Edité par Benzouye 21 janvier 2020 à 18:13:06

Bonjour,

Merci Benzouye pour ces précisions, elles me sont d'une grande utilité.

--------------------------------------------------------------------------------------------
EDIT (Je ne peux pas écrire de nouveau message avant 24H ):
--------------------------------------------------------------------------------------------

J'ai fini de créer le script SQL de création des tables et je me suis attaqué à la création du trigger et j'avoue être un peu bloqué .

Voici déjà la structure des mes différentes tables:

CREATE TABLE IF NOT EXISTS tb_guichet (
    pk_guichet INT UNSIGNED NOT NULL AUTO_INCREMENT,
	nom_guichet VARCHAR(50) UNIQUE NOT NULL,
	PRIMARY KEY (pk_guichet)
);

CREATE TABLE IF NOT EXISTS tb_horaire (
	pk_horaire INT UNSIGNED NOT NULL AUTO_INCREMENT,
	heure_debut_am TIME UNIQUE,
	heure_fin_am TIME UNIQUE,
	heure_debut_pm TIME UNIQUE,
	heure_fin_pm TIME UNIQUE,
	PRIMARY KEY (tb_horaire)
);

CREATE TABLE IF NOT EXISTS tb_jour (
	pk_jour INT UNSIGNED NOT NULL AUTO_INCREMENT,
	libelle_jour VARCHAR(8) UNIQUE NOT NULL,
	PRIMARY KEY (pk_jour)
);

CREATE TABLE IF NOT EXISTS tb_planning (
	fk_pk_guichet INT UNSIGNED NOT NULL,
	fk_pk_horaire INT UNSIGNED NOT NULL,
	fk_pk_jour INT UNSIGNED NOT NULL,
	date_debut DATE,
	date_fin DATE,
	PRIMARY KEY (fk_pk_guichet, fk_pk_horaire, fk_pk_jour, date_debut),
	FOREIGN KEY (fk_pk_guichet) REFERENCES tb_guichet (pk_guichet),
	FOREIGN KEY (fk_pk_horaire) REFERENCES tb_horaire (pk_horaire),
	FOREIGN KEY (fk_pk_jour) REFERENCES tb_jour (pk_jour)
);

(J'espère que j'ai pas fait de fautes, je connais un peu le SQL basique mais ça fait bien 2-3 ans que j'en ai plus fait )

Je bloque dans la création du trigger.
J'en ai jamais fait et je ne me rend pas bien compte comment faire pour que le trigger parcourt l'entièreté de ma table tb_planning et vérifie pour chaque ligne que la date de début et de fin de l'insert (Qui déclenche donc le trigger) ne soient pas comprises entre le date de début et de fin de la ligne actuellement parcoure par le trigger.

Après quelques recherches, j'ai trouvé un bout de code sur StackOverFlow et je l'ai adapté, est-ce que ça vous paraît correct comme façon de faire ? (Notamment sur la façon de lancer une erreur et d'arrêter l'insert en cas de problème)

CREATE TRIGGER validationHoraireUnique BEFORE INSERT ON tb_planning FOR EACH ROW
BEGIN
	IF EXISTS (SELECT * FROM tb_planning WHERE (NEW.date_debut BETWEEN date_debut AND date_fin) OR (NEW.date_fin BETWEEN date_debut AND date_fin)) THEN
		BEGIN
			RAISERROR ('Un horaire est déjà défini pour ce guichet à cette période');
			ROLLBACK;
		END;
	END IF;
END;

Merci encore de prendre le temps de m'aider, c'est super sympa 

-
Edité par Je s'appelle Groux 22 janvier 2020 à 16:21:26

Bonjour,

Je me permet de relancer le sujet vu que personne n'a répondu à mon dernier message..

Est-ce que mon TRIGGER vous paraît correct ?

Avec quel SGBD travailles-tu ?

RAISERROR ne fonctionnera pas avec MySQL, et ROLLBACK ne fonctionnera qu'au sein d'une transaction.

Mon environnement de développement et de production ne sont pas encore installé ni défini. Mais je pensait effectivement utiliser MySQL, car c'est le seul avec lequel j'ai déjà travailler et que je n'ai pas de problème à m'y connecter en PHP pour y effectuer des requêtes (L'application finale sera une page web).

Concernant le ROLLBACK, je suis au courant que ce mot-clé effectue une annulation d'une transaction mais qu'il ne fonctionne que durant cette dite-transaction. Si tu en parles, j'imagine que c'est parce que vu que mon TRIGGER se déclenche AVANT l'insertion et que du coup peut-être que la transaction n'aura pas encore débuté ? Comment puis-je l'annuler autrement alors ?

Si je ne devrais écouter que moi, j'effectuerai la vérification dans l'interface de saisie car je suis bien plus à l'aise avec le web qu'avec les bases de données mais je sais que c'est pas très propre de faire comme ça.

J'y pense maintenant aussi mais vu que ce travail sera pour des guichets communaux dont les horaires varient très peu (Juste pendant les vacances d'été et de Noël), ne serait-ce pas mieux pour moi d'avoir un horaire de base et de le remplacer à l'affichage uniquement lorsqu'un horaire spécial est défini et de réafficher l'horaire de base lorsque l'horaire spécial est échu ?

Je pense à ça car le jour de commencement du prochain horaire "spécial" n'est pas encore défini lors de la fin du précédent horaire "spécial"... Je m'explique --> À la fin de l'horaire spécial en fonctionnement durant les vacances de Noël 2019, les dates du prochain horaire spécial (Période des vacances scolaires estivales) ne sont pas encore définies. Je sais pas si ce que je veux dire est clair

Je s'appelle Groux a écrit:

mon TRIGGER se déclenche AVANT l'insertion et que du coup peut-être que la transaction n'aura pas encore débuté ?

Une transaction démarre lorsque tu le demandes, avec START TRANSACTION ... https://dev.mysql.com/doc/refman/8.0/en/commit.html

Attention, car :

La doc MySQL précise:

By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START TRANSACTION and COMMIT. You cannot use ROLLBACK to undo the effect; however, if an error occurs during statement execution, the statement is rolled back.

Je s'appelle Groux a écrit:

je pensait effectivement utiliser MySQL

Benzouye avait écrit:

RAISERROR ne fonctionnera pas avec MySQL

Il faut utiliser SIGNAL. https://dev.mysql.com/doc/refman/8.0/en/signal.html

Enfin, tu peux simplifier ta clause WHERE.

Pour MySQL, le TRIGGER pourrait donc être :

DELIMITER &&

CREATE TRIGGER validationHoraireUnique
BEFORE INSERT ON tb_planning
FOR EACH ROW
BEGIN
	IF EXISTS (
		SELECT *
		FROM tb_planning
		WHERE
			NEW.date_debut <= date_fin
			AND NEW.date_fin >= date_debut )
	THEN
		SIGNAL SQLSTATE '23000'
			SET MESSAGE_TEXT = 'Un horaire est déjà défini sur cet intervalle';
	END IF;
END&&

DELIMITER ;

J'ai mis SQLSTATE 23000 car c'est le code d'erreur en cas de conflit d'unicité.

Je s'appelle Groux a écrit:

j'effectuerai la vérification dans l'interface de saisie car je suis bien plus à l'aise avec le web qu'avec les bases de données mais je sais que c'est pas très propre de faire comme ça

Le problème c'est qui si tu insères autrement que par ton interface applicative, le contrôle ne sera pas fait ... Tu peux par contre faire le contrôle des deux côtés, dans ton application (avec un appel AJAX par exemple) cela évite de recharger le formulaire de saisie si un horaire existe déjà ...

Je s'appelle Groux a écrit:

le jour de commencement du prochain horaire "spécial" n'est pas encore défini lors de la fin du précédent horaire "spécial"...

En effet, le problème est plus embêtant ... peut-être qu'il serait alors intéressant de mettre au point un mécanisme d'horaire par défaut et de n'enregistrer que les horaires spéciaux ... Si aucune horaire spécial enregistré alors tu affiches l'horaire par défaut. Tu pourrais du coup définir un horaire par défaut par guichet ...

-
Edité par Benzouye 11 février 2020 à 16:00:44

Merci beaucoup Benzouye pour ton aide,

Je vais me renseigner sur ce fameux SIGNAL en SQL. Et j'effectuerai une deuxième vérification dans l'application de saisie pour valider les entrées avant de les envoyer.

Je note le sujet comme résolu