firebase realtime database query to find data

Question

I wish to store data for some children activities where each activity is good for certain age range. Let's say act A is good for 2 - 5 year old. act B is good for 0 -1 year old.

On the client side, there is a fixed set of choices like: 0 - 1 years, 1 - 3 years, 4 - 5 years, 6 - 13 years

Now the requirement is that the activity A should come up for selection 1 - 3 as well as 4 -5 years as 2 - 5 overlaps both the ranges.

What would be the good way to store activity data and then query it efficiently ?

Answer 1

Assuming the fixed set of choices is a permanent feature to your application, I'd have a boolean field for each match, for example, your activities would look like:

activities: {
  activityA: {
    range0to1: false,
    range2to3: true,
    range4to5: true,
    range6to13: false
  },
  activityB: {
    range0to1: true,
    range2to3: false,
    range4to5: false,
    range6to13: false
  }
}

And then when you want to query all activities which apply for eg. ages 2 to 3, then you already have the field to query with nothing too complicated.

But really for longevity, I wouldn't assume that the fixed set of choices is permanent for the lifetime of a an app, in which case I'd rather have something like:

activities: {
  activityA: {
    minAge: 2,
    maxAge: 5,
  },
  activityB: {
    minAge: 0,
    maxAge: 1,
  }
}

...and then if I want to query for the fixed choice of ages between x and y, my ideal query would be for all activities where either minAge or maxAge are between x and y (hence there's an overlap in the range)

eg (pseudocode) where ((minAge > x and minAge < y) or (maxAge > x or maxAge < y))

But unfortunately, in practice, firebase RTDB doesn't let you query by multiple fields, so if it's not too late, I'd recommend looking at Firestore which may be better suited for your needs (personally I think I'd typically recommend firestore over RTDB for most use-cases).

If you are stuck with RTDB, then another solution might be to create a lookup block at the root of your structure:

{
  activities: {
    activityA: {
      // age range of 2-5 stored however you like
    },
    activityB: {
      // age range of 0-1 stored however you like
    },
    activityC: {
      // age range of 0-3 stored however you like
    }
  },
  ageActivityLookup: {
    age0: {
      activityB: true,
      activityC: true,
    },
    age1: {
      activityB: true,
      activityC: true,
    },
    age2: {
      activityA: true,
      activityC: true,
    },
    age3: {
      activityA: true,
      activityC: true,
    },
    age4: {
      activityA: true,
    },
    age5: {
      activityA: true,
    }
  }
}

So then you can simply query ageX and get your list of activities. This will mean multiple queries if you're looking for a range of ages, and does mean having to ensure your lookup block stays in sync. This should be OK if the rest of your application data structure isn't too complex.