Discussion :

[melwin]

Bonjour à tous

j'ai crée une base de donnée Sqlite3 et je travaille avec SQLite Expert Personnal. Le but étant d'attaquer au final cette base avec un programme Python vers 3.3

table CLIENT ( client_id, nom, prenom, adresse, cp, ville, tel)

table MATERIEL (materiel_id, modele, marque)

table PRET(pret_id, date_debut, date_fin, nom, materiel)

les clés primaire sont soulignées, les clés étrangères en italique.

Voilà ma question, quelle commande pour savoir si le matériel est disponible ou en prêt? Pour un jour donné, une période donnée etc...


merci

[seabs]

Bonjour,

J'ai examiné ta question et en préambule, je ferai deux remarques.

table PRET(pret_id, date_debut, date_fin, nom, materiel)

A mon avis, les clés étrangères dans cette table devraient être

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

table PRET(pret_id, date_debut, date_fin, Client_Id, materiel_id)

.
Avec ton approche, il est possible de prêter à un autre client un matériel qui est non disponible. Il faut revoir ta modélisation.

La requête pour visualiser la disponibilité d'un matériel peut s'appuyer sur ce principe.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT DATE_DEBUT, DATE_FIN, MODELE, MARQUE, NOM, PRENOM
FROM MATERIEL M
  LEFT JOIN PRET P ON M.Materiel_Id = P.Materiel_Id
  LEFT JOIN CLIENT C ON M.Client_Id = C.Client_Id
WHERE Materiel_Id = :Materiel_Id

A parfaire en fonction du cas réel.

Ceci est une première approche, car il faut connaître l'index du matériel pour exploiter la requête. Si tu veux partir du modèle et de la marque cela devient plus compliqué. Cette méthode est possible si tu as quelques matériels, mais pour un nombre important les délais de traitement deviendront rapidement longs.

Il faut peut-être envisager de créer une colonne avec identifiant matériel, laquelle sera indexée pour optimiser la recherche.

Beaucoup de points inconnus, reviens lorsque tu auras avancé et effectué les aménagements préconisés ou les raisons qui font qu'ils sont impossibles à appliquer.

Bon courage

[melwin]

Bonjour,

merci de ta réponse.

A mon avis, les clés étrangères dans cette table devraient être

Pour ce qui est de des clés étrangère, c'est une erreur quand j'ai fait le message, effectivement c'est bien client_id et materiel_id, .

Avec ton approche, il est possible de prêter à un autre client un matériel qui est non disponible. Il faut revoir ta modélisation.

C'est bien pour cela que je pose la question, en me doutant qu'il faudrait sûrement revoir la modélisation. Ça faisait un moment que je n'avais pas remis mon nez là dedans.

pour un nombre important les délais de traitement deviendront rapidement longs

Pour ce qui est du nombre de matériels, cela devrait se limiter à une dizaine environ, donc pas énorme.

Il faut peut-être envisager de créer une colonne avec identifiant matériel

La colonne identifiant matériel ce n'est pas materiel_id?

[seabs]

Bonjour

Pour ce qui est de des clés étrangère, c'est une erreur quand j'ai fait le message, effectivement c'est bien client_id et materiel_id,

J'ai pris bonne note. Par contre, dans votre situation, il me semble préférable d'utiliser l'identification relative

Avant de poursuivre, je voudrais savoir si au moment de la location vous connaissez la date de début et la date de fin de location. Si oui, il me paraît intéressant d'ajouter dans la table PRET une colonne NbJoursLocation. Ainsi votre table serait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

table PRET(pret_id, date_debut, date_fin, NbJoursLocation, CLIENT_ID, MATERIEL_ID)

Mais avec le nombre de jours de location, il est possible dans reconstituer dans une vue la date de fin de location. Vous pouvez simplifier la table PRET

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

table PRET(pret_id, date_debut, NbJoursLocation, CLIENT_ID, MATERIEL_ID)

Il faut maintenant éviter de louer à deux clients différents le même matériel pendant la même période, nous avons une contrainte qu'il faudra traiter avec un trigger.

Pour vérifier mes dires, je vais construire sur Sqlite de votre base de données et vérifier le fonctionnement. En effet, j'utilise très peu Sqlite et ne me souviens plus des limitations de ce produit. Avec une base PostgrSQL, l'implantation se ferait sans souci.

En fonction de vos réponses, je reverrai la modélisation et établirai un prototype de la base.

La colonne identifiant matériel ce n'est pas materiel_id?

S'il n'y a qu'un dizaine de matériel, cette approche peut être suffisante

Bon courage

[melwin]

Bonjour,

encore merci pour votre aide. Je me replonge dans la modélisation et je me rends compte que j'ai oublié pas mal de chose et que je suis pas mal rouillé.

il me semble préférable d'utiliser l'identification relative

Si je comprends bien, utiliser les 2 clés étrangères comme clé pour identifier les prêts?

Je voudrais savoir si au moment de la location vous connaissez la date de début et la date de fin de location

On connaît surtout la date de début et normalement la date de retour. Le but étant de savoir si le matériel est toujours en prêt et de connaître le matériel disponible pour un nouveau prêt. Il faut pouvoir retrouver le client qui n'a pas ramené le matériel pour le contacter. Il n'est pas prévu de réservation, donc en cas de retour de matériel en retard n'engendre pas de problème outre le fait de n'avoir le matériel à disposition pour un nouveau prêt.

il me paraît intéressant d'ajouter dans la table PRET une colonne NbJoursLocation

J'étais justement, suite à votre précédent message, en train de revoir la modélisation et d'envisager une durée d'emprunt.

nous avons une contrainte qu'il faudra traiter avec un trigger

Là je coince, je vais fouiller parce que c'est flou pour moi.

j'utilise très peu Sqlite et ne me souviens plus des limitations de ce produit. Avec une base PostgrSQL, l'implantation se ferait sans souci

Comme je programme en Python, je suis parti avec Sqlite vu que la base ne sera pas énorme. Mais ça ne me dérange pas de passer sous PostgreSQL si vous pensez que ce serait plus simple

[SQLpro]

Si je comprends bien, utiliser les 2 clés étrangères comme clé pour identifier les prêts?

Non, car une personne doit avoir le possibilité d'emprunter un même matériel plusieurs fois de suite à des périodes différentes.
Il faut donc une unicité sur matériel avec exclusion des périodes (non recouvrement). C'est une contrainte généralement difficile à exprimer qui nécessite un déclencheur.

Par exemple avec MS SQL Server, le modèle serait le suivant :


Les clients et le matériel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE CLIENT  (client_id    INT NOT NULL PRIMARY KEY,
                      nom          CHAR(32),
                      prenom       VARCHAR(25),
                      adresse      VARCHAR(100),
                      cp           CHAR(8),
                      ville        CHAR(32),
                      tel          CHAR(20));
 
CREATE TABLE MATERIEL (materiel_id INT NOT NULL PRIMARY KEY,
                       modele      VARCHAR(32),
                       marque      VARCHAR(32));

La table de prets :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE PRET     (pret_id     INT NOT NULL PRIMARY KEY,
                       date_debut  date NOT NULL,
                       date_fin    date,
                       client_id   INT NOT NULL FOREIGN KEY REFERENCES CLIENT  (client_id), 
                       materiel_id INT NOT NULL FOREIGN KEY REFERENCES MATERIEL (materiel_id),
                       CONSTRAINT CK_PRET_DATES CHECK ((date_fin >= date_debut) OR date_fin IS NULL));

Notez les contraintes FK et CHECK...

La contrainte de non recouvrement de périodes, sous forme de déclencheur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER E_IU_PRET
ON PRET
FOR INSERT, UPDATE
AS
   IF EXISTS(SELECT *
             FROM   PRET AS P
             WHERE  EXISTS(SELECT *
                           FROM   inserted AS i
                           WHERE  P.materiel_id = i.materiel_id
                             AND (i.date_debut BETWEEN P.date_debut AND P.date_fin OR 
                                  i.date_fin   BETWEEN P.date_debut AND P.date_fin)))
   ROLLBACK;

Une vue pour savoir la durée d'emprunt :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE VIEW V_PRET
AS
SELECT *, DATEDIFF(day, date_debut, date_fin) AS DUREE_JOUR
FROM   PRET;

Et une autre pour savoir le matériel disponible au jour le jour :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE VIEW V_MATERIEL_DISPONIBLE
AS
SELECT M.*
FROM   MATERIEL AS M 
WHERE  NOT EXISTS(SELECT *
                  FROM   PRET AS P
                  WHERE  M.materiel_id = P.materiel_id
                    AND  CURRENT_DATE BETWEEN date_debut AND date_fin);

A adapter !

A +

[melwin]

Bonjour et merci beaucoup pour la réponse.

[melwin]

Je ferme la discussion en la mettant en résolu. Après quelques recherches, je pense que je vais utiliser PostgreSQL. Je poserai des questions à propos TRIGGER sur le forum de PostgreSQL.