One To Many Relationship In Firebase

Question

I have a Firebase Database. I have Job objects and Item objects. Each Job can contain many Items and one Item must be contained by a single job (Simple One to Many). My present database structure is that both Item and Job are top level lists in firebase and each Item contains the jobKey to which it belongs. This allows me to find all items in a job as long as I know the job key. The firebase code to find the items requires a query which includes and an "orderBy" on the jobKey in the items. So it is easy to retrieve all items for a job. However, I also want to sort and filter the items based in additional data in the items. But because of the firebase restriction of a single "orderBy" I cannot accomplish this second level of filtering using firebase. This is my challenge. The structure of my present data is illustrated below.

+--jobs
  |
  +-- jobKey1
  |  |
  |  +-- <jobdata1> ..
  |  
  +-- jobKey2
     |
     +-- <jobdata2>..

+--items
  |
  +-- itemKey1
  |  |
  |  +-- jobKey : jobKey2  // this item belongs to job2
  |  |
  |  +-- <the rest of item1 data
  |  
  +-- itemKey2
  |  |
  |  +-- jobKey : jobKey2  // this item belongs to job2
  |  |
  |  +-- <the rest of item2 data
  |  
  +-- itemKey3
  |  |
  |  +-- jobKey : jobKey1  // this item belongs to job1
  |  |
  |  +-- <the rest of item3 data
  |  
  +-- itemKey4
     |
     +-- jobKey : jobKey1  // this item belongs to job1
     |
     +-- <the rest of item4 data

As mentioned earlier, I want to be able to retrieve all items for a job and then order and filter items by various field in the item. Given the structure above, the only way to do this (that I see) is use the firebase query to retrieve the items, then use logic in the component (I'm using angular2) to cache all the items into some sort of collection then sort and filter based on the cached data. This isn't very satisfying, there must be a better way. What are the reasonable alternatives?

Answer 1

I have come up with the solution.

One alternative way to structure the data is to nest the items list directly in a Job. This is illustrated below. This allows the use of firebase queries to order and filter within the item data. This seems like a good solution but it has scaling drawbacks.

   +--jobs
      |
      +-- jobKey1
      |  |
      |  +-- <jobdata> ..
      |  |
      |  +-- items
      |     |
      |     +-- itemKey3
      |     |  |
      |     |  +-- <the rest of item3 data>
      |     | 
      |     +-- itemKey4
      |        |
      |        +-- <the rest of item4 data> 
      |  
      +-- jobKey2
         |
         +-- <jobdata>..
         |
         +-- items
            |
            +-- itemKey1
            |  |
            |  +-- <the rest of item1 data>
            | 
            +-- itemKey2
               |
               +-- <the rest of item2 data> 

On drawback of this solution is that it doesn't scale very well if the item lists are large, because each read of the Job reads all items. And in my case, when I read the Job object, I don't want to read the entire item list, rather I want to create a ref to the item list and use the firebase query/filter capability on the item list. So there is really no direct benefit from placing the item list inside the Job. In my application the practical limit is hundreds of items. So even though the solution will probably work for my application, there must be a better, more scalable solution.

A better solution.

After further consideration, the best solution to this problem is to create Item lists that are specific to a job but not contained in the Job object, so that when a Job is read, the entire list doesn't have to be read. And we also preserve the ability to order/filter data in the item list based on its data. This structure is illustrated below. ItemLists for each job are kept at the path "itemlists/< jobKey >/items"

+--jobs
   |
   +-- jobKey1
   |  |
   |  +-- <jobdata1> ..
   |  
   +-- jobKey2
      |
      +-- <jobdata2>..

 +--itemlists
   |
   +-- jobKey1    // items list for job1   
   |  |
   |  +--items
   |    |
   |    +-- itemKey3
   |    |  |
   |    |  +-- <the rest of item3 data
   |    |  
   |    +-- itemKey4
   |    |  |
   |    |  +-- <the rest of item4 data
   |    
   +-- jobKey2    // items list for job2   
      |
      +--items
        |
        +-- itemKey1
        |  |
        |  +-- <the rest of item1 data
        |  
        +-- itemKey2
           |
           +-- <the rest of item2 data