Best practices around creating collections with relationships
Hi everyone, I'm trying to wrap my head around DB (and using a Electric SQL as sync engine.)
Currently my app has a list of customers and each of these customers can have multiple phones and emails.
The data is presented in a classic list -> details screen flow.
What would be the best practice for creating collections for this scenario?
1. Fetch all customers with all relationships and store them all on a customer collection and then query this collection on the details page.
- This could potentially have the app fetch a lot of data (but if this data can be stored offline, maybe it makes sense to download it one time
- If the list is paginated, how would it work for a deep link to the customer details screen (since that particular customer might not be available on the customer collection)
2. Fetch all customers to a collection and its relationship to separate collections
- This feels like the approach recommended by the docs
- I would need to implement specific endpoints on the backend for each specific table
- Feels like we wouldn't use the power of Electric SQL shapes (but maybe I'm just not understanding it correctly)
3. Create separate collection for each use case, so the list get a customerListCollection and the detail gets a customerDetailCollection
- Both collections wouldn't share data, which will lead to data duplication
- Electric SQL would keep both collections up to date (if you edit the customer on the customer detail screen, it would trigger a sync from Electric since it would be observing it)
- It doesn't feel like that's how DB should be used, since we would rarely need to use select/where/join operations on the frontend.
In general I think I'm trying to understand what are the best practices to map tables, collections and API endpoints. Should collections follow the client design or backend schema?
Currently my app has a list of customers and each of these customers can have multiple phones and emails.
The data is presented in a classic list -> details screen flow.
What would be the best practice for creating collections for this scenario?
1. Fetch all customers with all relationships and store them all on a customer collection and then query this collection on the details page.
- This could potentially have the app fetch a lot of data (but if this data can be stored offline, maybe it makes sense to download it one time
- If the list is paginated, how would it work for a deep link to the customer details screen (since that particular customer might not be available on the customer collection)
2. Fetch all customers to a collection and its relationship to separate collections
- This feels like the approach recommended by the docs
- I would need to implement specific endpoints on the backend for each specific table
- Feels like we wouldn't use the power of Electric SQL shapes (but maybe I'm just not understanding it correctly)
3. Create separate collection for each use case, so the list get a customerListCollection and the detail gets a customerDetailCollection
- Both collections wouldn't share data, which will lead to data duplication
- Electric SQL would keep both collections up to date (if you edit the customer on the customer detail screen, it would trigger a sync from Electric since it would be observing it)
- It doesn't feel like that's how DB should be used, since we would rarely need to use select/where/join operations on the frontend.
In general I think I'm trying to understand what are the best practices to map tables, collections and API endpoints. Should collections follow the client design or backend schema?
