CODEWITHSUNDEEP
androidfirebasegoogle-cloud-firestore
Remon Shehatta
Remon Shehatta

Reputation: 1480

how to structure firebase database to get results like relational databases

I am using firebase Cloud Firestore for my Android and iOS apps. I am stuck with how to structure my database.

Basically I have an "organization" collection that contains many users, and for every user, I want to save attendance time and leave time for every day.

The problem is I want to generate reports that allow me to get every day-attendance for each user, single-day attendance for all users, and all days attendance for all users.

so I tried this: inside the user I would have an "attendance" collection then each document is Unix timestamp of that day (to make sure that it's unique). then save fields like attend_time and leave_time...etc. the path is like that "/organization/android/users/3PRs42gRFzZQhLKcUhpf4wPMRV43/attendance/1590271200"

then I needed to get attendance for a single day for all users, so I did this: now I have another path "/organization/android/attendance" and inside the attendance, I store the Unix timestamp of the day, then the user ID then his attendance. and now I am saving attendance twice.

but I still can't get attendance for all days for all users! this would be easy in Relational Database like SQL. any idea how to do it in firebase?

Upvotes: 1

Views: 1085

Answers (1)

Frank van Puffelen
Frank van Puffelen

Reputation: 600100

If you want to track attendance across all users, you're looking for a collection group query. This allows you to query documents from all collections named attendance.

Since a query in Firestore can only see data in the collection(s) it queries, you may have to duplicate some data from parent collections (your users and organizations) into each attendance document to allow the query. This type of data duplication is quite normal in NoSQL databases too.

Finally: if you need to perform many ad-hoc queries, you might want to consider using a database for those that is more suited to that use-case. For example, it is quite common to use Firestore to handle the direct-to-client interactions that require scaling to massive number of users, but then use BigQuery for the ad-hoc querying of that data. There is even a Firebase Extension that automatically exports to BigQuery to make this easier.

Upvotes: 2

Related Questions