Modèlisation de location de chambre

**jean.dufour** · 08/08/2015, 22h14

salut

je dois modéliser un système de location de chambre

Un immeuble contient différent appartement qui contient 1 ou plusieurs chambre.

Un appartement peut donc contenir 1 ou plusieurs locataire (jamais plus de deux en fait).

Un locataire peut changer d'appartement.
Un locataire loue une seul chambre à la fois.

Je cherche aussi a pouvoir connaitre l'historique de location des locataires.

Immeuble contiendrait par exemple 2233 port royal
Appartement 1, 2, 3...
chambre a, b

Voici mon ébauche

Locataire
-------------
locataireId
nom
prénom

Immeuble
--------------
immeubleId
adresse

appartement
------------------
appartementId
numAppartement
chambreId

chambre
------------
chambreId
numChambre

location
------------
locataireId
locationId
dateEntree
dateSortie
chambreId

des suggestions?

**fsmrel** · 10/08/2015, 16h35

Bonjour Jean,

Il faudrait que vous utilisiez un outil de modélisation. Par exemple, avec MySQL Workbench (gratuit) :

En prime, l’outil permet la génération automatique du script SQL :



CREATE TABLE IMMEUBLE
(
        ImmeubleId           INT             NOT NULL
      , ImmeubleAdresse      VARCHAR(64)     NOT NULL
    , CONSTRAINT IMMEUBLE_PK PRIMARY KEY (ImmeubleId)
) ;

CREATE TABLE APPARTEMENT
(
        ImmeubleId           INT             NOT NULL
      , AppartementId        INT             NOT NULL
      , AppartementNumero    VARCHAR(5)      NOT NULL
    , CONSTRAINT APPARTEMENT_PK PRIMARY KEY (ImmeubleId, AppartementId)
    , CONSTRAINT APPARTEMENT_AK UNIQUE (ImmeubleId, AppartementNumero)    
    , CONSTRAINT APPARTEMENT_IMMEUBLE_FK FOREIGN KEY (ImmeubleId)
          REFERENCES IMMEUBLE (ImmeubleId)
          ON DELETE CASCADE
) ;

CREATE TABLE CHAMBRE
(
        ImmeubleId           INT             NOT NULL
      , AppartementId        INT             NOT NULL
      , ChambreId            INT             NOT NULL
      , ChambreNumero        VARCHAR(5)      NOT NULL
    , CONSTRAINT CHAMBRE_PK PRIMARY KEY (ImmeubleId, AppartementId, ChambreId)
    , CONSTRAINT CHAMBRE_AK UNIQUE (ImmeubleId, AppartementId, ChambreNumero)    
    , CONSTRAINT CHAMBRE_APPARTEMENT_FK FOREIGN KEY (ImmeubleId, AppartementId)
          REFERENCES APPARTEMENT (ImmeubleId, AppartementId)
          ON DELETE CASCADE
) ;

CREATE TABLE LOCATAIRE
(
        LocataireId          INT             NOT NULL
      , LocataireNom         VARCHAR(48)     NOT NULL
      , LocatairePrenom      VARCHAR(48)     NOT NULL      
    , CONSTRAINT LOCATAIRE_PK PRIMARY KEY (LocataireId)
) ;

CREATE TABLE LOCATION
(
        LocataireId          INT             NOT NULL
      , LocatationId         INT             NOT NULL        
      , ImmeubleId           INT             NOT NULL
      , AppartementId        INT             NOT NULL
      , ChambreId            INT             NOT NULL
      , LocationDateDebut    DATE            NOT NULL
      , LocationDateFin      DATE            NOT NULL
    , CONSTRAINT LOCATION_PK PRIMARY KEY (LocataireId, LocatationId)
    , CONSTRAINT LOCATION_AK UNIQUE (LocataireId, LocationDateDebut)
    , CONSTRAINT LOCATION_LOCATAIRE_FK FOREIGN KEY (LocataireId)
    REFERENCES LOCATAIRE (LocataireId)
    , CONSTRAINT LOCATION_CHAMBRE_FK FOREIGN KEY (ImmeubleId, AppartementId, ChambreId)
          REFERENCES CHAMBRE (ImmeubleId, AppartementId, ChambreId)
) ;

**jean.dufour** · 20/08/2015, 21h06

il reste juste à trouver une requête efficace pour trouver les chambres libres... ou bien d'avoir un peu de donnée en doublon pour faciliter la requête...

**fsmrel** · 21/08/2015, 17h49

Bonjour Jean,

Commençons par créer un soupçon de début d’amorce de jeu d’essai :


INSERT INTO IMMEUBLE (ImmeubleId, ImmeubleAdresse) VALUES
    (1, '3 rue du chien qui fume'), (2, '7 rue du poisson volant'), (3, '9 impasse de la république')
;

INSERT INTO APPARTEMENT (ImmeubleId, AppartementId, AppartementNumero) VALUES
    (1, 1, '51b'), (1, 2, '52a'), (1, 3, '57a')
  , (2, 1, 'app 1'), (2, 2, 'app 4'), (2, 3, 'app 9')
  , (3, 1, '11'), (3, 2, '14a'), (3, 3, '14b')
;

INSERT INTO CHAMBRE (ImmeubleId, AppartementId, ChambreId, ChambreNumero)  VALUES
    (1, 1, 1, 'ch 1'), (1, 1, 2, 'cuisine'), (1, 1, 3, 'wc')
  , (1, 2, 1, 'salon'), (1, 2, 2, 'fumoir'), (1, 2, 3, 'chambre 2')
  , (1, 3, 1, 'atelier'), (1, 3, 2, 'vestibule'), (1, 3, 3, 'chambre bleue')

  , (2, 1, 1, 'bureau'), (2, 1, 2, 'chambre verte'), (2, 1, 3, 'chambre noire')
  , (2, 2, 1, 'débarras'), (2, 2, 2, 'cave 1'), (2, 2, 3, 'cave 2')
  , (2, 3, 1, 'antichambre'), (2, 3, 2, 'grenier 1'), (2, 3, 3, 'grenier 2')

  , (3, 1, 1, 'corridor'), (3, 1, 2, 'salle à manger'), (3, 1, 3, 'chambre du fond')
  , (3, 2, 1, 'soupente'), (3, 2, 2, 'chambre noire'), (3, 2, 3, 'chambre jaune')
  , (3, 3, 1, 'douche'), (3, 3, 2, 'cuisine aménagée'), (3, 3, 3, 'chambre grise')
;


INSERT INTO LOCATAIRE (LocataireId, LocataireNom, LocatairePrenom) VALUES
    (1, 'Latour', 'Ti-Jean'), (2, '', 'Mac Pherson'), (3, '', 'Petit Pierre'), (4, '', 'Gros-Louis'), (5, '', 'Bozo'), (6, '', 'Joseph')
;

INSERT INTO LOCATION (LocataireId, LocatationId, ImmeubleId, AppartementId, ChambreId, LocationDateDebut, LocationDateFin)
    VALUES
        (1, 1, 1, 1, 1, '2014-01-01', '2014-12-31')
      , (1, 2, 1, 1, 2, '2015-01-01', '9999-12-31')
      , (2, 1, 1, 1, 2, '2015-01-01', '2015-09-01')
      , (3, 1, 2, 1, 1, '2015-01-01', '2015-10-31')      
;

Une requête (MySQL) sans (fioritures) pour lister les chambres libres :


SELECT x.ImmeubleId, x.AppartementId, x.ChambreId 
FROM CHAMBRE AS x 
WHERE  NOT EXISTS (SELECT '' 
                   FROM   LOCATION AS y 
                   WHERE  x.ImmeubleId = y.ImmeubleId 
                     AND  x.AppartementId = y.AppartementId
                     AND  x.ChambreId = y.ChambreId
                     AND  y.LocationDateFin > Now()
                  )
ORDER BY x.ImmeubleId, x.AppartementId, x.ChambreId
;

=>


ImmeubleId    AppartementId    ChambreId
         1                1           1
         1                1           3
         1                2           1
         1                2           2
         1                2           3
         1                3           1
         1                3           2
         1                3           3
         2                1           2
         2                1           3
         2                2           1
         2                2           2
         2                2           3
         2                3           1
         2                3           2
         2                3           3
         3                1           1
         3                1           2
         3                1           3
         3                2           1
         3                2           2
         3                2           3
         3                3           1
         3                3           2
         3                3           3

Une requête donnant un résultat plus parlant :


SELECT DISTINCT z.ImmeubleAdresse, y.AppartementNumero, x.ChambreNumero
FROM   CHAMBRE AS x, APPARTEMENT as y, IMMEUBLE as z 
WHERE  x.ImmeubleId = z.ImmeubleId
  AND  x.AppartementId = y.AppartementId
  AND  y.ImmeubleId = z.ImmeubleId
  AND  NOT EXISTS (SELECT '' 
                   FROM   LOCATION AS y 
                   WHERE  x.ImmeubleId = y.ImmeubleId 
                     AND  x.AppartementId = y.AppartementId
                     AND  x.ChambreId = y.ChambreId
                     AND  y.LocationDateFin > now()
                  )
ORDER BY z.ImmeubleAdresse, y.AppartementNumero, x.ChambreNumero 
;

=>


ImmeubleAdresse               AppartementNumero         ChambreNumero
3 rue du chien qui fume       51b                       ch 1
3 rue du chien qui fume       51b                       wc
3 rue du chien qui fume       52a                       chambre 2
3 rue du chien qui fume       52a                       fumoir
3 rue du chien qui fume       52a                       salon
3 rue du chien qui fume       57a                       atelier
3 rue du chien qui fume       57a                       chambre bleue
3 rue du chien qui fume       57a                       vestibule
7 rue du poisson volant       app 1                     chambre noire
7 rue du poisson volant       app 1                     chambre verte
7 rue du poisson volant       app 4                     cave 1
7 rue du poisson volant       app 4                     cave 2
7 rue du poisson volant       app 4                     débarras
7 rue du poisson volant       app 9                     antichambre
7 rue du poisson volant       app 9                     grenier 1
7 rue du poisson volant       app 9                     grenier 2
9 impasse de la république    11                        chambre du fond
9 impasse de la république    11                        corridor
9 impasse de la république    11                        salle à manger
9 impasse de la république    14a                       chambre jaune
9 impasse de la république    14a                       chambre noire
9 impasse de la république    14a                       soupente
9 impasse de la république    14b                       chambre grise
9 impasse de la république    14b                       cuisine aménagée
9 impasse de la république    14b                       douche

Mais, très important ! un travail non négligeable reste à faire quant à l’intégrité : actuellement, deux personnes peuvent louer la même chambre en même temps : il va falloir blinder ça...

Envoyé par jean.dufour

ou bien d'avoir un peu de donnée en doublon pour faciliter la requête...

=>

P.-S. Quel est votre SGBD ?

**jean.dufour** · 31/08/2015, 15h42

mysql

**fsmrel** · 02/09/2015, 03h16

Bonjour Jean,

Il faut déjà s’assurer que la date de fin d’une location est postérieure à sa date de début. En SQL, on déclare une contrainte dans le CREATE (ou ALTER) TABLE :


CREATE TABLE LOCATION
(
        LocataireId          INT             NOT NULL
      , LocationId           INT             NOT NULL        
      , ImmeubleId           INT             NOT NULL
      , AppartementId        INT             NOT NULL
      , ChambreId            INT             NOT NULL
      , LocationDateDebut    DATE            NOT NULL
      , LocationDateFin      DATE            NOT NULL
    , CONSTRAINT LOCATION_PK PRIMARY KEY (LocataireId, LocationId)
    , CONSTRAINT LOCATION_AK UNIQUE (LocataireId, LocationDateDebut)
    , CONSTRAINT LOCATION_LOCATAIRE_FK FOREIGN KEY (LocataireId)
    REFERENCES LOCATAIRE (LocataireId)
    , CONSTRAINT LOCATION_CHAMBRE_FK FOREIGN KEY (ImmeubleId, AppartementId, ChambreId)
          REFERENCES CHAMBRE (ImmeubleId, AppartementId, ChambreId)
    , CONSTRAINT LOCATION_CHAMBRE_CHK01 CHECK (LocationDateFin > LocationDateDebut)
) ;

Mais bien qu’il la détecte, MySQL ne gère pas la contrainte LOCATION_CHAMBRE_CHK01.

On doit donc pallier et se rabattre sur un trigger du genre :


DELIMITER GO

CREATE TRIGGER Location_Date_insert_tr BEFORE INSERT ON LOCATION 
FOR EACH ROW
    BEGIN
        IF new.LocationDateDebut >= new.LocationDateFin THEN
            SET @Erreur = CONCAT ('Dates incohérentes : ', new. ImmeubleId, ' - ', new.AppartementId, ' - ', new.Chambreid,  ' - ', new.LocationDateDebut,  ' - ', new.LocationDatefin) ;
            SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @Erreur ;
        END IF ;
    END 
GO
DELIMITER ;

Mais êtes vous familiarisé avec les triggers ?

En tout cas, un exercice intéressant consiste à programmer un trigger permettant de s’assurer que deux personnes ne pourront pas louer la même chambre en même temps.

Si cette programmation vous intéresse mais vous pose un problème, je vous la fournirai.

Modèlisation de location de chambre

Modélisation

Discussions similaires

Partager

Partager