Firebase database modeling in a appointment app

0
votes

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

enter image description here

what I got so far

enter image description here

The therapist agenda

enter image description here

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!

1
firebasefluttergoogle-cloud-firestore
Are you querying your appointments collection by the date in the document ? eg: .document(your_date) ? , is that collection the problem of your question ? - Gastón Saillén
Hi @GastónSaillén! Yes, i'm querying by the date through a isEqual query and using the document id which is in this case the date. - Gunzf
I suggest that you consider using a Could Functions to drive some query logic server-side. This does four things for you: 1) gives you the ability to run multiple queries in a single call, 2) allows the use of more sophisticated logic, 3) using the Admin SKD will bypass security rules, and 4) you can sanitise the output so that you don't leak sensitive data. - user10264162
Hi @Mike this would be indeed a much cleaner and efficient way to do this, although I'm not too familiar with CF or even how I could actually make it work with my project structure. If there's no other option I'll definitely consider spending a few days studying about it to make it work. Thank you for your suggestion and for helping me out! - Gunzf

1 Answers

1
votes

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