This is article about the design of the database to manages people’s information, its design and security considerations.

"CRM" as a term is not exhaustive

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.

The Wikipedia reference on the subject of CRM I do not find enough descriptive:

People Relationship Management Methods

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.

Sales process may be general development process

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:

  1. Send promotional materials by post;

  2. Follow up by email;

  3. Follow up by call;

  4. Send more promotional materials;

  5. Send discounted offer;

  6. Sell the product;

  7. etc.

Or it could be:

  1. Get customer’s lead information;

  2. Call customer;

  3. Send promotion;

  4. Visit by salesman;

  5. Make sale;

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:

  1. Design the general Development Type database table;

  2. Design the Development Process database table that references to the types;

  3. Design the Development Process Stages that belong to Development Process

  4. Finally, design the Development Tracking table. This table may reference to individual people but also to groups of people or their organizations or companies. If you don’t want to move people through specific stages, you may want to move some goods or products through specific stages. The Development Tracking table may reference to various other objects that have to move through specific stages.

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 is a must

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.

Data security and location

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.

Methods of communication to the database without Internet:

  • using LAN or local area network within organization, including wirelss LAN;

  • using virtual private network over Internet, this is 30 minutes to setup or faster

  • modem connections from computer to computer directly;

  • dispatching pieces of communication to collaborators, who handle one piece of information and report back. This is semi-automated. For example, contact information of Jimmy could be saved into data file, dispatched to assigner, who works on single data file and reports it back, which is then automatically imported into the database. This is disconnected atomic editing of database objects. Let us say that Mr. Salesman need to call customer Joe, the background of customer Joe, and contact information could be sent to Mr. Salesman. He opens it on any computer or device, conducts the call and sends back the report. Report is automatically inserted into the central database, and feedback of acknowledgment is sent to Mr. Salesman.

Where did CRM go wrong?

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

  • when somebody mentions "CRM", for many, that is application, not a method; and developers often don’t even know what are those fundamental methods to follow. This may be because often developers are not sales managers; CRM is much more than software, it is fundamental method on how relationships are widened and handled; it is not about sales only; we drive people and help them develop in various subjects, not only sales;

  • just because application is developed to be accessed through HTTP people tend to host publicly their private information which is often released into hands of crackers and other malicious people who sell the information. See RaidForums and instead, such database systems shall be held private.

Interface choice

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:

  • at least server side authentication; without authentication no page can be accessed by anybody; make it a private web server;

  • additionally to server side authentication, or standalone, one can setup private SSL key authentication. If user does not have the authorized SSL key in the browser, such user would not be able to access the website; and such user would need to be verified personally;

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:

  • database should not be placed on public Internet server;

  • data should remain private;

  • that we can still, easily rely our data to remote users over Internet by using VPN or other methods:

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.

But how can I interact with clients online?

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.

Example workflow for user’s self update

How can customer update his information? Customer could request online to update the information, and such information could be sent to customer to update it by using simple text editor and send it back or upload it.

It is possible to think of the following workflow:

  • customer enters the website and enters his email address, as customer wish to update the information;

  • the HTML form is generated for customer offline and dispatched by email (or other means);

  • customer opens the HTML form, can review personal information, edit it, and send it to WWW server;

  • WWW server receives information, stores it as encrypted data on the file system;

  • offline program downloads the data and parses it;

Liberate data to use any kind of interfaces and programming languages

When it is well understood that private data shall remain private the need to develop the HTTP interface diminishes and programming becomes really easy, and there can be plethora of various interfaces:

  • keep the SQL queries OUTSIDE of the programming language code; use it as data; example can be SQL query to find people by their description, SQL query to find companies related to person, SQL query to find people by their phone number or email, and so on. Such queries can be reused by various programming languages. SQL queries may be stored in the database itself as that makes the universal data storage and format easily parsable by any programming language.

  • then use ANY programming language that uses SQL, make the functions and programs. One is liberated. There is no need for HTTP, but one can use HTTP:

  • use shell, add, modify, delete information by using shell;

  • use any command line interface with any programming language;

  • use Zenity, GTK, other dialog based GUI toolkits; GTK Server;

  • use Emacs editor; or GUI capabilities of any programming language;

  • any OS and any GUI or console tool, programming language, can be used to handle database;

  • use WWW if necessary;

Make sure of database backed permissions and you are all fine and set;

Database design

Recommended fundamental database design is the GeDaFe’s design:

GeDaFe - PostgreSQL Generic Database Interface:

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.

List of tables in RCD Notes

I have already developed a system and I use 2 interfaces, I have used GeDaFe HTTP interface, which I use now less, but keep the database compatible and the Emacs editor interface. The database tables are here, and when you need it, I can send you.

Rapid development

It is breeze to develop the database table by using Emacs skeleton features, in other words the interactive SQL template, which one may find here as part of this package:

When I say rapid, I mean it rapid. Your database tables should be developed within few minutes for each.

GNU Free Documentation License

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"