Firebase Database Structure For Better Queries

Question

I am working on an app that helps the user search for their desired medication in nearby pharmacies then shows a list of the pharmacies that have the drug in stock with prices.

I come from a SQL background and have been having a hard time deciding how to structure Firestore databases for better queries, I think normally you would go through the list of pharmacies and their databases but is there a way to have a single database for all the drugs with maybe a field that has the primary keys of the pharmacies that have it in stock in Firestore?

It would help if you showed us a snippet of what you tried, and explain why it failed. You're asking us to write a tutorial instead of provide a focused answer. See "How to Ask". — the Tin Man
– the Tin Man, Commented Sep 28, 2021 at 20:23
New to stackoverflow, i didn't know the proper way to ask but i will keep that in mind from now on. Thanks! — Maged Faiz
– Maged Faiz, Commented Sep 29, 2021 at 21:10

Alex Mamo · Accepted Answer · 2021-09-21 06:48:27Z

2

There are two ways in which you can solve this. The first one would be to create a sub-collection under each pharmacy to hold all available drugs:

Firestore-root
  |
  --- pharmacies (collection)
        |
        --- $pharmacyId (document)
               |
               --- drugs (sub-collection)
                     |
                     --- $drugId
                           |
                           --- name: "Aspirin"
                           |
                           --- inStock: true

To get all pharmacies that have, for example, Aspirin in stock, a collection group query is needed. In Android, the query should look like this:

db.collectionGroup("drugs").whereEqualTo("name", "Aspirin").whereEqualTo("inStock", "true");

The second option that you have is to create a single top-level collection of drugs:

Firestore-root
  |
  --- drugs (collection)
        |
        --- $drugId (document)
              |
              --- name: "Aspirin"
              |
              --- inStock: true
              |
              --- pharmacyId: $pharmacyId

And create a simple query that looks like this:

db.collection("drugs").whereEqualTo("name", "Aspirin").whereEqualTo("inStock", "true");

So you can choose to work or one or the other according to the use-case of your app.

answered Sep 21, 2021 at 6:48

Alex Mamo

139k18 gold badges171 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Maged Faiz Over a year ago

Hey Alex, Thanks so much for the solutions you have provided! I haven't tried one yet but I think the first solution is better for my case will try it in a few hours

Alex Mamo Over a year ago

You're very welcome, Maiz.

Maged Faiz Over a year ago

I have tried the solution you provided, it required an index at first but after creating one it works fine but the only problem is that it returns an empty list even thought there's data in Firestore

Alex Mamo Over a year ago

Yes, an indeex is required. Without seeing the code, it's hard to say what's wrong. So please post a new question, here on StackOverflow, using its own MCVE, so I and other Firebase developers can help you.

Collectives™ on Stack Overflow

Firebase Database Structure For Better Queries

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related