Mongodb schema to building an appointment booking system

user_4508759 0
Viewed 3258

We have two different approaches for an appointment scheduling system, using mongodb.

1st approach:

appointments:
    {
        resourceId: "string",
        resourceType: "doc"/"nut"...,
        userId: "string",
        userName: "string",
        startDate: "2020-05-18T16:00:00Z",
        endDate: 2020-05-18T17:00:00Z
        title: "string",
        description: "string",
        type: "string"/"off"
    },
    {
        resourceId: "string",
        resourceType: "doc"/"nut"...,
        userId: "string",
        userName: "string",
        startDate: "2020-05-21T12:00:00Z",
        endDate: 2020-05-21T12:30:00Z,
        title: "string",
        description: "string",
        type: "string"/"off"
    },
    ...

resources:
    {
        resourceId: "string",
        resourceName: "string"
        resourceType: "doc"/"nut"/"room",
        autoApprove: true/false,
        constantDaysOff: [sunnday]
    },
    {
        resourceId: "string",
        resourceName: "string"
        resourceType: "doc"/"nut"/"room",
        autoApprove: true/false,
        constantDaysOff: [sunnday]
    },
    {
        resourceId: "string",
        resourceName: "string"
        resourceType: "doc"/"nut"/"room",
        autoApprove: true/false,
        constantDaysOff: [sunnday]
    }

Here appointments and resources are different collections, with sample documents in each collection.

2nd approach:

resources:
    {
        resourceId: "string",
        resourceName: "string",
        resourceType: "doc"/"nut"...,
        constantDaysOff: [sunday],
        2020-05-21: [
            {
                startDate: "2020-05-21T12:00:00Z",
                endDate: 2020-05-21T12:30:00Z,
                userId: "string",
                userName: "string",
                title: "string",
                description: "string",
                type: "string"/"off"
            }, 
            {
                startDate: "2020-05-21T14:00:00Z",
                endDate: 2020-05-21T14:30:00Z,
                userId: "string",
                userName: "string",
                title: "string",
                description: "string",
                type: "string"/"off"
            }
        ],
        2020-05-22: [
            {
                startDate: "2020-05-22T12:00:00Z",
                endDate: 2020-05-22T12:30:00Z,
                userId: "string",
                userName: "string",
                title: "string",
                description: "string",
                type: "string"/"off"
            }, 
            {
                startDate: "2020-05-22T14:00:00Z",
                endDate: 2020-05-22T14:30:00Z,
                userId: "string",
                userName: "string",
                title: "string",
                description: "string",
                type: "string"/"off"
            }
        ]
        ...
    }

Here we only have one collection, and appointment dates are keys in the collection. Each date key would contain multiple json objects representing different appointments on the same day.

NOTE:

There are no appointments longer than 1 day, the reason we have startDate and endDate is to calculate the length of the appointment, its start and end time.

We need to be able to perform queries, most efficiently, along the lines of:

  • get all appointments for a specific resource id
  • get appointments for a resource between two different dates
  • get appointments for a user between different dates
  • cancel/remove appointments

A resource could be anything, for example a doctor, coach, room...

So my question is which one would be more efficient/feasible when it comes to mongodb queries?

mongodb scheduler nosql scheduling database
1 Answers
  1. most articles said not to use start-Date and end-Date, but Date and start/end-Time (not even in Date format) - you mostly select (query) by day/days, then you will handle time

Time is more flexible, especially when you need some time-step between appointments. Also, you can easily edit the time long without changing the date or change the date without changing the time.

  1. IMHO: according to the previous point, it's much better to use your first variant and the second one I dislike.

In the second variant, it will be hard to change the date. You will need to handle the whole array, upsert by date, and then upsert the whole Set of those.

user_55715810
Related