I’ve been stuck for quite some time now with choosing the best structure for two types in my database.
I have a CRM that manages Clients and Leads. Initially, I had created a type Client and a type Lead.
The type Lead has basic information such as First Name, Last Name, Job, and the works. The type Client, however, has all the fields that are in Lead, plus quite a few more. I’ve been struggling with whether I should have them all in one table (a type called Person) and add a field “Type” to differentiate between whether the Person is a Client or a Lead, or keep them as is because of a few reasons -
I will be converting Leads to Clients within the app, hence, I would be having duplicates of the same info in both tables and I would need to create an extra field inside Client to link each Client to the Lead that was converted to it.
The conversion from one type (Lead to Client) would be easier if it’s all in one table since it would simply be a switch of a field inside the same table.
It feels like it might be slightly easier to manage different statuses of both the Lead and the Client if it were all in one table. However, I might then have different statuses for each type if I were to go with two types.
If I have them in two tables, if a user deletes a Lead that was converted into a client, I’m worried about the repercussions of such action since I’d want them to be linked.
I’m quite lost and I apologize if my confusion is translated in unclarity of my points mentioned above. As far as I’ve learnt about databases back in the day, I should supposedly opt for having two databases so I wouldn’t have too many empty/null values inside one table - which would be the case if I combine both - and to not increase the cost of the search function having to search for too many rows for values (Given that the leads to client ratio would be something like 10/1). Yet, I’m still uncertain and I am not sure what’s the ideal course of action.
Notwithstanding any dB model you choose … CRMs biggest “plague” are duplicate records. This is just the nature of the “beast”.
I have dealt with CRMs in different platforms and have also built CRMs with no-code tools. I can 100% recommend that you only use one data object for your main CRM entity (person or company) and track its progress along its pipeline spectrum (lead, prospect, client, etc).
Like mentioned by @cmarchan you should have one entity for ‘person’. I would make sure that has all the fields necessary for being a client or a lead, and no more than that.
Then I would have a type client and a type lead each would have a related field to the single entity you created for ‘person’. As you move the ‘person’ through the pipeline from lead to client you don’t need to make updates to multiple data types on the fields that are shared (ie: every field that is on the ‘person’ because it is used for ‘lead’ and ‘client’)…then if the ‘person’ after being a ‘lead’ becomes a ‘client’ you can add the fields associated on the ‘client’ data type.
What you do is simply create a ‘client’ data type and relate it to the ‘person’ data type and then add the fields specific to client.
Not sure exactly the reason that you would need to keep a ‘lead’ after they have been converted to a ‘client’ though as once a lead becomes a client they are no longer a lead and instead a client. So personally, I’d set up the system to delete the ‘lead’ data entry as soon as I create the ‘client’ data entry because my ‘person’ entry is not changing at all.
The ‘lead’ and the ‘client’ are the same ‘person’ the only thing that really changes is their designation as a ‘lead’ or ‘client’.
Always lots of ways to structure a database model based on how you want your app to function.
The reason I want to keep the lead entry is because I want to be able to calculate conversions from leads to clients, and if I delete the lead after the client is created, then my calculations would be wrong. Would you still keep it in that case or delete it still and do that some other way?
Also, your solution would then mean having three data types: Person, Lead, Client. Is this for the purpose of avoiding empty/null values? Otherwise, would you just have one data type ‘person’ and differentiate using a field inside of that type?
@cmarchan Would you go at it by having one type (Person) and track its progress through a field within that type, or would you do it the way @boston85719 recommended, which is by having three types: Person, Lead, Client, and linking the latter two to Person?
I would delete it and do it another way…but I don’t know everything about how you want the app to function so part of database structure is creating to fit your personal use case. For me, I’d assume a client is a converted lead…so any client means a 100% conversion rate on that person. But you might be expecting companies to input clients directly so of course that wouldn’t translate to a conversion…either way lots of ways to calculate that without needing to save the entire data type entry.
I’m not recommending to link the client or lead to person…i’m recommending linking person to the client or the lead data type. So on client their is a person data field and on lead there is a person data field but on person there is no field for either client or lead.
For a straightforward crm, I would create a data object called “deal” with an options set for each of the “pipeline” stages. If you want to enable the creation of stages you can have the stage as a data object. But if you have a defined process it is usually better to have descriptive-lists of a data object being an options-set for performance purposes.
So … when an entity (i.e. a person or a company … the main data object whose everything happens for) finds itself in a deal you are tracking the progress of the deal as a proxy for what is happening deal wise for that entity.
I get that! However, since you’ve implemented it previously, does a problem arise with this method as a result of the empty/null fields? Does it not affect performance having too many of those fields?
It is great that you are investing much time upfront so that the app comes out as best as possible.
The structure I suggested is not complete. It is just one way to do it and then the rest needs to be expanded on.
In essence, you are facing the challenge that we all face when building an app. To create a dB structure that is as optimized as possible and provides for scalability if needed. For this I refer you to the following material which is very useful!
I know I did not provide you with a straightforward answer. To get into the details of a specific course of action on how to build an app plan, takes much more time and insight into what the vision for that app is. So, I prefer to share app building concepts that have and continue to help me when building.
Hope this helps a bit and best of luck with your project!
