I’m looking for help with best practises using many to many relationships with attributes pertaining to those relationships and how to sort on them.
I’ve read plenty of posts on the forum regarding this, particularly by @NigelG but haven’t really grasped a solution for my use case (similar to IMDB example below). If I’ve missed any existing tutorials or info, please do point me in the right direction, thank you!
Here’s an example of what I’d like to know how best to solve:
Imagine the IMDB data structure as an example. There are 3 important Data Types:
Title (a movie, TV show, etc. e.g. Star Wars)
Person (a person credited in a Title e.g. George Lucas, Harrison Ford, John Williams)
Credit (a Person’s credit(s) on a Title e.g. Director, Actor, Composer)
In terms of database structure, I’ve realised this isn’t as straight forward as having a List of Persons custom field on Title (or a List of Titles custom field on a Person), as Credits also need to be recorded per Title —> Person relationship. Also, Person may have multiple Credits for a Title (and perhaps different sets of multiple Credits for other Titles) e.g. Actor/Director/Producer/Writers, Composer/Orchestrator/Instrumentalists etc.
Some solution options & problems with them
Create an intermediate (joining) Data Type Title-Person-Credit with fields for each of the 3 custom data types above: Title, Person, Credit. In this case a Title-Person-Credit Thing would be required per Title per Person per Credit. But how then does one display all Title-Person-Credit data for a Title, sorted by Credit (e.g. on its “name” field), or by Person, or even by a Credit “sorting index” field, so Credit data is always listed in a particular order e.g. Director, Writer, Actors etc.? These sorting options simply don’t appear via Bubble’s “apostrophe” language, so it doesn’t seem to be possible to sort by Credit in this case without a hack of e.g. duplicating Credit “name" field into a new text field in Title-Person-Credit, and then sorting by that (as this field will appear as a sorting option). But this seems like an overly complex solution, not to mention having to maintain that text field whenever the Credit “name” field changes etc. Is there a better way to sort by Credit in this example?
A distilled version of 1. where the intermediate Data Type is simply Person-Credit (custom fields for Person & Credit only) with a List of Person-Credits custom field in Titles, although this could easily break through the recommended <100 list item limit). This also has the same sorting problems as 1. but is more efficient with data (less redundancy, as each Title binds to Person-Credit combinations)
I believe it’s possible to use the Intersect and Merge searches to join data types too, but it’s not 100% clear to me how this would work in the IMDB example, and how it compares to solution 1 in terms of performance, and how to sort by Credit. Presumably a “join table” data type would still be required? And would that data type need Title and Person unique_id reference (text) fields, rather than custom field types, to be able to do the “join” searches (using intersect/merge)? Will sorting on Credit be possible and more straight forward that solution 1.?
Are there other solutions I’m missing? Grateful for any thoughts on what are the best solutions might be.
Many thanks indeed.