In a table with room number, start time integer, end time integer, and person id, how to restrict ti-CodePudding

I have a SQL table for room bookings in which I have columns:

Room_num,
Person_id,
start int,
end int

I want to add a restriction/constraint in the table such that a particular room cannot be booked by multiple person_id(s) for conflicting start/end durations.

I am going in the direction of having start and end times as integers where they represent the start of an hour (1 being 1 am and 13 being 1 pm). I am not able to figure out how to add such a restriction.

CodePudding user response：

You can have a check against the table before inserting the data. additionally, as @DaleK mentioned, it is better to go with time datatype, as you can leverage time functions.

if not exists (SELECT * FROM Table where room_num = @room_num and end_time > @start_time and start_time < @end_time)
BEGIN

     INSERT INTO Table(personid, room_num, start_time, end_time)
     values(@personid, @room_num, @start_time, @end_time)

END
ELSE
BEGIN
   throw 51000, 'You are already having room booking conflicting with current booking', 1;
END

CodePudding user response：

If you always book whole hours you can just indicate the start. If someone wants 2 hours they book 2 slots. We then use Room Number Date Hour as primary key which automatically blocks all duplicate bookings. This could be modified to use smaller block, half-hour, 10 minutes etc. by modifying the datatype or slotHour.

create table bookings (
  Room_num int not null,
  Person_id int not null,
  slotDate date not null,
  slotHour int not null,
  constraint pk_slot primary key (Room_num,slotDate, slotHour)
  );

insert into bookings values (1, 1,'2022-02-25',10);
insert into bookings values (2, 1,'2022-02-25',10);
insert into bookings values (1, 1,'2022-02-25',11);

select * from bookings;

room_num | person_id | slotdate   | slothour
-------: | --------: | :--------- | -------:
       1 |         1 | 2022-02-25 |       10
       2 |         1 | 2022-02-25 |       10
       1 |         1 | 2022-02-25 |       11

insert into bookings values (1, 1,'2022-02-25',10);

ERROR:  duplicate key value violates unique constraint "pk_slot"

DETAIL: Key (room_num, slotdate, slothour)=(1, 2022-02-25, 10) already exists.

db<>fiddle here