Data Model Suggestions

Looking for some feedback on my data model and how to correctly configure this in Bubble.

Do I set Assetname to text in the dbo_assetinformation table and then set Assetname in the other tables as a “list” pulling from dbo_assetinformation?

Hey @jasonmeszaros - if I understand correctly, you have a thing in your database (Assets). And this thing has approximately 7-8 different things that are associated with it (ie. AccessReview, AssetInformation). Or, is dbo_assetinformation meant to be the primary table?

Within Bubble, to create an association for a specific database object type, you create a new field (in this case on the thing Assets, that has the defined type of the other table you’re trying to reference, ie AccessReview). This by default makes the association with the unique ID of the other thing. It can be a single thing or a list of things. There’s no need for text of Assetname if you’re creating a defined thing on the corresponding table.

Let me know if I can clarify. Hope that answers your question.

The manual has some good documentation.
https://manual.bubble.is/working-with-data/the-data-tab.html

dbo_assetinformation is meant to be the primary table where the value is stored. I would like to use the Assetname field as the unique ID that connects the other tables (dbo_accessreview, dbo_vulnerability, dbo_securityinformation, etc).

My assumption is I create a field type in each table (dbo_accessreview, dbo_vulnerability, dbo_securityinformation, etc) that references back to dbo_assetinformation/Assetname.

Am I on the right track?

Ah, makes sense now. (Missed reading the PrimaryKey on dbo_assetinformation). In this case, when you create the new database fields on the other tables (dbo_accessreview, dbo_vulnerability, dbo_securityinformation, etc), dbo_assetinformation is the unique ID that you reference. (The actual name of the field is irrelevant).

(You may already know this, but sharing for others that see this in the future). Bubble’s database automatically creates a unique ID for every thing created in the database. It also records the created date, created by and modified date. (Meaning, it is redundant to create any other fields, unless there’s a specific need in your app).

What you want to keep in mind is where the association is stored. For example, say that I have an inventory app, where I have Stores, Departments, SKUs and Inventory (Physical) Items (SKUs).

Think from the user case of doing search for the Physical Items. Does it make more sense to save the Item to the Store, to the Department and to the SKU individually or can that be saved as an attribute on the physical item (Ie. where you have the key value of Store, Department and SKU as stored items). My bias is towards saving these attributes on the Physical Item, not in each individual place, given that I would need to (potentially) update a List in three other areas every time I make a change.

1 Like