A good way to deal with date and time spans?

Question

For my web app i need to handle access requests. Requests are entered for a monday-sunday schedule specifying hours for each day. This week schedule can repeat up to 52 times. A user can have multiple schedules during a week (mon 8-9 and mon 10-11) There are the following requirements:

1. Searchable/filterable
2. Detect overlapping requests

I want the database to handle as much of the lifting as possible. Right now the only design I can think of is storing each day's access as a separate record. Doing this I would pull all accesses for a user and loop to determine if the new request overlaps. This requires code or a stored procedure.

Does anyone have a better database model idea or a clean way to deal with overlaps in code?

Answer 1

If you store each access request in a table with fields with start_time and end_time, then you could use the database's BETWEEN functionality to determine if a particular access would overlap with one already in the database.

As an example, let's say that someone had completed an access request for Monday from 9:00AM to 12:00PM.

Then someone else comes and tries to make an access request for Monday from 11:00AM to 3:00PM. To determine if this would conflict with something else, you'd need to look for the following possible conditions:

• start_time < Monday 11:00AM && end_time > Monday 11:00AM
• start_time BETWEEN Monday 11:00AM & Monday 3:00PM
• start_time == Monday 11:00AM || end_time == Monday 3:00PM

These can bet translated down to a SQL query which would prevent having to load and iterate over records in the application. As a bonus, you can use transactions to guard against race conditions.

Answer 2

a datastructure that is impossible to contain overlaps would be one where each record contains the duration of the access and a reference to the next record and offset of that record. As if it was a linked list where each item and each link has a length.

This structure is probably too complicated to achieve what you want. I think Collins answer is a lot simpler and therefore better.