Schema Design to find conflict

Working with Data
,
#1

Hi everyone, I am new to MongoDB.
I have a use case for which I have to write a query.
I am working on a project that has Java Spring boot in the backend connected to the MongoDB database.

UseCase:
In a hospital, we have multiple doctors.
Each doctor can have 3 types of appointments.

  1. Once on a particular date and time slot.
  2. Every day between 2 distinct dates and on the same time slot.
  3. Specific days of every week between 2 distinct dates and on the same time slot.

Here are the sample documents for all 3 use cases: https://json-parser.com/83cc64fe.json

We will have a portal to book an appointment.
Now we have a requirement that if a patient tries to book an appointment with a doctor with whom he/she already has a current appointment at the same time/overlapping time frame, then we need to respond to him by telling him he/she already has an existing appointment. Appointments with the same doctor at different time frames shouldn’t be blocked from booking.

I am thinking to write a query something like this: (I have some SQL experience but am a beginner in MongoDB, so not sure how to translate the below query to MongoDB Query)

SELECT * FROM appointments
WHERE
(
drName="Curr_requested_drNames"
AND
pName="Curr__requested_pName"
AND
("Curr_requested_startDate" <= appointmentSchedule.endDate && "Curr_requested_endDate" >= appointmentSchedule.startDate)
AND
("Curr_requested_startTime" <= appointmentSchedule.endTime && "Curr_requested_endTime" >= appointmentSchedule.startTime)
AND
CONTAINS (appointmentSchedule.days, "Curr_requested_days") 
);

My doubts:

  1. In my query I am using multiple AND operations, will that be an anti-pattern query?
  2. Any thoughts on improving the query. Wanted to make just one DB call to get conflicting appointments.
  3. Any thoughts on how to translate this SQL-like query to a MongoDB query.
#2

You have several options.

  1. You could implement a schema for the appointments collection like the following
{
  // ...
  doctor_id: SOME_ID,
  year: 2021,
  month: 12,
  day: 2,
  hour_slot: 12
 // ...
}

Then implement an unique index:

db.appointments.createIndex( { doctor_id: 1, year: 1, month: 1, day: 1, hour_slot: 1 }, { unique: true } )

So any invalid entry would trigger an unique index error

The benefit would be that since it is indexed, any query to check for available slots would also be covered.

#3
  1. You can query the database to check if an appointment exists before submitting a new appointment.
    The query would be similar to case 1 though, so I suggest you to try implementing option 1.
#4

Regarding your doubts,

  1. Not at all, if you have appropriated indexes in place there should be no problems using complex queries.
  2. Use indexes. I recommend you use compound index that cover the fields you will use to query. That way you will get the maximum performance possible.
  3. The query doesn’t seem that complicated, it will be a nice opportunity to learn the mongodb query language. If you need to implement more complex queries though, I recommend giving the aggregation API a try.

Don’t hesitate in contacting me if you need more help with this issue. :wink: