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 "[ask]". — the Tin Man, Sep 28 '21 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, Sep 29 '21 at 21:10

score 2 · Accepted Answer · answered Sep 21 '21 at 06:48

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.

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 — Maged Faiz, Sep 21 '21 at 09:13
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 — Maged Faiz, Sep 26 '21 at 16:02
Yes, an [indeex](https://stackoverflow.com/questions/50305328/firestore-whereequalto-orderby-and-limit1-not-working) 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](https://stackoverflow.com/help/mcve), so I and other Firebase developers can help you. — Alex Mamo, Sep 26 '21 at 17:03

Firebase Database Structure For Better Queries

1 Answers1