Hi Bubblers, I’ve read quite a bit already on the differences between Bubble’s database and to consider it as a (no-SQL) design rather than SQL or a typical relational database (if this makes sense - I am not a database expert). I have friends that are software engineers, but when I talk to them I get more confused as I’m pushed down the path of normalising my data, where when I read the forum it seems like this isn’t as advantageous as it sounds and that realistically I should be focusing my database on search return results rather than how data is written in the back end.
I’ve completed a few different scenarios for one small section of an app. Essentially, I want to have “portfolios” of “projects”, with each “portfolio” having a “Team” of users that can see the portfolio and the projects within them. I would note, that another datatype in this which I haven’t included in the example is the user info and the project info (project being most relevant as I assume if a user didn’t have permission to view a project, they would see a project in a portfolio but be denied access).
My current database design is this (which I felt was very simple and would have been quick for bubble update and search - but after looking further, I realise any result for “what user is part of what project” would require a search of the entire database.
So, I moved on and created a second scenario which now I believe meets what is termed 3NF and may be sufficient for all of my queries…
Otherwise, I considered a few other options for 4NF and 5NF, but now I’m not sure if I am designing like a traditional database and making this more complicated than needs to be?
How data is written and how searches are made - both of them are equally crucial for the application to perform best.
If i understand your case correctly, you wanted to have a list of Portfolios, where a single portfolio can have multiple projects. But why do you need the team table? Do you want to restrict the view access of the portfolio and only make it visible to people in the team which is assigned to the project?
Yes, spot on. Each portfolio will have a set of projects in it and also the portfolio will be viewable only by those that are in the team for that portfolio.
I’m trying to work out what will be the best for performance as from what I understand, bubble search action “do a search for” is a slower way of finding data than displaying an items thing which is downloaded to the page when loaded.
Another option I did think of now was also to go back to my original design of this to easily see what portfolio has what projects and team…
But, my thinking is that for the current user who wants to view their portfolio and projects in it, as well as team, the repeating group will need to do a search for portfolios and then filter by those with current user in the team.
To speed up that search I thought maybe the data then could be saved in two locations as below:
But, then I understand any time I want to make an update to who was in a portfolio I’d have to change two things, the portfolio team members and also the user being modified’s list of portfolios (and if there were multiple people being removed it may become more difficult, or simpler if it was made to be one at a time and a button sent the data of the user being deleted to another group/button and then that thing was deleted).
We use this technique often and have more generally moved to this cross-reference approach of storing data in both reference tables when searching is a key requirement. Yes, it is more difficult to maintain, and yes it goes against traditional database design, but with Bubble it just makes sense in a lot of scenarios.
We used to do this decades ago, but never really went beyond 3NF even in banking apps.
Anything beyond 4 is purely academic.
I don’t see much correlation between normal forms and Bubble search speed, in fact I would guess it is quite the opposite.
We were taught denormalisation at the same time.
Bear in mind that this is from the days of limited storage and focused ‘transactional’ databases. You would then take your data and transform it for datawarehouses (data marts, data lakes, whatever) into structures like star schemas, snowflake schemas, cubes…because 3NF databases are pretty poor at being queried.
Arguably Bubble is not even a relational database as it doesn’t allow keys, and the ‘lists’ construct has more to do with object or document databases.
Not really sure why you would want to have a list of IDs of anything in bubble. Unless you are forced externally.
Denormalistion for search purposes is perfectly fine. As is putting references at both ‘ends’ of a relationship.
So yeah, I think you are overcomplicating things by doing all this normalisation.
@NigelG, really appreciate your clarity on this - I did think that I was overcomplicating it (it was only when I showed someone in IT what I was doing that I became confused as they told me what I was originally doing was wrong and needed to ‘normalise the data’… I didn’t know what that meant so have gone full circle - but at least I now know a little more …
…