NoSQL Database Design for Queries with Multiple Constraints (Firebase)

Question

I need best practice tips to improve the performance of my noSQL database (hosted on Firebase). Moreover, I need advice on how to structure nodes.

The database stores product information with three main properties:

$productId /date /category /subcategory 

On my website, I have three views:

• get the last 4 products (order date)
• get the last 4 products (by date) of category X
• get the last 4 products (by date) of category X and subcategory Y.

Note that I also have node product_images, which have trays matching productIDs. So, let's build the databases as follows:

 $categoryId $subCategoryId $productId 

will not work, since I need to know $ categoryId and $ subCatrgoryId in advance before I can match it with $ productId. It will also make it difficult to get the last 4 products.

How could I effectively create my noSQL database and how can I get products with Firebase by filtering them with a few restrictions?

I know that in Firebase you can use orderByChild and equalTo, but this only works on one restriction, while I have to deal with one or three.

Answer 1

Since Firebase can only filter one property, you will need to combine the values ​​you want to filter into one property. Since you have several filtering requirements, you may need this property for each use of filtering.

It seems to be possible for you if you combine <category>_<date> into one property, which we will call multi-prop . We will also combine <category>_<subcategory>_<date> into a property called megaprop :

 $productId /date /category /subcategory /multiprop /megaprop 

Some sample data:

 id_cc_1234 date: 20151031 category: candy subcategory: halloween multiprop: candy_20151031 megaprop: candy_halloween_20151031 id_tg_2345 date: 20151125 category: candy subcategory: thanksgiving multiprop: candy_20151125 megaprop: candy_thanksgiving_20151125 id_tg_3456 date: 20151125 category: food subcategory: thanksgiving multiprop: food_20151125 megaprop: food_thanksgiving_20151125 id_sk_4567 date: 20151205 category: candy subcategory: sinterklaas multiprop: candy_20151205 megaprop: candy_sinterklaas_20151205 id_sc_5678 date: 20151225 category: candy subcategory: christmas multiprop: candy_20151225 megaprop: candy_christmas_20151225 

Now your queries turn into:

• extract the last 4 products

   ref.orderByChild('date').limitToLast(4); 

• get the last 4 products of category X

   ref.orderByChild('multiprop').startAt('candy').endAt('candy_~').limitToLast(4); 

• get the last 4 products of category X and subcategory Y.

   ref.orderByChild('megaprop').startAt('candy_christmas').endAt('candy_christmas_~').limitToLast(4); 

Also see: http://jsbin.com/piluzo/edit?js,console for the working version.