9

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?

Andrew Lee
  • 10,127
  • 3
  • 46
  • 40
cdock
  • 850
  • 9
  • 15

1 Answers1

11

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.

Juan Lara
  • 6,454
  • 1
  • 22
  • 31
Gil Gilbert
  • 7,722
  • 3
  • 24
  • 25
  • So here you basically have an object `tenants` in your document right, and you're querying on `tenants.cdock`. Does this mean that Firestore is capable of indexing on these nested properties? – Gertjan.com Nov 27 '17 at 09:06
  • 1
    I was trying to do something similar but upon querying Firestore tells me that it needs to create an index first, and wants to index every possible key like 'tenants.cdock', 'tenants.alice' etc. My keys are variable like the tenant names here, so I can not have them indexed all before querying. How do you get around this? – Thijs Koerselman Jan 31 '18 at 14:41
  • @ThijsKoerselman there's info in the docs about this, in case you've not found it yet: https://firebase.google.com/docs/firestore/solutions/arrays#solution_a_map_of_values You encode more info in the data structure, such as a timestamp as the value to the key: `'tenants': { 'cdock': 1502144665, 'alice': 1502144665, 'bob': 1502144665 }` which at least lets you have some range-querying capability on these properties.. – cdock Feb 12 '18 at 18:46
  • @cdock I found that but for me it was not an option unfortunately. My data was too complex so I choose to do the sorting client-side. Here's [more info](https://stackoverflow.com/questions/48545826/how-to-query-a-variable-field-and-apply-ordering-without-creating-an-index-first) – Thijs Koerselman Feb 13 '18 at 12:26