Database Structure - Need Help Visualizing

I’ve built a CRM for in bubble and have the functionality working. However, this CRM was my first project on Bubble and I am not a programmer. I am planning on re-building it from scratch, this time focusing on database structure and privacy rules. I’ve learned so much over the past 6 months or so, that it will be more effective just to start from scratch.

The CRM is for a mortgage business and I am only currently dealing with the main Data Types (not fields at the moment). I know this is hard to do without understanding the specific needs or seeing other fields, but I would appreciate any input, suggestions, etc.

Looking at the image below, the names are Data Types, and the lines represent which Data Types are connected to one another. Does this seem like a logical layout?

Hi, I suggest you try drawing it out in a more standard database diagram style – where you have a box for each datatype, and within each box you list the datatype’s fields, and then you use lines to show when a field in one datatype is connected to another datatype.

It would help with your planning, and also allow others to understand how you’re approaching it and give you advice.

I will work on that, sounds like good advice. Do you know of any examples of such a diagram?

Something along the lines of this is fairly typical… database design - Adding price field to both products and order_details tables - Stack Overflow

(Though note Bubble is a little different in that you can just connect records directly without having to specify a client ID)

If you google database design you’ll get a bunch of images, and may find a style that works better.

I find that some diagrams in data modeling by database experts complexify what’s actually pretty simple. The key (to me) is to show the fields in each datatype, and then to show when a field is pulling from another datatype.

The goal should be to “normalize” your data, such that data is not repeating in your database.

1 Like

Thank you. I will work on updating it to show the extra details and fields. I’ve read up on things, watched videos, etc., but I want to start out on the right footing this time. I appreciate you guidance.

Ok, here is my first stab at a more comprehensive visualization of the database (see image below).

This is not complete yet as I don’t have all the fields showing. I’m hoping this is enough to get a general idea if I am moving in the right direction.

The blue heading is the data type. The yellow highlighted areas are the data type’s ID. The blue highlighted areas are linked data types. Most of these data types will expand with more fields once I start putting things together further.

Any additional guidance on where I should go from here?

Hi, a few comments…

  • I assume that (hopefully) you aren’t actually putting an “ID” in a text field when you want to relate things – that you are just using Bubble’s ability to set a field as another datatype.

  • You have a lot of different types of “people”… Underwriters, Realtors, Service Providers. If any of these people need to have login capability, put them under “User”. You can distinguish them by a type field that uses an option set. This also allows a User to have multiple roles. And even if some users don’t need to have login capability, ask yourself if there is a strong reason why you can’t just put any “person” under User.

  • I wouldn’t make “Permissions” as a separate datatype unless there’s a specific reason to do so. You can just create a “Permissions” field in the User datatype (and if you want the ability to rename permissions, you can use an option set to create the options). Or, if the permissions are driven off the User type (Underwriters, Realtors, etc.), then a permissions field isn’t necessary since you can just run the permissions off the User type.

  • Seems like you need to have a “Company” datatype since your Underwriters, Realtors and Service Providers all have a “Company” field.

  • Not really sure what “Prospects” is for and how it relates to the other datatypes. If a person may or may not be a “Prospect” then you could just create a yes/no field to mark someone as a Prospect.

Hope this helps. Big caveat that I’m not in a position to endorse someone’s database setup or give a definitive answer on the best way to do something without knowing a lot more about an app’s needed functionality and privacy requirements, and that’s a bit more involved than I can get via the forum.

I appreciate your time and information/suggestions. It is helping me focus in on how the database structure should look. It is still hard for me to visualize the structure without using it in the editor. I think I may need to get the database setup as best as possible, and then tweak it as I start to buildout the site.