Firebase Database proper structure for several indexes

Question

I'm still relatively new to Firebase and it's been pretty weird to move from that considering my knowledge with SQL.

This is my desired table

Contracts
    ID
    PlayerID
    CompanyID
    Status
    ExpiryDate
    Etc...

In one function, I would like to get all contracts where PlayerID = custom_playerid (to get all contracts of one player)

In another function, I would like to get all contracts where CompanyID = custom_companyid (to get all contracts of one company)

In another function, I would like to get one contract where Status = 0 and CompanyID = custom_companyid (See if there's a 'non-fulfilled' contract in a company)

And in a cron job, I would search for all the contracts where CurrentDate > ExpiryDate and Status = 0 to remove them from the database.

How should I approach this? I've just learned that I couldn't do several orderByChild and that's been making the database a bit more complicated for newbies.

I've considered at some point to make several tables "ContractByCompany", "ContractByPlayer", which would facilitate the task but I would assume it would be a big pain to fix all the tables I currently have to suit that.

score 1 · Answer 1 · answered Oct 16 '18 at 13:36

If you're new to NoSQL database, I'd first recommend reading this article about NoSQL data modeling. Then if you come from a background in relational databases, what the video series Firebase for SQL developers. With that material under your belt, you'll be in a much better shape to reason about data models. :-)

All three use-cases you mention seem possible without custom indexes. The first two are directly supported by queries on your current data model, while the last one (performing an AND on two values) is possible by introducing a synthetic, composite property such as "Status_CompanyID": "0_custom_companyid" and then querying with ref.orderByChild("Status_CompanyID").equalTo("0_custom_companyid"). For more on this approach, see http://stackoverflow.com/questions/26700924/query-based-on-multiple-where-clauses-in-firebase

That said, it is also fine to create custom indexes. In fact, for the first few years of its life Firebase didn't support orderByChild(), so such custom indexes were the only way to perform such queries. In this case you'd end up with three custom indexes in addition to your primary Contracts node:

ContractByCompanyId
ContractByPlayerId
ContractByStatusAndCompanyId

You're duplicating data under these nodes, where every write operation to Contracts also results in write operations under the indexes. That is an operation quite similar to what a relational DBMS may do automatically for you behind the scenes. In NoSQL it's just handled by your own code, in a process known as fan-out.

It can indeed be tricky to initially write the code to keep the fanned out data up to date. For the most common strategies for this, see How to write denormalized data in Firebase.

Firebase Database proper structure for several indexes

1 Answers1