Using relational database structure and effects on storage and search filtering

[boston85719]

I am need of some advice, opinions, and any other comments people may have on the subject. Any insight into potential costs associated or actual improvement on search filtering speed are going to be helpful.

I am in the process of creating a marketplace app that enables B2B product sales.

My products category data table ( data type ) will look like this

I am using the parent_id and grandparent_id for excluding the need for a sub-categories table.

My products table ( data type ) is related to the categories table which is a data field in the products table.

I want to create a “many to one” or a “many to many” relationship by having the products data type to be a data field on the products categories data type. This is where I am at a cross roads in my decision making and what the possible advantages / disadvantages of the different choices are.

If I were to create a “one to many” relationship I would create a data type as a list of products in the categories data type…like the first image shows and the below

If I were to do this, that would mean I’d have products that would be listed on more than one category because I have parent and grandparent data fields to create the sub categories.

For example, if I have a category set up like below

I would have a list of products that have the category of Cereal and that would naturally have products that are on the list for category Oatmeal and on Wet…

So, on a third tier category such as “wet” the products on that list would be present on three lists of products on three different categories.

My thought and worry is that a relationship between data entries means that the entry is stored multiple times on my database and would therefore add to overall costs of maintaining and operating the platform, since data storage would increase.

Or am I wrong in that thought and the relationship is actually only “pointing” to the data entry in the data table and doesn’t actually create another copy of it, so just “pointing” to the data entry ( ie: having the relationship ) wouldn’t increase the costs of storage as I think.

My thoughts on the benefits of this strategy would be to make filtering and returning search results faster, as I could as the user selects the different category filters narrow down my search by saying “Do a search for selected category’s list of products”.

The drawback of not placing a list of products onto each category is that during search filtering I’d have to create a lot of conditionals to reference the parent id or grandparent id of a category.

Overall, I’m wondering about experiences people have or knowledge of the affects on data storage of using a relationship ( such as those for a products list as data field in categories data type ) and whether or not I’d actually see any kind of performance improvement during filtering search results.

[boston85719]

Really curious if anybody has any insight on the topic. At this stage I am trying to make a large number of conclusions in my D.B. design around the relational between tables and the need or benefit of having them. Really the decisions are based on performance of search queries and if the added speed of a search is worth what the potential storage costs are for having the extra data fields that are foreign keys to related tables ( data types ).

@eve or @peterj would you maybe have some resources you could point me to?

[NigelG]

@boston85719

My thought and worry is that a relationship between data entries means that the entry is stored multiple times on my database and would therefore add to overall costs of maintaining and operating the platform, since data storage would increase.

Or am I wrong in that thought and the relationship is actually only “pointing” to the data entry in the data table and doesn’t actually create another copy of it, so just “pointing” to the data entry ( ie: having the relationship ) wouldn’t increase the costs of storage as I think.

Think of it more like an object-relational database.

The entries are not stored multiple times. But they don’t really act like a foreign key and “point” either.

When you “load” the child record by doing a search then the parent will get loaded as well, so when you refer to child’s parent’s name … you don’t have to navigate the relationship again. It is already loaded.

So Bubble is making it faster for you by pre-fetching data from relationships. Or they are already embedded like a document database. It doesn’t matter how it works, as that is hidden from us.

Quite how far Bubble pre-fetches related data is an open question (you could conceivably load to the whole database … but that doesn’t seem to happen). But we know from testing (crudely … pulling the plug on the internet and then running an action) that it happens.

In designing your database, I always tend to say this. Think about the big nasty query on your biggest nastiest page. And design the database around that, or rather the relationships. Make the query as simple as possible, and it will run quickly. When you end up with lots of Advanced filters (which don’t happen on the database) that is when it can get slow.

[boston85719]

Thanks @NigelG

Would you say then that the use of the relations and having a data type be a data field on another datatype

ie: Data type - City ; Data Field = List of Stores

Data Type - List of Stores; Data Field = City

Would not do much to affect the cost of storage, since like you mention the entries are not stored multiple times

And

that ultimately my search queries would be faster due to the relationships between data types since “Do a search for: “Cities list of shops””

would be faster than

“Do a search for shops : constraint = city = “this city name””

And that in respect of the big nasty query on my biggest nastiest page I should create as many relations in the database as possible and be concerned with an increase in storage costs?

[eve]

Hello!

One-to-many-to-many-to-many performance

Let’s be nice, please If A has a list of B, whenever you load A, you load all the ids of Bs in that list. We may change this eventually but no short-term plans to do this. We don’t actually load the rest of the data for the Bs unless you actually reference a property on the Bs. (I think! If you can build a simple test page that demonstrates otherwise, submit it as a bug) If you’re expecting to have a list > 100 items, I always recommend establishing the relationship in the other dire…

This post by Josh might be particularly helpful here. In general, if you have a performance-related question, it’s always worth reading through Josh’s performance-related posts (though be careful with the old ones, some info from 2016 may not still apply )

[boston85719]

Thanks @eve I have read through the performance thread in the past, but didn’t see anything related to the cost of storage.

Is there any affect on storage costs if I am utilizing a lot of “many to many” relationships, or does the amount of storage necessary for these relationships equate to a minimal amount; meaning that I should focus more on creating the relationships for improving performance as it doesn’t actually create a lot more data to store?

I am just confused on how bubble does what it does in the background…most of my understanding about database design has come from resources I’ve found online which are written for programmers or coders and I felt like the takeaway there is that the entries with relationships get added each time there is a relationship created.

For example, if I have a data type with 50 data fields which lets say equals 50 storage “points”…would it mean that when using bubble and having a relationship to another data type I would then be having 100 storage “points” ( ie: a relationship so 50 data fields*2 ) or would I have basically 51 storage “points” ( ie: the relationship is only one data field in the related data types “table” )

At this point, I think I am fairly clear on the performance side of things, but still in the dark on the storage cost implications.

[eve]

Unless you’re on a Dedicated plan, your storage costs are only affected by the number of files uploaded to your app (you can check these out in your File Manager tab). Database entries are not counted towards your storage usage and are covered by your plan, so you can actually build a very extensive database with a very large number of entries at no additional costs.

Hope that helps!

[boston85719]

That does help thank you…last question, by files that would include images and files that are uploaded?

[eve]

That is correct - any files that are in your system count towards your storage.