SQL Interview: Prevent overlapping date range

Question

Say there is an appointment_booking table for a list of Managers (or HRs) with startDatetime and endDatetime, then how do one design the table carefully such that it doesn't accept next entry that overlaps for same manager if he/she has appointment with some other person.

If Manager: A has a appointment from 2016-01-01 11:00 to 2016-01-01 14:00 with Employee-1 then if Employee-2 (or someother employee) tries to book an appointment from 20-16-01-01 13:00 to 16:00 then it shouldn't allow.

Note: It is about designing the table, so triggers/procedures isn't encouraged.

Answer 1

Instead of inserting ranges, you could insert slices of time. You could make the slices as wide as you want, but pretend you can book a manager for 30 minutes at a time. To book from 11:30 to 12:00, you'd insert a row with the time value at 11:30. To book from 11:30 to 12:30, you'd insert two rows, one at 11:30, the other at 12:00. Then you can just use a primary key constraint or unique constraint to prevent over booking.

create table appointment_booking (
    manager char not null,
    startSlice DateTime,
    visiting_employee varchar2(255),
    primary key (manager, startSlice)
)

I know this doesn't exactly fit your premise of the table with a start and end time, but if you have control over the table structure, this would work.

Answer 2

CHECK CONSTRAINT + FUNCTION (this is as close as I can get to a DDL answer)

You could create a scalar function -- "SCHEDULE_OPENING_EXISTS()" that takes begin, end, employeeID as inputs, and outputs true or false.

Then you could create a check constraint on the table

CREATE TABLE...
    WITH CHECK ADD CONSTRAINT OPENING_EXISTS
    CHECK (SCHEDULE_OPENING_EXISTS(begin, end, employeeID)) = 'True')

TRIGGERS:

I try to avoid triggers where I can. They're not evil per se -- but they do add a new layer of complexity to your application. If you can't avoid it, you'll need an INSTEAD OF INSERT, and also an INSTEAD OF UPDATE (presumably). Technet Reference Here: https://technet.microsoft.com/en-us/library/ms179288%28v=sql.105%29.aspx

Keep in mind, if you reject an insert/update attempt, whether or how you need to communicate that back to the user.

STORED PROCEDURES / USER INTERFACE:

Would a Stored Procedure work for your situation? Sample scenario:

1. User Interface -- user needs to see the schedule of the person(s) they're scheduling an appointment with.

2. From the UI -- attempt an insert/update using a stored proc. Have it re-check (last-minute) the opening (return a failure if the opening no longer exists), and then conditionally insert/update if an opening still exists (return a success message).

If the proc returns a failure to the UI, handle that in the UI by re-querying the visible schedule of all parties, accompanied by an error message.

Answer 3

I think these types of questions are interesting because any time you are designing a database, it is important to know the requirements of the application that will be interacting with your database.

That being said, as long as the application can reference multiple tables, I think Chris Steele's answer is a great start that I will build upon...

I would want 2 tables. The first table divides a day into parts (slices), depending on the business needs of the organization. Each slice would be the primary key of this table. I personally would choose 15 minute slices that equates into 96 day-parts. Each day-part in this table would have a "block start" and a "block end" time that would referenced by the scheduling application when a user has selected an actual start time and an actual end time for the meeting. The application would need to apply logic such as two "OR" operators between 3 "AND" statements in order to see if a particular blockID will be inserted into your Appointments table:

• actual start >= block start AND actual start < block end
• actual end > block start AND actual end < block end
• actual start < block start AND actual end > block end

This slightly varies from Chris Steele's answer in that it uses two tables. The actual time stamps can still be inserted into your applications table, but logic is only applied to them when comparing against the TimeBlocks table. In my Appointments table, I prefer breaking dates into constituent parts for cross-platform analysis (our organization uses multiple RDBMS as well as SAS for analytics):

CREATE TABLE TimeBlocks (
          blockID Number(X) NOT NULL,
          blockStart DateTime NOT NULL,
          blockEnd DateTime NOT NULL,
     primary key (blockID)
);


CREATE TABLE Appointments (
          mgrID INT NOT NULL,
          yr INT NOT NULL,
          mnth INT NOT NULL,
          day INT NOT NULL,
          blockID INT NOT NULL,
          ApptStart DateTime NOT NULL,
          ApptEnd DateTime NOT NULL
          empID INT NOT NULL,
     primary key (mgrID, yr, mnth, day, blockID),
     CONSTRAINT timecheck
          check (ApptStart < ApptEnd)
);