Indexing on Dates, Conflict-Checking for Appointments

Help
,
3

I think you should be fine by just checking with Exists and Match whether such a calendar item already exists or not.

Sidenote on consistency

Just to be clear, since there is a discussion in slack that was slightly inaccurate where the question was: “is this safe in FaunaDB”, in some (or quite a few) databases the approach I’m going to explain exposes you to a phenomenon called: Phantom reads. FaunaDB protects you against these anomalies as it has one of the highest consistency guarantees (and went through the trouble to let someone external who is very well known to break databases for a living (cool job right? :slight_smile: ) validate it to make sure the approach is sound)

Sidenote on uniqueness

For the users that are looking for something similar but are not dealing with overlaps. We can in many cases use indexes with the unique boolean set to true to make sure such conflicts do not happen. Of course this is a totally different case since you are talking about ‘overlaps’. In that case, we can write a simple match function and check with FQL logic whether it returns something.

The approach

Imagine you have appointment documents with a start and end date and ‘calendar guests’ which would just be the users who would join the appointment. In this case that’s an array, that might be cumbersome to adapt. It could also be a pointer to an external collection (often a good idea for many to many collections) that keeps relations from. user <-> appointment. Let’s start simple though.

{
   data: {
      guests: [ 
            Ref(Collection("users"), "268431417898172934"), 
            Ref(Collection("users"), "268431417898591012") 
      ],
      start_date: Time("2020-06-15T19:00:00.000000Z")
      end_date:  Time("2020-06-15T21:30:00.000000Z")
    }
}

To check whether they overlap we’ll have to know what overlaps means… that’s beyond the FaunaDB scope, so I’ll point to: https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/325964#325964

With a range check in FaunaDB we can check whether one attribute lies in between two values or above or equal/beneath or equal one value (inclusive) but in this case we are doing two checks:

  • whether the new start date <= another appointment date
  • AND whether the new end date >= another appointment date

You can not do this check (as far as I know) immediately in one Range query with one index, this stackoverflow post should explain why.

I think we explained extensively there how to do range queries on multiple attributes and as mentioned there, there are multiple options so, it’ll take me a bit too much time to apply it here so I’ll keep it short…

In essence you could go the Range + Filter route Option2 in that post which only requires one index or you could take the other approach, write two indexes, one on with start_date and ref in values and one with end_date and ref in values take out the reference with join and intersect both Option 3 which requires two indexes and another one to drop the ref although your could that with map as well. Whatever you choose there, imagine that is encapsulated by ‘SearchForOtherAppointments’ then you could write the following logic:

If(
    Exists(SearchForOtherAppointments(..)),
    Abort("Appointment already exists"), 
    Create(...)
)
1 Like