Forum Academy Marketplace Showcase Pricing Features

Database structure solutions for many to many relationships with attributes required to sort by

Hey Bubblers!

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

  1. 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?

  2. 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)

  3. 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.

1 Like

Interesting question. There are smarter people than me in this forum who could help out, but let me throw some random ideas at it:

Petter

Check @petter 's book. It’s full of great tips. It might help you think through your situation.

TemplatesU?

Are there any templates on the store that already built a similar app? Some templates serve as great education.

Answering your question

I would go with option 1.

Couldn’t you just make 3 different blocks like IMDB does now? Otherwise, if you want everything in 1 single RG, couldn’t you join different searches? searchForDirectors + searchForWriters + searchForActors?

Random ideas

Algolia or FaunaDB

This text will be hidden

Algolia

I don’t know much about Algolia. However the fact that Bubble is (was?) looking at integrating with that service might be an interesting route. Yes, you will still have to structure your DB, but wouldn’t Algolia ease the pain of search and sort?

FaunaDB

I’ve stumble on this video and I was fascinated by the FaunaDB idea (08:27 timestamp). Maybe worthwhile thinking about?
https://youtu.be/W2Z7fbCLSTw?t=509

1 Like

Our data relates to Disney characters, media, franchises, studios (as pertains to how they are shown on collectible pins) so I can tell you from our perspective what we did.

I decided in the end to go down the intermediate table route for “Character starred in media” with fields on character for debut media (one media). Same for “Character groups” like dogs, cats, villains in a “Group members” table.

The data is pretty much normalised that way and you can always query in any direction to get to the others.

Your data is one relationship deeper than ours and I would definitely be going down the option one route. A credit is a person on a title in a role so your unique record identifier is actually over four fields.

I am still learning bubble. If this were traditional SQL relationships I’d make sure that your roles were also a data type so that they were properly indexed for querying.

1 Like

Well done on discovering an “association class” or “link box” or “join table” or … there are lots of words for this :slight_smile:

So what you can do is the “reverse lookup” …so search for Credit (sorted as you like) and then link to your Join Table to do the filter part.

2 Likes

I have exactly the same issue with sorting, the attributes from another table (e.g. contacts & company) simply doesn’t appear in the “sort by” list.
Filter is easy, but sorting seems impossible natively.
Has anyone found a workaround yet?

Does that “reverse sort” technique not work for you?