1

It is trivial to create a Constraint on uniqueness for an integer value, but I am wondering if it is possible to create a constraint on overlap/intersection of a range of values. For example, my schema requirement from a business level is to only allow one Person to reserve one Resource for one particular Time on a particular day. They may wish to claim the resource from 9 AM Jan 2, 2016 until 10:15 AM on Jan 3, 2016, so let's assume the resolution of resource-reservation is 1 minute. The reservation claim could be a tuple (row) in a table where the time (converted to an integer perhaps), plus a resource id (foreign key) must be unique. The third column in this reservation table would be a foreign key to refer to the Person.

The problem with such a schema is that if someone wants to reserve a resource for a week, at one minute resolution, they have to create 1440 rows in the table per day, so that's 10K rows for ONE reservation.

Does SQL Server 2014 (or SQL Server 2016) have any built in way to express that a particular range of values does not overlap any other range entry, in a way that would let me specify the beginning and ending of the reservations as a BeginDateTime/EndDateTime column in a row, and check that the range plus the resource ID form a unique non-overlapping entity?

Until now I have always modelled these constraints in the application layer (above the database) but I think that the power of relational integrity would be useful for preventing people from inserting and updating the reservations in ways that create conflicts.

It would be possible to do something similar by having triggers check for the database values being in an inconsistent state, but I'd rather have the attempt to write an overlapping value blow up, and not commit.

Paul White
  • 83,961
  • 28
  • 402
  • 634
Warren P
  • 1,087
  • 2
  • 14
  • 24
  • 3
    That's logic similar to Slowly Changing Dimensions usually done in Data Warehouses, ETL tools should support that. In Standard SQL 2011 there's a new Temporal Tables feature, which is supported at least by DB2 and Teradata, SQL Server 2016 implements it partially: http://sqlmag.com/sql-server/first-look-system-versioned-temporal-tables-part-1-creating-tables-and-modifying-data Before you might have a look at Alex Kuznetsov's implementation: https://www.simple-talk.com/content/article.aspx?article=1191 – dnoeth Feb 02 '16 at 18:27
  • 2
    @SteveMangiameli This type of constraints are avaiable in Postrges as (EXCLUDE) constraints, a generalization of UNIQUE constraints. So it's not that difficult to implement at the database level. The good reasons that Microsoft hasn't done something sinmilar is probably that it won't sell many more licences ;) – ypercubeᵀᴹ Feb 02 '16 at 18:31
  • If I understood your question correctly, the answer is: Yes. As @dnoeth mentioned, have a look at Modifying Contiguous Time Periods in a History Table by Alex Kuznetsov and Contiguous Time Periods by Joe Celko. – Vladimir Baranov Feb 03 '16 at 03:02

0 Answers0