Combine two datasets in NoSQL (Firestore) where one is date-range and one is date - without N+1 lookups?

Question

I have a programming/logic issue in a Node.js app using GCP Firestore (Nosql). The fact that the app is JS/Node.js is in this issue irrelevant (I believe) as this questions is more of a pseudocode/logic issue.

I have two datasets in Firestore. One is called Days and one is called Events. I use the Days dataset to, on the application, display interesting things that occur on these Days. An Event is something that could last for 1 to at most 7 days, with a start date and en end date. Events is (despite the name) rarely updated (once a month). Example:

Days dataset (apprx 7000 items):

'2017-04-02': {
  'description': 'A fun day'
}, 
'2017-04-01': {
  'description': 'A boring day'
},
'2017-03-30': {
  'description': 'A random day'
}

Events dataset (apprx 70 items):

'one-day-event': {
  'startDate': '2017-04-01',
  'endDate': '2017-04-01'
},
'multiple-day-event': {
  'startDate': '2017-03-28',
  'endDate': '2017-04-03'
},
'multiple-day-short-event': {
  'startDate': '2017-04-01',
  'endDate': '2017-04-02'
}

What I want: I would like to iterate through Days and find all events that occur during this Day. This could simply be done with a lookup where every day, looks for dates that exist within the Event date-range, but this is highly ineffective (N+1).

I could also pre-fill all Days with the data from Events so that Day '2017-04-01', for example includes these three Events. But then that data will be identical in different places, which would just be bad coding.

So, the best possible outcome would be to have Day "pre-associated" with Events. Is my best option here to do a scheduled run every month and fill up Days accordingly?

'2017-04-01': {
  'description': 'A boring day',
  'eventIds': ['one-day-event', 'multiple-day-event', 'multiple-day-short-event']
},

and then do a N+1 single .doc()-lookup, which would at least be better.

What are my options here?

Answer 1

On a relational database you'd perform a server-side join to get the data from two (or more) tables in a single query. On Firestore no such server-side joins are possible though.

But then that data will be identical in different places, which would just be bad coding.

This is where you're wrong. While relational databases are often quite dogmatic about not duplicating data, NoSQL databases take a different stance and focus on scalable performance over almost anything. So in your scenario duplicating the data for the day into each event, or duplicating some data for each event into the day, is actually quite common.

I recommend checking out these to learn more about this:

• this article on NoSQL data modeling.
• the video series Getting to know Cloud Firestore
• my answer on How to write denormalized data in Firebase