How to properly store and query availability data

Question

I am building an app that allows people to mark when they are available/unavailable, and allows others to book appointments during that time. However, I am having trouble figuring out a good method to store and query the availability for when someone does a search.

For example:

Host A is available on Oct 1st, 3rd, and 8th from 7am - 4pm and on the 11th from 9am - 10am and 12pm - 6pm. They also have a (1 hour) appointment on Oct 1st at 12 pm
Host B is available on Oct 1st, 4th, 6th, and 8th from 9am - 8pm
User C is searching for any availability on Oct 6-8 from 4pm to 6pm, so should see only host B
User D is searching for any availability on Oct 9-12 from 9am to 1pm, so should see only host A
User E is searching for availability with Host A on October 1st, so should be able to make a 1 hour appointment at any from 7am to 11am, and from 1pm to 4pm

What is an efficient way to store and query the data?

Other notes:

I am using elasticsearch in conjunction with DynamoDB to store and query the data
Bookings can be made up to a year out

Honestly, this question is opinion based. Everyone will have their own answer based on their experience - there is no one answer. Have you tried any design yet? why dont your share it and discuss issues you are running into and improve from there. As of now, this question reads(to me) : "Design this for me". — Polynomial Proton, Sep 29 '19 at 16:55
I understand there could be an opinon element to this, however I honestly dont even know where to begin with a design like this, and there does not seem to be any standard way to structure and query dates (especially not recurring ones with one-offs). I figure there has to be some sort of standard method to do this, but I am currently at as loss as to what that could be. — Reid, Sep 29 '19 at 17:41
Elastic does have a [date datatype](https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html) — Polynomial Proton, Sep 29 '19 at 21:47

Val · Accepted Answer · 2019-10-03T06:58:03.357

I would clearly leverage the date_range data type (introduced here). You can create one document per host and per availability (or if you want more fine-grained data, one per hour of availability) of that host. Given your requirements above, it would look like this:

First create the index and mapping:

PUT hosts
{
  "mappings": {
    "properties": {
      "name": {
        "type": "keyword"
      },
      "availability": {
        "type": "date_range"
      }
    }
  }
}

Then add the availabilities of your hosts (I've decided to use the GMT timezone so the hours match 1:1 to the hours you've mentioned above):

PUT hosts/_doc/_bulk
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-01T07:00:00.000Z", "lte": "2019-10-01T12:00:00.000Z" }}
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-01T13:00:00.000Z", "lte": "2019-10-01T16:00:00.000Z" }}
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-03T07:00:00.000Z", "lte": "2019-10-03T16:00:00.000Z" }}
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-08T07:00:00.000Z", "lte": "2019-10-08T16:00:00.000Z" }}
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-11T09:00:00.000Z", "lte": "2019-10-11T10:00:00.000Z" }}
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-11T12:00:00.000Z", "lte": "2019-10-11T18:00:00.000Z" }}
{"index": {}}
{ "name": "B", "availability": { "gte": "2019-10-01T09:00:00.000Z", "lte": "2019-10-01T20:00:00.000Z" }}
{"index": {}}
{ "name": "B", "availability": { "gte": "2019-10-04T09:00:00.000Z", "lte": "2019-10-04T20:00:00.000Z" }}
{"index": {}}
{ "name": "B", "availability": { "gte": "2019-10-05T09:00:00.000Z", "lte": "2019-10-05T20:00:00.000Z" }}
{"index": {}}
{ "name": "B", "availability": { "gte": "2019-10-06T09:00:00.000Z", "lte": "2019-10-06T20:00:00.000Z" }}

Then querying is easy using the range query:

User C is searching for any availability on Oct 6-8 from 4pm to 6pm, so should see only host B

POST hosts/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "range": {
            "availability": {
              "gte": "2019-10-06T16:00:00.000Z",
              "lte": "2019-10-06T18:00:00.000Z",
              "relation": "contains"
            }
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-07T16:00:00.000Z",
              "lte": "2019-10-07T18:00:00.000Z",
              "relation": "contains"
            }
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-08T16:00:00.000Z",
              "lte": "2019-10-08T18:00:00.000Z",
              "relation": "contains"
            }
          }
        }
      ]
    }
  }
}

Result: only host B =>

  {
    "_index" : "hosts",
    "_type" : "_doc",
    "_id" : "KzOggG0BzetAdRPqckxN",
    "_score" : 1.0,
    "_source" : {
      "name" : "B",
      "availability" : {
        "gte" : "2019-10-06T09:00:00.000Z",
        "lte" : "2019-10-06T20:00:00.000Z"
      }
    }
  }

User D is searching for any availability on Oct 9-12 from 9am to 1pm, so should see only host A

POST hosts/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "range": {
            "availability": {
              "gte": "2019-10-09T09:00:00.000Z",
              "lte": "2019-10-09T13:00:00.000Z",
              "relation": "intersects"
            }
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-10T09:00:00.000Z",
              "lte": "2019-10-10T13:00:00.000Z",
              "relation": "intersects"
            }
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-11T09:00:00.000Z",
              "lte": "2019-10-11T13:00:00.000Z",
              "relation": "intersects"
            }
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-12T09:00:00.000Z",
              "lte": "2019-10-12T13:00:00.000Z",
              "relation": "intersects"
            }
          }
        }
      ]
    }
  }
}

Results: only host A =>

  {
    "_index" : "hosts",
    "_type" : "_doc",
    "_id" : "JzOggG0BzetAdRPqckxN",
    "_score" : 1.0,
    "_source" : {
      "name" : "A",
      "availability" : {
        "gte" : "2019-10-11T12:00:00.000Z",
        "lte" : "2019-10-11T18:00:00.000Z"
      }
    }
  },
  {
    "_index" : "hosts",
    "_type" : "_doc",
    "_id" : "JjOggG0BzetAdRPqckxN",
    "_score" : 1.0,
    "_source" : {
      "name" : "A",
      "availability" : {
        "gte" : "2019-10-11T09:00:00.000Z",
        "lte" : "2019-10-11T10:00:00.000Z"
      }
    }
  }

User E is searching for availability with Host A on October 1st, so should be able to make a 1 hour appointment at any from 7am to 11am, and from 1pm to 4pm

POST hosts/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "name": "A"
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-01",
              "lt": "2019-10-02",
              "relation": "intersects"
            }
          }
        }
      ]
    }
  }
}

Results: host A from 7 to 12 am and 1pm to 4pm =>

  {
    "_index" : "hosts",
    "_type" : "_doc",
    "_id" : "IjOggG0BzetAdRPqckxN",
    "_score" : 0.0,
    "_source" : {
      "name" : "A",
      "availability" : {
        "gte" : "2019-10-01T07:00:00.000Z",
        "lte" : "2019-10-01T12:00:00.000Z"
      }
    }
  },
  {
    "_index" : "hosts",
    "_type" : "_doc",
    "_id" : "IzOggG0BzetAdRPqckxN",
    "_score" : 0.0,
    "_source" : {
      "name" : "A",
      "availability" : {
        "gte" : "2019-10-01T13:00:00.000Z",
        "lte" : "2019-10-01T16:00:00.000Z"
      }
    }
  }

How to properly store and query availability data

1 Answers1