Firebase Database Structure For Better Queries-CodePudding

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?

CodePudding user response：

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.