0
 CREATE TABLE HotelStays
 (
     roomNum INTEGER NOT NULL,
     arrDate DATE NOT NULL,
     depDate DATE NOT NULL,
     guestName CHAR(30) NOT NULL,
     PRIMARY KEY (roomNum, arrDate)
);

ISSUE: a new entry (for a new guest) could be put in for a room number, even before the existing guest has checked out:

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2919890
  • 29
  • 1
  • 1
    *What* database? Do you have a **constraint** that would prevent a reservation for a room that's already occupied? – marc_s Feb 21 '16 at 21:58
  • 1
    Stopping overlaps is usually only enabled by the use of triggers. Post the type of database (looks a bit like SQL Server) and maybe we can help. How is the data currently added, and does it have the capability to manage errors thrown by the databsae – Nick.Mc Feb 21 '16 at 23:46
  • `(roomNum, depDate)` is also a candidatae key, ie UNIQUE NOT NULL here. – philipxy Feb 22 '16 at 09:46
  • Re ISSUE: Are you trying to say that you want to know how to enforce the constraint that room bookings don't overlap improperly? – philipxy Feb 22 '16 at 09:49

1 Answers1

-1

If guests must check out before some time of day and check in after that time then it is ok to have a booking end on the same day as another one begins. That is why there is a checkout time and checkin time.

See this question & its answers for the kind of query that would check whether there would be a conflict in bookings. Ie whether two arrDate-to-depDate intervals overlap inappropriately. Ie whether they overlap by more than one day. You can query for conflicts in an trigger when you (try to) add a new booking.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83