Search code examples
datetimegoogle-app-enginedatabase-designgoogle-cloud-datastorequery-optimization

Storing and querying time-based data for scheduling

I’ve done a lot of searching but I feel like I could do with some advice. I’m in the early stages of developing an app that will allow teams of people find a time that they can meet another team of people online. Each team may be up to 6 people but only four people are required. Each person in a team will specify when they are available and the app needs to find compatible dates/times with another team. It’s not always known which two teams need a compatible time, the app needs to be able to find a suitable times for one team and up to 20 other teams.

I’m using Google App Engine and Node.js with Datastore for storing user details and preferences. I'm happy to use Datastore for the time-based aspect but equally happy to explore other options.

My initial thoughts are to store time increments for each users' availability for each hour of the day for up to 2 weeks in the future. e.g

{userId: '123456',
"2021-11-14T12:00:00+0000": true,
"2021-11-14T13:00:00+0000": false,
"2021-11-14T14:00:00+0000": true,
etc...
}

Then, periodically and each time a user changes their availability, each time slot should be evaluated to find and store the team's availability.

When a member of a team (Team A) wishes to find a time to meet another team, they will look to find teams that are available at the same times as them.

My thoughts here are less clear. Should the app pull a list of Team A’s available times, then search for the matching times and return a list of teams for each time slot? Should it search for each possible team and return a list of time slots for each team?

Am I approaching this wrong? Should each entity be a single time slot with the available teams?
e.g

{timeSlot: "2021-11-14T12:00:00+0000",
teams: [664466,345433,543465,443456]}
{timeSlot: "2021-11-14T13:00:00+0000",
teams: [543465,443456]} 
etc...
}

Is there a standard approach for this kind of task?

Thanks in advance for any advice!

Solution

  • First, I would not put all time slots for a user in a single entity (record). This makes expiry difficult and the entities slow and unwieldy - especially in the datastore viewer.

    Second, you might want to store only available times in the database. And maybe denormalize the data to save time in processing. Call this FreeUsers:

    {userId: '123456', time: "2021-11-14T12", teamIds: ['31245', '007']}
{userId: '123456', time: "2021-11-14T13", teamIds: ['31245', '007']}

    You can use cloud tasks to update the teamIds in the records whenever a user changes his team memberships. That would be filter(teamIds, '=', '31245') and the like.

    I would also write one entity per free team/timeslot. Call this FreeTeams:

    {teamId: '31245', time: "2021-11-14T12"}
{teamId: '007', time: "2021-11-14T12"}
{teamId: '31245', time: "2021-11-14T13"}
{teamId: '007', time: "2021-11-14T13"}

    This makes concurrent updates and expiry more robust. So when a user changes a timeslot you can (via cloud task) iterate over all the teams of a user, calculate if everybody in the team is free and write or delete something like {teamId: '007', time: "2021-11-14T13"}.

    So, when a user wants to find available teams at time x you just have to query FreeTeams for the timestamp x. Eg filter(time, '=', '2021-11-14T13')