Firebase INNER JOIN and GROUP BY month query

Question

I am having some difficulties in designing the database structure in Firebase. I am new to it so I am not so familiar with it yet.

The transaction table will record the date together with the overall amount from all sub categories. As for each sub-categories table, it will only record the relevant data according to its type.

What I tried to do is, is there anyway to count the total amount of certain sub-category for each user group by month? In MySQL, I can simply INNER JOIN, WHERE clause and GROUP BY date, but I not sure with this design structure, is it possible to do so? Or I should modify the structure?

The output should be like for instance I am choosing food transaction:

Month: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
Amount : 0, 0, 0, 0, 0, 0, 25, 0, 0, 0, 0, 0

score 1 · Answer 1 · answered Jul 06 '17 at 03:39

Trying to model a SQL table structure in a NoSQL database like Firebase is a recipe for pain. Stop thinking about SQL concepts like inner joins and group by clauses.

Instead embrace the nature of the platform you are using. For example: do you need to usually show the list of transactions for an account? If so, why not model that in your JSON tree:

accountTransactions
  accountId1
    transaction1: {
      type: "entertainment"
      ...
    }
    transaction2: {
      type: "food"
      ...
    }
  accountId2
    transaction3: {
      type: "food"
      ...
    }
    transaction4: {
      type: "food"
      ...
    }

Now if you want the list of transactions for account 1, instead of needing a query, you can directly them from /accountTransactions/accountId1.

If you also want other lists of transactions, you may need to duplicate some of the data in your tree. For example, if you want to show a list of all food transactions, you could add this to the database:

transactionPathsByType
  food
    transaction2: "/accountTransactions/accountId1/transaction2"
    transaction3: "/accountTransactions/accountId2/transaction3"
    transaction4: "/accountTransactions/accountId2/transaction4"

There are many ways to model this last relationship. For example, you could also keep the list of transactions as a single flat list:

accountTransactions
    transaction1: {
      account: "accountId1"
      type: "entertainment"
      ...
    }
    transaction2: {
      account: "accountId2"
      type: "food"
      ...
    }
    transaction3: {
      account: "accountId2"
      type: "food"
      ...
    }
    transaction4: {
      account: "accountId2"
      type: "food"
      ...
    }

Which would simplify the mapping from transaction type to transactions to:

transactionIdsByType
  food
    transaction2: true
    transaction3: true
    transaction4: true

We're just using true as a dummy value here, since all the information needed to look up each transaction is already present in the key.

If you with the straight list of transactions also want a quick way to look up the transaction IDs for an account, you could add an index that maps the account ID to the corresponding transaction IDs:

transactionIDsByAccount
  accountId1
    transaction1: true
    transaction2: true
  accountId2
    transaction3: true
    transaction4: true

There are as many possible models as there are app ideas out there. More probably. For a good introduction article, I recommend reading NoSQL data modeling. Definitely also watch Firebase for SQL developers on youtube.

So by the time when you insert a new transaction under accountTransactions, you have to insert another record under transactionIDsByAccount as well? — QWERTY, Jul 06 '17 at 05:52
Hey I have updated my question. Can you help me check for the edited portion? — QWERTY, Jul 06 '17 at 08:18
For you comment: yes, you will store data in multiple locations. This is called fanning out and can be done with [multi-location updates](https://firebase.googleblog.com/2015/10/client-side-fan-out-for-data-consistency_73.html). I usually recommend **storing** running aggregates in your database instead of calculating them on demand. See [](https://stackoverflow.com/a/37838387/209103). Stack Overflow is not great for back-and-forth Q&A like this btw, which is why I tried to give you enough to get started with. — Frank van Puffelen, Jul 06 '17 at 15:04
Hey frank, I saw your profile and realized that you are expert Firebase developer. May I ask if there's some standard for Firebase table naming convention? Because I was asked by my professor not to use those naming convention like 'receipts, stores, itemIDsByType etc'. I was so confused that I actually could not find any so-called standard online as most of them are actually the combination of uppercase, lowercase like 'itemIDsByType'. Do you have any ideas? I realized that it is abit extra if open another thread just for this small question so I would just ask at here. — QWERTY, Jul 15 '17 at 07:17
There is no standard way. If your professor doesn't like the ones you use, it would be considerate if they told you what to use instead. — Frank van Puffelen, Jul 15 '17 at 14:02
I see I see. I thought there were some standard ways existing, no wonder I couldn't find it. Thanks so much! — QWERTY, Jul 15 '17 at 14:04

Firebase INNER JOIN and GROUP BY month query

1 Answers1