Design and Security for Database Management

Customer Relationship Management is not enough exhaustive term.

I don’t think that databases that handle people and their relations should be named just "CRM" as it is narrowing the term to customers only. A cooperative society of farmers in East Africa may rather manage their own list of farmers, there could be 3000 people and related family members, and the number of customers could be just 3 within 3003 of total records. Each of them has to pass specific stages of development such as education, submitting report of the farming land, and exchanging goods or services. It may not be a direct sale neither farmers need be customers, but fundamental principles do not change.

I like personally to call it People Management.

Today people refer to "CRM" as being solely the application without understanding of the underlying method of management of relations. That is also wrong, as Customer Relationship Management methods existed long before the invention of computers.

Many city administrations world wide still work with paper files and folders. Collecting all information about the related person into a folder is the basic principle to develop such relation.

In general, we could say that each event related to a person shall be recorded or noted if we wish to rely on any method of relationship management. In other words we have to remember information about the person to which we have any kind of relation.

Only so each collaborator may be assigned to handle specific person and with the related recorded information at hand thus becomes capable to follow up the person’s development or conversion from one stage to other.

A Customer Relationship Management system is meant to drive sales. Thus "customers" information entered into system and customers themselves have to pass through certain stages of a sales process or any other process. For example, some sales process may be described as:

Or it could be:

The more interactions with the customer the more steps of the process are finished and thus sales increase.

We may however want to apply the concept of sales process to objects different to sales such as cooperatives or humanitarian organizations. They may not deal with customers and may not deal with sales processes. But they have different other processes.

The majority of people related to variety of activities and organizations may not be really customers and sales may be completely absent. We may want to bring the person through different stages or different type of a process.

Thus every database design that leans to well known Customer Relationship Management principles shall design its various stages or processes in which people may be tracked. That is very simple to accomplish:

Majority of publicly available CRM systems have only one defined sales stage process such as the conversion from lead to customer. And that is poor database design even for sales.

For example, pupiles in a school have to pass through various stages, there are classes, there is maybe visit of parents to the school, there are activities, there are many development stages for a pupil, those are by its nature different to sales stages.

For patients in a hospital there are various stages, such as arrival to reception, emergency analysis, seeing doctor, getting general diagnosis or moving a patient to specialized doctor, receiving treatment or medication and so on, payment included. Those are just general examples. A patient may be a "customer" but not necessarily, there are non-profits who handle patients as well.

In any kind of management of people’s information there are some stages that may be defined so to help people move forward to the next stage.

The concept of moving people through various stages, converting leads to sales, or converting pupils to educated graduated students; that is fundamental to each method of the Customer Relationship Management. It does not matter how people call the method, they may call it "CRM", but fundamentally the database is there for the purpose to guide or move people through specific stages of a development process.

Collaboration should be fundamental principle of any database management related to people. As compared to paper files, teams of people may work on bunches of paper files, thus paper based records have good collaborative capability. The paper file may be assigned to Joe to handle the client named Aaron, Joe as salesman may put notes into the paper file, and salesman Jane may look into the notes and follow up the cycle with Aaron without assistance of Joe. If this basic principle is not provided by software, then software degrades human to the development that already existed for hundreds of years with the paper files.

Thus collaboration feature is a must in any kind of people’s information management.

I would choose PostgreSQL database as it already has collaborative features. Here I would not discuss differences to MySQL, I am just used to the former.

For reasons of security, as a fundamental principle of privacy , the database and people’s information management system should not be located on Internet.

That such tools exist and are available over HTTP interface, it does not mean that any serious organization neither individual, should ever keep their database on Internet. This is too often abused, and people’s information leaks, it may be stolen or distributed to others. Today, managers of people’s information don’t even react when it happens. It is all lack of responsibility.

To gather, collect, record information, one has to pay and investment is considerable. There are business secrets. If information is online, it may leak or get cracked sooner or later. This is worst that can ever happen to some organization.

Personally I know somebody who was stealing databases, he earned 500,000 euro in about one year on stolen databases of people who ever subscribed to SMS services. Because those people could be spammed with Premium SMS and they would in general, less complain than the others who never subscribed to it. Those who complain would get refunded. Others would get scammed for 6 or 10 euro, not much, not worth much walking and calling, right? I don’t condone such activities.

Such database shall be in premises of the organization, its location shall not be on Internet. One should not use VPS or cloud providers to keep personal information of thousands people online.

If any user need to access the information then such access may go through the virtual private network to the private computer located behind the firewall. When I say "VPN" I mean PRIVATE, and I definitely do not mean applications that provide VPN access for browsing Internet. Such VPNs are NOT PRIVATE. Private is what one setup privately by one’s own interests.

To access remotely a private computer behind firewall, one again either need to know each other IP addresses, or to have a central Internet based server that serves the VPN connection. The difference is that private information is not anyhow accessible to public, neither to those who would eventually intrude into the server, as they would not have other necessary credentials.

However, there is absolutely no need to pay some central server to store the database, to host the program, and so on.

The development of our "CRM" systems on this planet already went wrong in following areas:

If one is mislead by the above mentioned propaganda how "CRM" is application and how such must be accessible over Internet, one will make wrong choices and thus: place the unsafe database online, often getting trapped in one or the other vendor’s lock-in, there will be increased expenses over the years, and one will NEED AND WANT to develop the CRM program by using HTTP interface.

Today, when CRM as term is mentioned many will immediately think of HTTP or WWW application. How contradictory is that to the fact that people’s information should be private information?

Then even if it is placed online, it is suprising how people rely only on the basic WWW/HTTP exposed authentication program, such as username/password, thus having the WWW application accessible just to everybody including to search engines, making it easier for crackers to find your database.

Instead, any WWW application that handles private data should have:

Additionally, instead of keeping information on the public server one could use Haproxy or other proxy tool over VPN so that information from organization’s computer is pulled by request through Internet and handled through the Internet domain, however, such information would not be stored on the Internet public server. In other words database should always be private.

If however, we consider that:

that does not any more dictates the development of the HTTP interface.

In my experience, and I have been testing almost all WWW interfaces, they are all terribly slow compared to some other techniques, they may be very buggy, time wasting programming processes. I am not against it, but I would not focus on one interface especially not on HTTP interface.

Interactions such as mailing emails to customers is possible without online database, keep it offline. Sending SMS, letters, emails, all that works without offline database.

When customer wish to unsubscribe, such may send a request that is stored as piece of information. It could be stored in separate public database and later accessed and processed by the offline database and program that unsubscribe the customer. No name of the customer need be stored online. I have been using this approach.

Now I am using the approach that customer can send an inquiry, upload information, even forms by using HTTP/HTML over WWW, and those forms are securely encrypted as LISP objects on the server, later they are synchronized with the offline database by pulling them, downloading them and automatically parsing such LISP data.

Again only those necessary pieces of information are handled online. Not the whole database.

Recommended fundamental database design is the GeDaFe’s design:

GeDaFe can already be used as a generic database interface to list tables, add, edit, delete records; extensions with any programming language are also possible.

database information people customer postgresql security safety emacs interface crm rcd-notes

Copyright © 2021-05-15 11:41:28+02 by Jean Louis. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license is included in the section entitled "GNU Free Documentation License"