Relational data structure - what am I missing?

casheets123 · 2020-07-08T17:37:14.663Z

Hoping that @boston85719 will stumble across this thread and sort me out

This is long overdue, but I just started looking at relational database structure today (oddly haven’t had a need for it before). I’ve read dozens of threads on the forum about it and watched several videos. And I’m still at a loss.

Creating the “link” is done by creating a “Datatype B” with a type of “Datatype A”. Now “Datatype B” has access to what’s stored in “Datatype A”, supposedly.

In my idea of linking, that means I can simply call for information that is stored in “Datatype A” through a repeatingGroup displaying “Datatype B”. so the following search should yield the same result;

Search for DataTypeAs’s Points

and

Search for DataTypeBs’s PointsLinked’s Points
(PointsLinked is of Type DataTypeA)

This of course does not yield the same result, because there is no “Link” to the existing entries in DataTypeA. And The second search will of course return nothing, since the PointsLinked field is empty in the Database.

To remedy this, I found one, out of all the videos I watched and threads I read that actually populated the linked datatype, in this case “DataTypeB’s PointsLinked”. In that video, the person simply creates the entry for DataTypeB at the same time as he’s creating it for DataTypeA.

Workflow
Step 1. Create a new thing > DataTypeA > Points > Input’s value
Step 2. Create a new thing > DataTypeB > PointsLinked > Result of Step 1

I suppose at this stage there could be some behind the scene pseudo linking going on.
As from here on, I can not add points to PointsLinked, but I can delete value, which I then assume is simple breaking this “link”? I am also able to make changes to DataTypeA’s points and that will be reflected in DataTypeB’s PointsLInked without a second step needed in the workflow to alter
DataTypeB (as I had to do when creating the first entry, what I assume is creating the link)

So where does this leave me?

From what I gather, a “linked” datatype. a data type of a data type, a so called inception datatype if you will, does not go and look for what already exists inside a secondary/linked data type.

I.e. Hey Bubble, I want you to find what’s inside the Points field of DataType A, through the eyes of DataType B’s PointsLinked

The relation/linking between the two datatypes is not created when you create a datatype of a second datatype in the Bubble Data tab. Instead the Linking of the two happens in the workflow when you create a Thing? So each thing you create that you want to be linked will require a second workflow step (or multiple if you have several links) to create the link between two entries?
From what I can tell, the two entries has to be created at the same time? So if you are well into development and then need to link a new type of data to a pre-existing type, you’re SOL?
(I’m going to assume I’m just misunderstanding this)

So, @boston85719, or anyone else with a great understanding of bubble’s relational data structure, please, please, tell me what it is I can’t see,

boston85719 · 2020-07-08T19:16:05.426Z

I don’t really understand what it is that you are trying to do in terms of relating “Points” and “PointsLinked”

Basically the relationship of creating “DataType A” and “DataType B” where “DataType A” has a data field that is of the type “DataType B” means that when you search for “DataType A” you can pull up all the information stored in that particular “DataType A’s Data Type B”

So if you have a datatype “User” and data type “Contact Details” you put in “Contact Details” some data fields like phone, email, address etc.

Then on the “User” data type you put a data field and make it of type “Contact Details” and maybe label it ‘ContactDeats’…then when creating a ‘User’ your next step would be to create ‘Contact Details’ and the third step would be to ‘make changes to thing: result of step 1’ and for ‘ContactDeats’ data field put ‘result of step 2’…

So when you search for that ‘User’ you can pull up ‘Users’ ContactDeats’

Check out this thread

Sorry-Need to Revisit-BEST Way to Retrieve Data-Speed Concerns Question

Ok, so I have been using Bubble for quite sometime now. And I feel like what I am seeing right now has turned most things I’ve come to learn about Bubble in concern of speed for retrieving data has just been turned on its head. From different threads I’ve come to be of the understanding that using related data types is faster to retrieve data than to search the data type alone. For example, I have ‘Analytics’ as a data type. It has fields (share date, ID (text value representing another data t…

casheets123 · 2020-07-08T20:45:36.931Z

Yay Boston, you found it! haha.

Ok, well, that sounds like what I outlined above. Maybe I’m not misunderstanding it then?

In my example DataType A would be your “Contact Details” and DataTypeB would be your “User”.
My Points would be your “Phone” and PoinstLinked would be your “ContactDeats”, right?

the third step would be to ‘make changes to thing: result of step 1’ and for ‘ContactDeats’ data field

I think this is what tripped me up at first. Because If I understand it correctly, then, this is what actually links the two together. When you do this, you’re saying “hey, this contact information should be linked to this unique user”

I suppose when you have two already existing entries, you would be in trouble.

Say, you already have a User named “Boston”, you also already have a Contact Details
entry with the two fields “Phone: 85719” and “address: Bostonville”.

If I now go and create the field “ContactDeats” under user, it would have no meaningful connection to the user “Boston”. I mean, how could it?

That thread is actually what got me looking at linking data types, since I wanted to run some speed tests myself to see

Edit:
And then the upshot of this is that A is faster than B.

A
Search for User’s ContactDeats

B
Search for Contact Details with a constraint of User

deadpoetnsp · 2020-07-08T21:45:26.453Z

[Edited] Yes, you will have to handle both creation and deletion of “inceptions” linked data–because it is (next to) impossible for Bubble to efficiently know what links you had in mind. I can give you a concrete example. In my app, I have a comment type, and a user type. The user type also includes “comments” which is a list of comment type. When a user creates a comment, (1) I create a new thing of type comment, then (2) add the result of step 1 to that user. When deleting the comment, delete the thing of type comment-it will get removed from the list of comments as it no longer exists, as noted by @boston85719. My app also has more complex links using similar logic (for example, messages related to a particular post get added to the creator and recipient and to the post; similar for user notifications). I also have links between three or more types/tables so to speak. It all works fine.

It does add some more work on your end when CRUDing, but it significantly reduces the need to “Do a search for” and improves page load and performance.

Also: do not load all RGs and groups with data when the page loads. Only load the RGs that need to be loaded in response to events and user actions. For example, if you have a mailbox. Dont load the mailbox. Instead, let it sit as an invisible shell mailbox. When the user hits the mailbox icon, then load the mailbox data and make it visible. Of course, there is some trade off between (1) initial page load performance and (2) response to user events performance.

deadpoetnsp · 2020-07-08T21:49:27.633Z

casheets123:

From what I can tell, the two entries has to be created at the same time? So if you are well into development and then need to link a new type of data to a pre-existing type, you’re SOL?
(I’m going to assume I’m just misunderstanding this)

This has not really been a problem as I continued developing my app and extending the data types. I have launched an alpha and have been changing the data and incepted data and links, without much pain. What happens is when you create a new link (say a new link between message and post) some previously created data links may get orphaned, but you can “gracefully” handle such things in execution. You can also create workflows. For example, we didnt have notifications at launch. We then added notifications later, and had to write a quick one-time workflow to send out pooled notifications from the past. But of course, all this is fine in alpha/testing, probably not in full-deployment.

casheets123 · 2020-07-09T02:11:19.157Z

Thanks @deadpoetnsp and @boston85719

I’ve had some more time to muck about with it today and ran some speed tests and it’s a great improvement. I went from 2-3sec load time to 0sec for the slowest part of my app. I was already planning on running a master-admin-workflow for that data type anyway to sort something else, so I will roll this into it as well.

boston85719 · 2020-07-09T07:13:11.533Z

deadpoetnsp:

When deleting the comment, first (1) remove the comment from the comment’s “creator”'s comments (each type automatically has a “creator” field - so there is no need to search for the comment’s creator) and (2) delete the thing of type comment.

You don’t need to do that. You can just delete the thing of type comment and because it no longer exists, it will be automatically removed from the list of comments on the user data type.

deadpoetnsp:

Also: do not load all RGs and groups with data when the page loads. Only load the RGs that need to be loaded in response to events and user actions. For example, if you have a mailbox. Dont load the mailbox. Instead, let it sit as an invisible shell mailbox. When the user hits the mailbox icon, then load the mailbox data and make it visible. Of course, there is some trade off between (1) initial page load performance and (2) response to user events performance.

Try putting a RG that is set to fixed cells with number of rows to 0 and columns to 0 for your mailbox data. On page load it does not affect performance, but you do get count of items. You can use this to show a user the number of new messages by referring to this RG.

I use this method on all my pages. When I have a page searching a data type with 2,200 entries, I can get the count to display total results…it has no affect on page load speed, and allows for immediate access to counts for pagination etc.

I did some tests on this and it is the fastest way (at least from my perspective) of getting information to load quickly…I also create other RGs of fixed cell, 0 row 0 column and reference the original as the datasource and use those for filtering and sorting features.

boston85719 · 2020-07-09T07:19:06.129Z

casheets123:

Say, you already have a User named “Boston”, you also already have a Contact Details
entry with the two fields “Phone: 85719” and “address: Bostonville”.

In this situation there is no meaningful way to recognize whose Contact Details they are so, in practice you’d just have a bunch of Contact Details “floating” about your DB with no way of utilizing them. In practice I am sure there would be some way to recognize which users Contact Details they are.

Usually the issue is that somebody create Contact Details and had a data field of type text, which they use the users username. In that situation, if the app creator wants to now make a ‘live link’ by creating on the ‘user’ data type a data field of type Contact Details they can just simple make a recurring workflow that searches the Contact Details text field to correlate to the correct user and then make changes to the user by taking the found Contact Details and adding it to the ‘user’ data type.

deadpoetnsp · 2020-07-09T12:11:12.461Z

Thanks! I’ll edit my deleting step above so that future readers don’t perform unnecessary steps

james4 · 2020-07-09T16:22:00.634Z

Great post. I’ve been reading a bunch of your topics lately on database structure. This tied it all together nicely. Thank you!

casheets123 · 2020-07-09T18:27:07.506Z

Matthew McGowan:

In this situation there is no meaningful way to recognize whose Contact Details they are so, in practice you’d just have a bunch of Contact Details “floating” about your DB with no way of utilizing them.

Yes exactly, which makes perfect sense.

Matthew McGowan:

Usually the issue is that somebody create Contact Details and had a data field of type text, which they use the users username. In that situation, if the app creator wants to now make a ‘live link’ by creating on the ‘user’ data type a data field of type Contact Details they can just simple make a recurring workflow that searches the Contact Details text field to correlate to the correct user and then make changes to the user by taking the found Contact Details and adding it to the ‘user’ data type.

Yup, haha that’s how I’ve solved things in the past when having to restructure my DB. I’ve been lucky enough to always have had a field I was able to cross reference between the two datatypes.

casheets123 · 2020-07-09T18:30:19.130Z

Indeed, so have I. And discussing it here have summed it all up in a great way. Glad someone else also got something out of it as well

Cheers to @boston85719 and @deadpoetnsp

system · 2020-09-16T17:37:19.834Z

This topic was automatically closed after 70 days. New replies are no longer allowed.