Designing Cloud Firestore schema with many-to-many and cross entity relationships

Question

Just a quick question about designing a Firestore schema:

I know you gotta use the right tool for the job, and Firestore isn't supposed to be a Relational database, but is there suggested techniques to model many to many relationships?

Ex:

I want to build a property management app where

Companies own Properties, which contain Units. Hierarchy is straightforward here.

So Tenants will rent multiple Units over time, and each Unit will have many tenants over time.. all that encapsulated by Leases which tie 1 Unit to 1 or more Tenants.

Units can own Leases I guess, but how do Tenants see all their Leases over time.. should Leases have a sub collection that references the Tenants.. are Tenants top level?

Just looking for some basic suggestions..

Or is it better to just user GraphQL with a relational database?

Answer 1

Update: As of May, 2019, Cloud Firestore now supports collection group queries.

Units can contain Leases sub-collections and you can use a collection group query to return all leases across all units that include a specific user.

Original answer

There's no free lunch here unfortunately.

Units can own leases but that currently prevents cross unit queries like which units a tenant has leased. We don't yet support collection group queries but when we do this would be viable.

To solve this today, make leases a top-level collection of 1-to-1 mappings between unit and tenant fields then your lease history would be db.collection('leases').where('tenant', '==', 'cdock'). You could store enough information about the unit in the lease that you could avoid joining from leases to units and enough information about the tenant to avoid joining there.

If the number of tenants per lease is small you could also make leases 1-unit to many tenants with a tenants set like so:

'tenants': {
  'cdock': true,
  'alice': true,
  'bob': true
}

Then you could query for your history with db.collection('leases').where('tenants.cdock', '==', true).

Tenants can be top-level (and joined) or not depending on how much information you need to keep about them and whether or not the lease needs up-to-date information about the tenant.