How to structure database for this application?

raptorflapjacks · 2024-12-06T13:57:34.831Z

Hey all.

In short, I’m building a sales funnel for a niche industry. I’m looking for clarity on how to structure my database to allow my users to categorize their leads based on existing & custom categories, grouped into 4 main groups.

A user will categorize each prospect with 4 different groups: 1) Prospect Origin, 2) Prospect Destination, 3) Prospect Type, and 4) Sale Payment Structure.

I intend to ‘pre-load’ their interface with a handful of common categories for each of these groups, but I also want the user to be able to define their own categories, as each user may have a different sales payment structure depending on where they work, for example.

I’m hoping to mirror how Monarch Money lets users group and categorize their budgets. As you can see in the image, they have Groups such as ‘Housing’ and ‘Bills & Utilities’, and then categories within each with the option to create additional categories per each group.

image374×760 13.1 KB

If ‘Create Category’ is selected, an input is presented with a dropdown which selects the appropriate Group for the new category. There is a SAVE button on this popup which I assumes adds that new Category to a list. There is also an option to delete a Category if not needed.

image627×299 6.66 KB

My question that I’m trying to wrap my head around is, how can I properly structure my database to allow for this functionality? Each user will add in Prospects, and that Prospect will be tagged with exactly 1 Category from either 3 of the 4 Groups, or just the 4th Group. My plan is to then generate a Sankey Chart which shows the flow of prospects from Prospect Origin to Prospect Destination and further subdivide by Sale Payment Structure.

This app is for financial advisors tracking new assets coming into their business, or tracking their insurance pipeline. An example of how this would work is a user would identify outside assets an existing prospect or client has with a different firm (Prospect Origin Category = 401k), identify where that is going when it is moved to their branch (Prospect Destination Category = Traditional IRA), and identify what type of program that money is moved into (Sale Payment Structure = Fee Based Program).

The 4th Group, Prospect Type, would be used to identify existing insurances among clients or opportunities for insurance sales. An example is Prospect Type = Term Life Insurance.

While I know I can pre-populate all of these categories, I’d like the user to be able to define additional categories, or to bring greater resolution to their business by manipulating how they can work with the app. For example, they may want to change the Prospect Origin Category = 401k into three categories, Pre-Tax 401k, Roth 401k, and After-Tax 401k. Additionally, my users will be working at different firms so there is a need for them to self-identify categories for the Sale Payment Structure Group as that will differ firm to firm.

How do I go about structuring this in my database? Should I be using Data Types? Option Sets? And once that is established, how do I present this within my ‘Categories’ tab within Settings? Do I use a repeating group? Any and all help will be appreciated

Thank you,
Raptorflapjacks

stefano · 2024-12-06T21:38:08.881Z

Hey there.

I would recommend you start drawing. A good diagram is great to help solve this issue.

I did something that could be analogous to your app, but for equipment maintenance. I used connection tables for that. This will take a bit of a toll on the writing side but make reading much easier.

What you do: for each connection you creat a new data type. For example:

You have Leads, then Categories, then Stages.

You creat data types for these three and then also:

Leads X Category
Leads X Stage

That way you can easily add new categories or stages.

raptorflapjacks · 2024-12-14T19:05:21.721Z

This was very helpful, thank you! I was able to solve the problem and it wasn’t as complicated as I had expected it to be.