Forum Documentation Showcase Pricing Learn more

How to learn about database models and database structure

I feel that a lot of new users struggle with bubble, not just because there is a learning curve to the bubble platform itself, but because most are non-coders and do not have a background in database design.

Personally, once I began to understand database design a bit more, it had profound impacts on how I was able to build using bubble, especially when it came to ease of manipulating data.

I also feel, that a lot of new users forget about Google…that powerful search engine that can point you in the direction of the information you seek, mostly because it seems like our answers should be coming from the bubble forum. In my experience, I have learned a lot about bubble on the forum, but I’ve learned things that have improved my abilities off the forum for things like user experience design and database design, two crucial components to building a successful app.

One of the best resources I have come across using google when searching for database design models is a website called Vertabelo.

The site has lots of database models available with explanations for each data type involved, the different data fields in each and the relationships between them.

For anybody in need of guidance on database design, they should check it out.

https://www.vertabelo.com/blog/example_models/

They have different professional database designers who post articles that are basically how to tutorials. Here is a link to one designer who has database models that I find helpful for retail sales models.

https://www.vertabelo.com/authors/emil-drkusic/

If you can’t find what you are looking for…remember google. Just type in the search bar what type of database design you are in need of followed by “vertabelo” and they just might have an article on it.

14 Likes

Oh this is fantastic!

I have somehow got some small tournaments functional, but am now ready to go to larger tournaments and the size is overwhelming and I just thought there must be a better way.

Thank you Thank you for the site. I have already found a tournament one built for Tennis tournaments. First read thru it appears there are some similarities to what I want to create for larger tournaments.

Awesome resource and thanks again for sharing it!

Excellent resource.

Just remember that the “id” and “fk” parts are done for you by Bubble field types and list.

Building in your own keys is probably not the most efficient thing to do.

Very much agree on the importance of good database design, since it becomes the primary constraint in terms of business rules. Bubble allows flexibility in terms of lists, but these are not the same thing in terms of related tables. And design choices will impact both performance and the ease with which development can be done.

I need to spend more time on this site but my guess is that newbies should look at tutorials for PostgreSQL, which I think would be closest to Bubble, instead of more traditional databases like MySQL. (IIRC, Bubble uses PostgreSQL on the backend.)

If I have a database with Tournament Details and one field is NAME.

If I have another table that I want linked to a a specific Tournament, should I link the NAME field or the ID Field to that table?

Neither !

To link TABLE A to TABLE B you have a field in TABLE A with the data type of TABLE B (scroll down a bit past date/number/text etc).

Bubble does the link for you and handles the joins.

Relating tables in Bubble is simply a matter of naming a field’s data type as the name of the table you want to link to. Embedding a foreign key in a table is not necessary.

For 99.9% of cases that will be the easiest way to develop as you don’t need to link tables in your search.

Postgres is largely irrelevant here as Bubble’s data worked the same way when it was on Elastic (very much not relational). Our tables are not Postgres tables. (Postgres allows for table inheritance which is certainly not how bubble does things).

I understand how to relate tables in Bubble. While this is second nature for anyone who has tangled with SQL, for new users I think this can be a confusing concept and it isn’t always clear when and why this would be done, and the benefits of one approach versus another and the availability of list fields makes it more challenging.

If I have created a table for Tickets (help desk or service tickets) and I need notes on those tickets I can create a text field and make it a list. This may work in limited cases, but anyone with experience knows that this will run into limitations if those notes need attributes per note, like which user made a note, or files attached to each notes, or date fields etc.

So you create a table for Notes - and then create the association by putting a list field on Ticket for the Notes data type, and tying the two together neatly on the Ticket side. This will be easier in some cases to pull data together in development and perhaps in performance (?) but could create orphan notes and you could end up with hundreds of values in that one field for each row.

So the other way is to create a Notes table with a Ticket field. Then you will be doing a lot of ‘Search for …’ type of activity to associate things back together in the UI which could be slower (?) but then again could be faster because you are not overloading some list field (?). It also allows you to find and manage orphan Notes - and changing a single field is a bit easier than adding and removing things from a list.

Then there’s the scenario of “what if I want a Note that applies to multiple Tickets” and if you a traditional SQL person you will start normalizing things at this point with things like a NoteTicket table, though, maybe if it’s an edge case a list field of Tickets could come into play.

I’ve been using Bubble for 2+ years and have worked with SQL and data modeling for 15+ years and it makes me laugh sometimes to see the data solutions that users have come up with on the forums. BUT … that doesn’t mean they do not work or I necessarily know better. I know Bubble has tried to support a development approach that supports “doing things like Excel” – which means a lot more free-form. So those solutions, while they may seem a bit bonkers, are legitimate.

I just wish there was a better guidepost for the performance implications of one approach versus another, because you can land in a world of hurt when things scale and there’s a lot of unknowns in an MVP-phase project.

Thank you very much for your post on how to better understand database design through the given online source and other ideas.

Yes, and that statement was not really for you :slight_smile:

It also worries me when I see some of the ways things are done. Agreed, there is a lot of flexibility.

I have written a lot of posts in the past about data structures and comparisons of SQL Vs Bubble.

Not quite sure what else is needed to get the word out there. Those examples are great and would be useful to turn into Bubble tables.

Maybe people are googling relational databases and applying to Bubble ?

Any suggestions welcome. Was really just hoping to stop people from immediately putting in foreign keys !

When someone is new to tables & data structures and asks me about it,

I always give the example of an Excel workbook with many sheets (which seems more familiar to many), that are all linked, and all individually have rows & columns. More is explained in this short video playlist:

1 Like

Thats good and thank you. Can you recommend something for user experience as well please?

Yes, totally agreed. I add one step which is showing a newbie how Excel’s data validation translates to relational structures.

Not quite sure what you mean ?

Thank you very much @TipLister for this.Thank you for your YouTube Channel. I have subscribed(I’m #no7 :man_dancing:man_dancing:) and looking forward to more vids. :+1:

Hi

I really enjoyed this post in particular, and have found it very useful in my thinking.

So, based on this, this is how I understand the various options:

Option 1 - “text field list in the Tickets table”
-only works in few cases / runs into limited cases

Option 2 - “list field of Notes in the Tickets table”
-easier to pull data in development + maybe performance
-could end up with lots of values in one field for each row
-could lead to orphan Notes

Option 3 - “field of Tickets in the Notes table”
-easier to work with as not dealing with lists in the Notes table
-need to do alot of Searching which could be slower

Option 4 - "if need to apply a Note to multiple Tickets
-create a Tickets list in the Notes table

My questions are:

  1. Is there any easy way to evaluate whether one should be going for “option 2” or “option 3”?
  2. Is there ever any rationale for doing both “option 2” and “option 3” in Bubble? ie putting a “list of Notes” in the “Tickets” table whilst simultaneously also putting a field for a “Ticket” in the “Notes” table? Or is this entirely redundant?

I ask the second as this is what I’ve done in my database to date (probably in some confusion as to how databases should be designed), and I’m now running into challenges relating to retrieving and editing data.

I am building a marketplace application where I essentially white label software engineers working in development agencies and sell these then to clients looking for software engineers. Leaving the client side out of it…this is how I’ve constructed my database to date.

Does it look horribly wrong? Would appreciate any guidance that can be offered.

Screenshot 2020-02-20 at 12.44.40

Screenshot 2020-02-20 at 12.45.06 Screenshot 2020-02-20 at 12.45.13 Screenshot 2020-02-20 at 12.45.15

Attaching an overview as to my data types and how they all relate together:

Feel like I am getting muddled somewhere, as quite new to database design…so trying to get to grips with.

Thanks!

What I mean is something that guides us to the best practices in designing the user interface - how to structure it and data flows etc. Any material that will help us design a better UI/ UX interface

I use google for that myself. I just google search something like

sign up user design or sign up ux design guide

I find a lot of different resources that way and I read through all that I find informative and if I see overlapping ideas, I ensure I use them