CRM system from scratch #3

Last time I defined such concepts: BusinessEntityType, BusinessEntity, BusinessEntityTypeRule.

So, by operating these concepts, we can define almost any business-system. But we still haven’t discuss how to store data. For example address of Client, phone number of Manager, names, last names, all data.

Almost all CRM are using databases for storing data. For storing data for each entity type I’ll use table in database. BusinessEntityType should be extended:

Entity BusinessEntityType(id, name, tableName)

In this way we can define for each type of BusinessEntity in which table data should be stored. Any DataBase Architect/Analyst can create proper tables, according to business models.

Let’s add a few more concepts. As I said before, we have Managers, Clients. We are working somehow with Clients — we should have entities Contracts, Invoices, Payments. Contract may be active, canceled, scheduled, ended; Invoice can be new, payed, canceled, nullified, partially payed, over-payed; Payment can be pending, approved, finished, canceled, returned. All of this represent a new entity:

Entity BusinessEntityStateType(id, name, business_entity_type_id)

With this concept, we can now define for each BusinessEntity a required set of states. But what if some BusinessEntity have two or more independent states? For example, color and weight, or any else?

Entity BusinessEntityStateType(id, name, business_entity_type_id, table_column_name)

With only one additional table_column_name, we created a way to any entity have as many states as we need. (If you like creative and abstract thinking, you should notice, that there is no difference between «state» column and any finish-state column; so BusinessEntityStateType could be used a bit wider, and renamed to AvailableColumnValue.)

Okay, we now can define business entities, we can define some available states for each entity, we can specify for each state all values, what’s next?

Many of system operating by CRUD ideology. CRUD — is 4 freedoms, Create, Retrieve, Update, Delete. For managing impersonal data in database it’s surely enough. But it’s not enough for me, in this CRM system.

I want define a new entity, named BusinessAction. It will be not complicated entity, but with sign of usage of some code, and with specification of on which BusinessEntityType this action defined:

Entity BusinessAction(id, name, code_function, type_id)

What does it mean for us? We can now define CRUD actions for each entity, and we can define additional, business-specific actions. For example — «change owner action», «change Invoice state», «change Payment state», etc. After we’ll define these actions, we can use permission/acl system and specify rights for each manager to each object.

Stay tuned, next time I’ll cover permissions and rights system! :)

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! :)

Russian spellchecking PSI

Как установить проверку русского правописания в PSI под Виндовсом?

  1. Взять здесь архив.
  2. Выйти полностью из PSI.
  3. Распаковать его в C:\Program Files\Psi\ (надо правильно распаковать, все файлики-папочки)
  4. На рабочем столе, где иконка PSI, нажать на ней правой кнопочкой мышки, выбрать свойства (обычно самое нижняя кнопка),  вылезет окошко с свойствами.
  5. Нажать на кнопку «Сменить иконку», вылезет новое окошко, с одной-единственной иконкой, на ней делаем даблклик (два раза мышкой быстро нажимаем), это окошко должно исчезнуть. Если не исчезает, нажимаем «ОК».
  6. У нас до сих пор открыты «свойства». Ищем страшную надпись объект, возле нее будет текстовое поле, скорее всего там будет «C:\Program Files\Psi\psi.exe», меняем exe на bat, все англиЦкими буквами.
  7. Сохраняем наши изменения, запускаем PSI, радуемся (обязательно).

I’m not sure anyone english-speaking in need of this article, but anyway, here is translation.

How to install russian spellchecking in PSI under vindovs (and yes, I know how it spells)?

  1. Grab archive here.
  2. Exit from PSI.
  3. Unpack  archive to C:\Program Files\Psi\ (all files and dirs)
  4. On desktop, where PSI icon, right-click on it, choose properties (usually bottom), and properties window will appear.
  5. Click button «Change Icon», new window will appear, with only one icon there, double-click this icon. Window should close, if not — click «OK»
  6. We still have «properties» window. Look there for «Object», near will be text field, with something like «C:\Program Files\Psi\psi.exe», changing exe to bat.
  7. Save our changes, launch PSI, became happy (mandatory).

CRM system from scratch #1

Well, all started very long ago.

There was in my life  CMSes, billings, some admin systems, etc.

After that I started see some things which are repeated and appear in many different softs. This gave me idea to plan and develop my own solution, in no haste, but with careful thinking of many aspects, and without hardcoded, quick, dirty codes.

So, this is part 1 of my article cycle.

CRM, what is it? This is customer relations management.  As could be seen, this is some system where are customers, relations (not exactly clear, between whom these relations are) and management, meant some actions on this all mess. If many companies use the same CRM, there must be concept «Company», to make sure all users from one Company is digging in their own sandbox.  I preferred to name this concept as Branch.

Entity «Branch»

Every user who works in system, belongs to branch (directly, or indirectly).  Every company who works in system, have their own «branch», and all actions of all users/processes/business logics are limited by this branch.

So, this entity is pretty simple and plain, speaking in sql/uml it would be something like this:

Entity Branch (id, name)

I’m assuming that technical fields like whoCreated, whenCreated, ipFromWasCreated, etc — is  obvious, and should have realization in fields, or in logging system, or in both of them.

If there are customers, probably there is some managers, who works with customers. if there are managers, then probably these managers have some heads of office. If there is more than one office, probably we need some «office» concept. In each office could be not only managers and directors (heads), there probably could be dealers, secretary who make calls, hr-managers, etc.  Some manager could be also secretary, some driver could be a dealer, and so on. So simple «position» not fit here.

Entity «RoleType»

We have here entity RoleType, this set could be specific for each company who works in my CRM of dream. For example, in company «A» will be three roletypes: «manager», «client», «dealer». And in company «B» will be «manager», «head of department», «over-super director», «client», «vip client», «dealer», «partner».

So, this entity is pretty simple and plain, speaking in sql/uml it would be something like this:

Entity RoleType (id, name, branch_id)

Where branch_id — is column, by which each record RoleType bound to specific branch_id. If there somewhere will be similar things, like Entity  Foo(id, blabla_id), it means that each record Foo could be bound to some record blabla. This called N:1 relation type.

Entity «User»

Every user (except for banned, or retired/fired/etc) must have way to log into system. I’m thinking of openID way to login. Because of this, User is pretty empty too:

Entity User(id, openid, enabled, deleted, comments)

enabled and deleted could be merged in some «status».

Ok, we have a way to every user could login in system, but what’s next? Let me introduce entity «UserInRole»

Entity «UserInRole»

As I mentioned above, every human being can have more than one assignments, jobs, or statuses in company. Because my system would be pefect and ideal (at least, I hope so 😀 ), we should somehow allow this happen.

Entity UserInRole(id, user_id, roletype_id)

That is it! Simply and elegant. Adding so many records of UserInRole how many he has jobs, we could achieve this functionality. I’m not sure that all business logic could handle working in all roles simultaneously, but at least, I could make a way to select to change current role. So, user logged in, he chose one from available role, and he see the system from this Role view point. He change role to other (from management to dealer, for example) and he see other things, according to other role.

Here we have one entity,  UserInRole which bounds to both User and RoleType. As could be seen, this entity is pure helper, it allow to bound each User to many RoleTypes; And eery RoleType can be bound to more than one User, this called M:N relation type.

So, in this article we covered all this entities: Branch, RoleType, User and UserInRole. In next article we’ll go further :)

My tech blog in my horrible english