Firebase database modeling in a appointment app

Question

This is my first question ever on SO and I just want you to know that this community have been helping me for quite some time, and I really got this far thanks to all of you. So thank you. 😀

Now, let me tell you a bit about the question:

I have been scratching my head for days trying to figure this database model for a quite complex (for me, at least) appointment app.

Patients choose a day and it's respective hour slot (15 hourly slots - from 7AM to 9PM) to finish booking a appointment with a therapist. The problem is: I don't want to show unavailable slots so I must do some real-time querying everytime a date is picked. I found a way to do this by performing a isEqual query to check if the picked date existed on the therapists agenda collection. If false, the date is available.

The thing is: each therapist has their own subcollection called Agenda but I also need to check if the same date is available in a root collection called Appointments, which is also used by other therapists. This Appointments collection is related to the rooms available for booking. There are 3 rooms available and any room can be automatically booked if it's available. It's a quite complex operation so this is driving me nuts.

Root collection

what I got so far

The therapist agenda

I'm pretty sure I lack a few logical skills to solve this and the answer is probably right in front of my face but I'm failing to see it.

Could anyone please shed me a light into solving this puzzle? Is it possible to achieve all of this in one query function (or performing multiple queries in the same function)? Is there a better way or database structure to do this?

Thanks again!

Answer 1

I would suggest that you store the documents with a random ID and store inside of them a new field called appointment_date which will hold the date, after that, you can query the whole appointment collection with a query like whereEqualTo("appointment_date", yourDate), that query will just fetch the appointments of any specialist.

Also you can store an aditional field inside the appointments documents called specialist_id in which will help you also to get all the appointments for each specialist with a query like

FirebaseFirestore.getInstance().collection("appointments").whereEqualTo("appointment_date",your_date).whereEqualTo("speciallist_id",your_speccialist_id)

This is a kotlin code example, but, with this structure you have more flexibility to query for dates in the agenda, and also query for any speciallist dates

Then, you don't need a subcollection inside your specialist called Agenda, you can just do the query above and you can get each specialist appointment

Check: https://firebase.google.com/docs/firestore/query-data/queries