Data Relationship Mockup for Multi Sided Marketplace

Here is a mockup of my data relationships for a Marketplace app with Coaches (Fitness), Companies, and Attendees. Here’s how it functions:

User Types/Profiles:
Coaches - Create Profiles with Photos, Videos, Hourly Rate, Reviews.
Companies - Create Profiles with Photo, Rooms, Reviews, and Events.
Attendees - Minimal Profile and ability to favorite coaches or Events

How it works:
Coaches - Search for Company Events that are Hiring.
Companies - Search for Coaches to Hire… or post events that Coaches can apply to.
Attendees - Search for Company Events to Attend or Search for Coaches they can Favorite/Follow.

**My goal is Speed and Scalability.**

My biggest questions/hangups:

  1. Should Profiles be separate data types… or should I use the User Data Type as the profile?
  2. Is it better to user Primary/Foreign Keys everywhere to link Data Tables… or is choosing “List of Data Type” on corresponding tables enough? (@NigelG @boston85719 @anon38627393 I see your names on a couple of the Join Table threads I’ve read here.)
  3. I read the “Ultimate Guide to Bubble Performance” by @petter (which was awesome) and there is some suggestion to create Search Data Types. Basically a truncated data type just for that search. Does this mean a data type that is linked with multiple tables… or a data type that has duplicated inputs that stemmed from multiple tables?
  4. Trying to do availability for Coaches based on A) Usual Days of week and times available, and then cross referencing B) Blocked out dates/times. Any tips on how to do this without creating a huge mess?

Would love feedback, suggestions, ect. Any help would be great.

*Also… if any of this is a “piece of cake” for anyone… what do you charge for an hour or two of coaching?

1 Like

After 18 months of pushing the Bubble DB to it’s limits, I can say scalability is purely a question of the level of “ordinal joining” your tables need. In this case, “scalability” refers to record counts in excess of ~5,000.

For instance, if you’re searching for Events that are hiring Coaches of a certain class or specialty, to be performant your Events table must include that search criteria data type. Such as Events: event_id | owning_company | event_date | event_address | event_specialty (that last one being what I’m referring to).

As long as the criteria remains strictly on-table, your searches will be performant into the millions of rows. However, as soon as you need to run an Inner Join (such as give me all Events where the Owning_Company hasn’t successfully hired someone yet for any of their Events OR the Owning_Company has at least X number of Rooms) know that your performance days are numbered (running purely on the Bubble DB).

This is why some have suggested “truncated search tables” (basically creating a table for every type of possible complex search you’d do) whereby all needed attributes are part of the tables definition. Sort of like a Materialized View in traditional DBMS land. BUT. Unlike a materialized view, Bubble DB won’t automatically keep these search tables up-to-date as transactions occur (events get cancelled, companies close, room numbers change, etc etc) and you’ll need to add the logic to keep all of these tables up to date as part of your logic layer, instead of it nicely happening automatically at the DB transactional layer.

Honestly, your model is small enough and the projected CRUD statements few enough in individual count that you may want to consider strictly building your app on an external hosted DB and then utilizing Bubbles wonderful SQL connector plugin. So: UI/logic on-Bubble, DB storage and complex queries on a traditional-SQL cluster. The downside with that is it does require knowing SQL plus building in Bubble strictly off of external API data types is less user-friendly. For example, simply adding/removing an attribute from a table would require you to A) make the change to the backend DB DDL, B) alter the corresponding SQL statements in Bubble SQL Connector plugin, and C) cascade the change to all the app-level points where you are utilizing that SQL/API statement as action or data. It’s that last part that Bubble’s error-checking engine is less intuitive and it will likely under-inform you on certain areas you’ve missed manually cascading the change throughout your app, so being meticulous is key. The upside is scalability would be near-infinite with a well-modelled structure. Food for thought.

4 Likes

Thanks for the great response. I appreciate the suggestion of using External DB. I had ruled that out since a few people mentioned that using an External DB via API would not be as fast as using Bubbles DB. You’ve experienced good performance using External DB’s?

Any favorites? (I am a big fan of how easy AirTable is.)

So the initialization speed (connection establishment and handshake), takes about 500ms longer than Bubble’s native DB. So for every click that requires a DB update or pull, add that time to the response rate. You can mitigate this experience however via the use of custom-states in-page to immediately reflect a user’s action, then quietly go and schedule a custom workflow / backend workflow to perform the actual DB updates outside of the user experience. This very closely mimics how most large-corp sites work as well.

My recommendation relies always on traditional DBMS’s, as this is my background. So PostgreSQL, SQLServer, etc. I’m not a fan of simplified-DB’s such as AirTable. If you’re looking at scaling to the millions of rows, you need the robustness that a traditional cluster brings.

And as for actual query performance, you won’t notice a thing on the updates (save for that extra 500ms penalty mentioned earlier that you’ll need to mitigate). But on the complex Selects (ie Searches), it’s a non-compete. For example, one query that required joining 6 tables across 1,000 driving rows in Bubble-native took over 45 seconds to complete. The same query translated in Postgresql is < 0.4 seconds across 10,000 driving rows. The wild thing here, is Bubble’s native DB is actually also PostgreSQL, so I cannot explain the performance drop when needing to perform the most simplest of INNER JOINS. I can only venture a guess it’s due to the row-level security Bubble employs (coined “Data Privacy”).

Anyway, this is how we build all our apps now. But you’ll need to know SQL, need to know how to properly model and index within a traditional DBMS, and you’ll have the increased complexity of developing within Bubble against API datasources instead of the Bubble-native DB calls, for which there are few tutorials. If it’s your very first app within Bubble, I would actually recommend developing a mock app ((read: “destined for the trash”) against Bubble’s native DB so you can at a minimum get used to the DB methodology and behavior before attempting a pure SQL-API methodology on the real thing.

1 Like