3

I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an EndDate column. I now need to add another dimension of start and end dates, meaning that the relationship can be viewed with two different date "lenses" (queried with two dates, @Date1 and @Date2), so the schema will look something like this:

MyJoinTable:

| Id | Entity1Id | Entity2Id | StartDate1 |  EndDate1  | StartDate2 |  EndDate2  |
|----|-----------|-----------|------------|------------|------------|------------|
|  1 | A         | B         | 1753-01-01 | 2018-09-01 | 1753-01-01 | 2025-01-01 |
|  2 | A         | B         | 2018-09-01 | 2018-10-01 | 1753-01-01 | 2018-11-01 |
|  3 | A         | C         | 2018-09-01 | 2018-10-01 | 2018-11-01 | 2025-01-01 |
|  4 | A         | B         | 2018-10-01 | 2025-01-01 | 1753-01-01 | 2018-11-01 |
|  5 | A         | D         | 2018-10-01 | 2025-10-01 | 2018-11-01 | 2025-01-01 |

The queries will mostly be joins to this table, for example:

SELECT e1.Field, e2.Field
FROM Entity1 e1
INNER JOIN MyJoinTable jt ON jt.Entity1Id = e1.Id
    AND StartDate1 <= @Date1 AND EndDate1 > @Date1
    AND StartDate2 <= @Date2 and EndDate2 > @Date2
INNER JOIN Entity2 e2 ON e2.Id = jt.Entity2Id

My questions are:

  1. What's the best way to index this join table?
    • Index on Entity1Id
    • Index on Entity2Id
    • Composite Index on all four date columns? (StartDate1, EndDate1, StartDate2, EndDate2)
  2. What's the best way to constrain the DB so that I only get one relationship row returned for any @Date1, @Date2 combination?
  3. Do you have any recommendations on a better data model?
  • 3
    When they add a third and fourth lens this is going to get really ugly, really fast. – Aaron Bertrand Oct 18 '18 at 17:09
  • 2
    Since the query is trying to relate points - the (@Date1, @Date2) "point" - with rectangles - the (StartDate1, EndDate1) - (StartDate2,EndDate2) "rectangle" - in a 2D (time) space, you need an index for spatial queries, like the ones mentioned here: docs.microsoft.com/en-us/sql/t-sql/statements/… Problem is these specialized indexes exist only for spatial (geometry) datatypes and not for datetime-spatial types (there is not even a type like that in SQL Server). – ypercubeᵀᴹ Oct 18 '18 at 19:09
  • To achieve 2 you could only have StartDate EndDate with a 1 day gap. – paparazzo Oct 18 '18 at 19:53
  • @ypercubeᵀᴹ That's a neat insight. Do you know of anyone who has mapped non-spatial data into a spatial plane, stored it in SQL Server geometry datatypes, and used a spatial index to query it? I'm not considering it for this particular problem, but that would be a fun blog post/case study to read. – Seafish Oct 18 '18 at 20:50
  • Spatial works with numbers. Dates can be turned into numbers. QED. OK, the numbers are stored as DOUBLEs, but that does not matter. And don't get into spherical coordinates. – Rick James May 26 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.