Modeling a three-way many-to-many relation in Firebase

Question

I'm currently working on an app for sports statistics and I'm investigating if/how Firebase can handle the data and relationships I need.

Because Firebase does not seem to support the equivalent of WHERE a = x AND b = y, I'm not sure what a good / the best approach is for my use case.

I have the following entities in my system:

• Country - a country of the world.
• Sport - a sport.
• Region - a region in a country. For each sport, the way the country is separated in regions is different.

There will be a page where you can select a country and a sport, and a list of regions will be shown.

For Country and Sport it seems easy:

{
    "countries": {
        "-Kl9JB-XRzZyz_GkRiW8": {
            "name": "The Netherlands"
        }
    },
    "sports": {
        "-Kl9JB-XRzZyz_GkA83b": {
            "name": "football"
        }
    }
}

But what about regions? Since a region is tied to a country and a sport, something like this seems logical:

{
    "regions": {
        "-Kl9JB-XRzZyz_GkRiWa": {
            "name": "West",
            "country_key": "-Kl9JB-XRzZyz_GkRiW8",
            "sport_key": "-Kl9JB-XRzZyz_GkA83b"
        }
    }
}

However, this leads to problems, because as far as I know, I cannot query for db.ref().child('regions') and then filter by country_key = a and sport_key = b server-side. I could get all regions for a country, and filter by sport client-side (or vice versa), but that's not a very elegant (or scalable) solution.

I thought about a few solutions:

• Adding to each region a compound country/sport key, e.g. "country_key:sport_key": "-Kl9JB-XRzZyz_GkRiW8:-Kl9JB-XRzZyz_GkA83b"
• Adding a new entity sports_countries, and have each region point to one of those entities.

Of course, this is just a part of my whole model. The full model has many more relationships, for example regions have competitions, countries have clubs, clubs have teams, teams are on competitions, players are on teams, etc etc.

How would you solve this in Firebase? Or, also looking at the rest of the data model, would you tell me that I should stick with a relational database instead of Firebase?

Answer 1

For a three way relationship you can combine country_key and sports_key (not advisable for more than 2).

{
    "regions": {
        "-Kl9JB-XRzZyz_GkRiWa": {
            "name": "West",
            "combined_key": "-Kl9JB-XRzZyz_GkRiW8-Kl9JB-XRzZyz_GkA83b"
        }
    }
}

And then query them together:

...ref("regions").orderBy("combined_key").equalTo(country_key + sports_key)

Again, not advisable if you are trying to filter by more than 2 fields, but works well for 2.

This question has more insights.