CRM system from scratch #2

Last time I thought about users, their roles and how separate users from different companies to different branches.

Now, we continue our thinking on way to strong CRM.

Let’s say, we have small company, with few managers. So we need some director. And of course we should deal with clients. That’s three roles. Or two, if client shouldn’t login in system. In any case, we have business-level entity Client.

Client belongs to manager. That’s evolve in new vision of BusinessEntity: it can belong to some user (actually to UserInRole). So, here we define that BusinessEntity should be bound to UserInRole, let’s call it owner_id.

Entity BusinessEntity (id, owner_id, type)

Okay, that’s clear. Let’s think further. We have opened a one more department — so we need one more BusinessEntity —  Department. Each department have their own managers and own clients. That’s not exactly «owning», but more correct — it’s «structered». Clients are still owned by some Manager, but they are structured under specific Department.

Entity BusinessEntity(id, owner_id, type, structure_parent_id, structure_parent_type)

Advanced reader could probably note, that in real life could happen some «Trees» of any concepts. There can be manager 3rd level under manager 2nd level under top-manager, and they all could own clients. A tree of managers is what called hierarchy.  We actually don’t need seperate hier_parent_id, but we could make this property, to ease building business logic. Speaking simple language, hier_parent_id is equal to structure_parent_id if and only if structure_parent_type equals to object’s type.

So, at this point we develop some BusinessEntity concept. We are not yet sure about all properties it should have, but 100% sure that such concept must exists. So, Department, Client is some kind of BusinessEntities. What about UserInRole? It should be BusinessEntity too, because it should have ability to be structured. How we can generalize all this? I propose create concept BusinessEntityType and use type_id pointing at this entity in BusinessEntity instead of type.

Entity BusinessEntityType(id, name)

Entity BusinessEntity(id, owner_id, type_id, structure_parent_id, structure_parent_type)

Ok, going further. We can add now these records: BusinessEntityType(id=1, name=’Client’), BusinessEntityType(id=2, name=’Department’), BusinessEntityType(id=3, name=’Manager). We could create a few records of clients and departments, and place it under others in any way, even in not correct one. So, we need some way to determine rules and constarints for structuring our BusinessEntityTypes. Let’s say we want have two levels of departments, both of them can have managers, but only managers from 1st level of department can have own Clients; and only department 2nd level can have Clients;

Correct example:

  • Department 1st level
    • Manager of 1st level department
      • Own Client
    • Department 2nd level
      • Manager of 2nd level department
      • Manager of 2nd level department
      • Client of 2nd level department (common)
      • Client of 2nd level department (common)

Invalid example:

  • Department 1st level
    • Manager of 1st level department
    • Client of 1st level department (common, invalid record)
    • Department 2nd level
      • Manager of 2nd level department
      • Manager of 2nd level department
        • Client of 2nd level manager (own, invalid record)
      • Client of 2nd level department (common)
      • Client of 2nd level department (common)

Entity  BusinessEntityTypeRule(id, type_id, parent_type_id, parent_type_hier_level_min,  parent_type_hier_level_max, entryAllowed)

That’s very simple way to specify such rules:

  • Department can have parent department, if this parent is 1 level of hierarchy
  • Client can have  parent department, if this parent is 2 level of hierarchy
  • Manager can have  parent department, if this parent is 1 or 2 level of hierarchy
  • Client can have  parent manager, if this parent is 2 level of hierarchy.

Did you see the problem? Yes, the problem is «Client can have  parent manager, if this parent is 2 level of hierarchy» and similar cases. When we have hierarchy (means all of elements are the same type), then we easily could specify any correct rules. But what if we have not hierarchy, but more complex system? How we could separate rule for manager 3rd level, which could be this: Department -> department ->manager or this Department -> topmanager -> manager ? Short answer is: that would be very complicated.

Let’s review  BusinessEntityTypeRule then. to specify some rule, we should check and somehow store whole tree-path. So probably good way would be make BusinessEntityTypeRule structured itself!

Entity  BusinessEntityTypeRule(id, type_id, parent_type_id, parent_type_hier_level_min,  parent_type_hier_level_max, parent_rule_id, entryAllowed)

Current scheme required that or only parent_rule_id is set, or parent_type_id, parent_type_hier_level_min,  parent_type_hier_level_max is set. So, we can now rephrase our rules in new way:

  • Department can have parent department, if this parent is 1 level of hierarchy — this is Rule1
  • Client can have  parent department, if this parent is 2 level of hierarchy
  • Manager can have  parent department, if this parent is 1 level of hierarchy — this is Rule2
  • Manager can have  parent department, if this parent is 2 level of hierarchy
  • Client can have  parent, desribed in Rule2.

At this point I’ll stop. In next article I’ll elaborate how we should store BusinessEntityType fields, and how system should allow it’s editing.

Comments appreciated! :)