create table - SQL Interview: Prevent overlapping date range -

say there appointment_booking table list of managers (or hrs) startdatetime , enddatetime, how 1 design table such doesn't accept next entry overlaps same manager if he/she has appointment other person.

if manager: has appointment 2016-01-01 11:00 2016-01-01 14:00 employee-1 if employee-2 (or someother employee) tries book appointment 20-16-01-01 13:00 16:00 shouldn't allow.

note: designing table, triggers/procedures isn't encouraged.

instead of inserting ranges, insert slices of time. make slices wide want, pretend can book manager 30 minutes @ time. book 11:30 12:00, you'd insert row time value @ 11:30. book 11:30 12:30, you'd insert 2 rows, 1 @ 11:30, other @ 12:00. can use primary key constraint or unique constraint prevent on booking.

create table appointment_booking (     manager char not null,     startslice datetime,     visiting_employee varchar2(255),     primary key (manager, startslice) ) 

i know doesn't fit premise of table start , end time, if have control on table structure, work.