Complex Database Views

Hi all, I’m new to the Bubble world but have been floating around developing things on various platforms for many years.

I’m about to embark on using Bubble to create an app to store and display scores from gridiron games for nearly 30 years across about 12 different leagues. So as you can understand, it’s got a lot of results, nearly 8000, so I’m wondering if using the inbuilt database and views is really the best way to go. I need to not only be able to input the data (initially at least) but I need to be able to create league ladders for each season via a view and construct dynamic, ongoing results for current seasons without extra work.

Would it be best to start out with the SQL connector straight away or is there something in the Bubble built-in stuff that can help?

This platform is fantastic so far, I’m looking forward to developing some apps and seeing what it can do.

It is hard to say without knowing more about your data, but 8000 records is not necessarily large. I presume a lot of it is partitioned by league, or year, or some other way.

The SQl connector isn’t inherently better. If you have a need to do incredibly complex data transactions and just display the end results in a Bubble app it might be a good choice, but if you are going to have Bubble do all the work and just store the data in an external database I don’t think it is worth the headache.

My recommendation would be to presume you are building it all in Bubble and if you get 30% into the app and find the database limitations irking you, you can always change then.

One caveat, managing your data in Bubble is a pain. There is no reasonable Management Tool for Bubble databases. I have read on the forums that people have used the API to create their own management interface, but since there is no access to the schema via the API, even that has limited use (IMO).
I personally created a “database management page” in my app where I could just go and create workflows to do the stuff I would normally do in a management tool".

Marc

1 Like

Yes, 8000 is not all that large. People (myself included) and many more, and it is not an issue in production.

The data management side is a little flaky at the moment, but I am sure that will improve. The schemas are however available.

Not sure what you mean by “views” however. If you mean SQL Views, then the “view” in the data tab is going to be a bit of a dissapointment, it is not the same thing at all.

I’m not familiar with what exactly you mean by data management. Will you explain? What is it Bubble struggles with in that context?

Thanks!

Did you find anything good?

Did you mean the schemas are NOT available via API? Or that they are and I am missing something on how to access them.

To be fair, my comment about the issues with management stem from my background with SQL and experience with some very useful DB management tools. For what might be considered Bubble’s core market (non-developer developers) not having to use SQL might be perfect. That being said, there are database tools that don’t require SQL knowledge that have very powerful Visual interfaces (FileMaker and Access have had them for forever).

Just a few of the top issues with data management are, IMO:

Right off the bat you can’t use SQL against the database. Even a relative newbie would quickly find value in some simple SQL scripts. The workaround in Bubble is to go create a workflow and either call it from a page or create an API workflow and call it from the Bulk button in the App Data section, so it is not the end of the world, but occasionally being able to instead write

update user
set zipcode = 55555
where zipcode = 44444"

instead would be incredibly useful.

You can’t import/copy schema. Again, this might be considered an advanced use, but if I have a table called “cars” with 15 fields in it and now I want one called “trucks” with 17 fields where 12 of them are the same as “cars” it would be great to be able to either copy the cars schema or export it and import it.

The App Data interface is really clumsy. Controlling the fields that gets displayed is possible by using views, but not the order the display in. For that you have to drag the columns around. The columns often load 50 times wider than they need to be given the content in them, so when by default you should be able to see all 7 columns in your table, you can see 2. You can’t edit the data inline, which is somewhat unfortuante, but even worse, you can’t even copy it from the page. You have to open the record you are looking at to be able to copy data.

This may all seem nitpicky, but all combined the weakness of the data interface compared to the strength of the rest of Bubble is glaring. (To give credit where it is due, I am absolutely amazed at how bubble handles renaming fields and automagically changing all the references to that field - bravo).

Marc

OK. Now I understand. I come from the MS ACCESS world and between the VBA and query builder you (can) do a lot more with the data. You can do a lot with it in Bubble once you learn all the tricks, of which I still have a long ways to go. BUT, after coming from the ACCESS world I find Bubble a dream come true! I’ve needed to go web-based for years but didn’t have the time to learn the needed new skills. Once I found Bubble a few months ago and discovered its data features were a key component of the platform, and with the things you can do with the components without having to learn the (non-vba) code behind them I knew I’d found my tool to build what I’ve always wanted. (Well, rebuild is more like it.)

In the example you give with the cars and trucks, you CAN copy the schema’s. All you have to do is right-click/copy the cars table name and paste to get a copy of it that you can now rename it as trucks, and rename/add/delete fields as needed.

No tool is perfect, and Bubble has its limitations, but IMO it ADDS way more than it limits.

1 Like

Thanks for the replies so far.

Definitely, 8000 records is not large but it’s more and more looking like using an SQL connector is the way to go.

The database (and views) need to be able to:

  • store results for each game
  • compile and construct an ongoing ladder for each league on the fly
  • calculate an Elo-based rating system for each game
  • calculate season by season, league by league as well as overall results for statistics

The Bubble DB interface is great for a whole lot of things it seems but not for being able to generate these sorts of tables. I understand that my requirements may fall a little outside of what it’s meant for and that in the future the data management may be improved but for now, being able to construct all of this in an SQL database externally may be a better (and less time consuming) option.

Are there any sport-based examples of Bubble apps/sites around that do something similar?

Well, I’ll be. I did not know you could copy and paste in the data view by right clicking. That is good to know and alleviates one of the issues I have had in the past.

I am a huge fan of Bubble as well. My criticisms should not be taken as anything other than constructive in nature and born of a desire to make the product even better.

1 Like

@NigelG, you mention here that the schemas are available. Where/how can a Bubble app’s database schema be viewed and/or exported?

You can get it from the Data API “meta” path and/or the Swagger endpoint.

1 Like

Thanks @NigelG!

Do you know of any way to convert the db schema that Bubble outputs to a more traditional db schema chart along the lines of the image below?

There is a plug-in that does that I think ?

1 Like

Yep, you’re right…it’s Air Database Diagram. Thanks!

1 Like