Performance of a database with multi-relations

Hi,

I created a meditation app: there are programmes, each of them has a list of songs. When a user listens to a song, I keep the information in the DB (same for programmes). For that, I linked both collections: a user can have many songs and a song can have many users (same for programmes: a user can have many programmes and a programme can have many users)

I was wondering if the performance of the app could be really impacted if I have thousands of users (each song could have thousands of users and each user could have dozens of songs; same for programmes)

Thank you in advance for your light!

Hi @gary,

The performance of the app will be impacted with the growth of userbase and number of relationships between users and songs.

Real-life example from one of my clients’ apps: approx. 1K users and 150 businesses, each user can favourite a business, on average one user favourites ~10-15 businesses. I can’t count the total of number of relationships now, but adding a new one takes several seconds (so users simply don’t add new businesses as favourites - they don’t want to wait several seconds).

In some cases creating a separate “junction table” (each record has a link to a song, and another link to a user) could help, but the efficiency will depend on how are you structuring your screens and data access/ change in your app. And this, in turn, depends on the requirement why do you want to store the listened songs.

Couple of examples. If you would like to “favourite” a song directly from the song list, it will work badly both for M2M and for Junction table cases. If you limit the actions to a screen with a current song, then it might work better for some time.
Maybe you could use both approaches. As an idea - show M2M relationships in a song list (with NO actions); and use junction table in Song Details. Using Junction table to show the list of listened songs should work faster than filtering all songs using M2M (though I might be wrong here - I don’t know what optimizations does Adalo have under the hood).

Bottom line - I haven’t found any good solution using Adalo internal collections for creating favourites (I’m talking about scalable one). Maybe someone could advice. I have some promising results with external collections, though they are not yet ready to be published.

Best regards, Victor.

4 Likes

Thank you Victor for your really interesting feedback!

I am going to remove right away my “Add a favourite” feature, because the UX won’t be satisfying like you said

But I was thinking about keeping the “User can see the listened songs” feature. You said with your client it would take (with 1k users) several seconds to add a favourite, but does it take that long too to check the favourites’ list? or is it faster?

About your scalable solution you are testing with external collections, do you use Airtable, Spreadsheet or another solution?

Thanks a lot,

Gary

Hi @Victor,
Do you find with join tables it is easy to have double / triple entries if the user clicks the button a few times? Sometimes I prefer a many to many setup so this is prevented.

I think that without a true 1:1 relationship option in Adalo, join tables can be problematic. BUT I’m happy to learn otherwise.

Hi @gary,

If you display songs in a list, I guess you will have to use M2M relationships to display “listened” songs. With junction table option it might be more resource consuming (for each song you will have to send a separate query to another table; 10 songs → 10 queries).

If you mark the song as “listened” automatically (say, upon entering player screen with current song), and if you won’t display all songs in one long list but rather in a “lazy loading” one, then displaying “listened song” label using M2M might work. Most probably Adalo will send a filtered internal query to its backend, and backend should (hopefully) do the job for you.
It will be slower, but I don’t know how much slower.

Reg. external collections - I’m not ready to disclose any info publicly yet, sorry.

Best,
Victor.

1 Like

Thank you Victor for your reply, indeed I use M2M relationships and I will display the list of songs and the listened songs will have an icon, so it’s not that bad if the icon is displayed with a few seconds late

No problem for the external collections :slight_smile:

Thanks again!

1 Like

Hi @Rozza,

You’re right, with M2M you won’t get multiple entries by design.
For new records, unfortunately there is no efficient way (or at least way I’m aware of) to prevent Adalo from creating several entries by clicking button several times; it takes time to receive reply and process it, and user can click another time meanwhile. Sometimes combinations of junction and M2M may work.

As for Join tables - with relationships we get “simulated” join (in my opinion 1:M relationship could be viewed as a foreign key on M side). But of course we don’t get all the power of true SQL database. Keep in mind - this is a no-code platform :slight_smile:

Best regards, Victor.

1 Like

@Victor
Thanks for sharing your knowledge. Very helpful. I’m sure a lot of us are willing you success in your external collections work.
Thank you

1 Like