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.