Introduction

Nowadays, customer relationship management has become a key focus of many businesses who have to serve hundreds of thousands to a million customers. New technologies make it much more possible for companies to efficiently manage relations with customers, while providing a host of valuable data regarding their clientele. Such technologies are often referred to as "customer relationship management" (CRM) software.

CRM software can be defined as any software that aims to facilitate and/or automate business processes that occur between businesses, employees, and customers. Some of the ways CRM software can be used include the following:

• Storing customer information for communication and advertising purposes, including keeping track of customers' contact information.
• Storing transactions that occur between customers and businesses, giving businesses a wealth of data on which to predict the number of expected sales (or amount of revenue, profit, etc).
• Keeping track of transactions and receipts for customers.
• Recording communications between the customer and business, allowing businesses to personalize customer support for different individuals or provide other insight into their business processes.
• Giving employees access to key information about clients to personalize their sales processes for each individual.
• Giving higher-ups important information about employee performance (i.e. how different customer service representatives handle questions and concerns based on feedback).

Some real life examples include:

• Salesforce
• Zendesk Sell
• Scoro

Suffice to say, Customer Relationship Management software contains many features, including the ability to manage customers' data, analyze transactions, and open communication lines with customers and employees alike. These kinds of features may make it easier for businesses to handle customer relations efficiently, greatly improving customer satisfaction in turn.

Functional Requirements

For this article, we will design a basic customer relationship management software that keeps track of customer data, transactional data, and customer/employee interactions.

So what are some of the general goals for our basic customer relationship management software?

• Keep track of customer contact details, such as name, email, and phone
• Keep track of customer transactions with business
• Keep track of customer communications with business, whether it be for support, notifications, or inquiries

We can thus define some functional requirements as such:

Business Side:

• Register an employee into the company database
• Send notifications regarding support status, etc. to customers
• Open a communication channel with a customer or another employee

Customer Side:

• Register a customer into the company database
• Begin a conversation with an employee, through phone/email/chat
• Rate a conversation and provide feedback
• Purchase goods/services

Non-Functional Requirements

• Scalability

Our software must be able to adapt to growing demands (i.e. our customer base increases significantly), meaning the software must be maintainable and adaptable. In addition, any new incoming developers must be able to look into the code when integrating features or solving bugs.

• Performance

Any queries that take place from employees or customers must be served in the most efficient way. Time is money, after all!

• Usability

Usability will be key, as any customer needing assistance with various inquiries or processes will need to be able to navigate and find help easily.

Software Requirements

• Server cluster(s), operating in either Windows or Linux
• Relational database management system, preferably PostgreSQL as explained later
• Front-end frameworks, such as React or Angular
• Back-end server frameworks, such as Spring Boot
• Hosting technologies (i.e. Heroku, AWS, DigitalOcean)
• Load-balancing tools, such as AWS

High Level Design

What, then, could our system look like for different kinds of environments?

Small Business

For a small business (i.e. a few thousand users), the traffic is relatively low, with at most a few thousand concurrent users. However, given that the business may also go through spikes over the year (i.e. holiday shopping, etc), it is still recommended that a business invest in a group of servers, preferably with 16 GB RAM and good processors each.

To help deal with many requests at once, a request queue or list needs to be implemented to choose a request to serve. Since our customer base is only about a few thousand, the request queue should be enough to compile the incoming requests.

Customers can log in to the application and request to open communication channels with employees. In turn, employees can log in to the application and open such communication requests, providing assistance when possible.

Large Business

With larger businesses (i.e. tens or hundreds of thousands of users or more), it will become necessary to upgrade the hardware, as well as distribute hardware resources. In our case, we may need to start by investing in multiple server clusters. We may need to begin sharding our databases into database clusters -- that is, distribute parts of our database to different clusters to ease the load off of data input and output operations. Perhaps, for instance, we can store transactions in different clusters by month of year or by region. Many companies such as Instagram make use of PostgreSQL to help shard their databases (see my previous article on PostgreSQL); doing so will be natural with our preferred choice of PostgreSQL as our database.

However, dealing with more requests will become significantly harder, as the server must now deal wih tens or hundreds of thousands of users, or more (and thus, dealing with millions of requests becomes the norm). In such a case, it will be necessary to begin relying on multiple servers and/or server clusters. To balance incoming requests, a load balancer will be necessary to reroute traffic to different servers, perhaps by region or by server capacity. In any case, this load balancer will ensure that a single server is less likely to fail because of a sudden spike in (or a consistently high number of) requests.

Caching may also help in improving the performance of the system. There may be users who consistently access a particular transaction period from the database, or perhaps a manager may want to routinely contact all employees from a different department. In such a case, storing consistently accessed data in a cache layer (as demonstrated here) may help in reducing waiting times. PostgreSQL in particular also features integrated caching.

Low Level Design

So what might some of the basic functions look like in terms of Java code? Take a look:

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.UUID;

class Person {
    // Unique identifier assigned to person
    UUID id;

    // first and last name
    String firstName;
    String lastName;

    // phone number, email, and address
    String phone;
    String email;
    String address;

    /**
     * Request that a communication channel
     * be opened.
     */
    public void requestChannel();
}

class Employee extends Person {
    // company department an employee
    // is assigned to
    String department;

    /**
     * Assign department to employee.
     * @param department
     */
    public void assignDepartment(String department);

    /**
     * Accept an incoming communication channel.
     */
    public void acceptChannel(UUID personID);
}

class Product {
    // unique identifier for product
    UUID productID;
    
    // name of the product
    String name;
    
    // product type (i.e. service, good, etc.)
    String type;

    // description of product
    String description;
}

class Transaction {
    // unique identifier for product
    UUID transactionID;

    // identifier for customer
    UUID customerID;

    // identifier for product purchased
    UUID productID;

    // amount of money spent (or lost if refund)
    long amount;
    
    // date of transaction
    LocalDateTime date;

    // quick description or memo about transaction
    String description;
}

class Communication {
    // unique communication identifier
    UUID commID;

    // identifier for sender of communication
    UUID senderID;

    // identifier for receiver of communication
    UUID receiverID;
    
    // content of the communication (i.e. message, etc.)
    String content;
    
    // date of communication
    LocalDateTime date;
}

class PhoneCommunication extends Communication {
    // duration of phone call
    long duration;
}

class EmailCommunication extends Communication {
    // subject of email
    String subject;
}

class CommunicationChannel {
    // unique identifier for communication channel
    UUID channelID;

    // date the channel was started
    LocalDateTime startDate;

    // a list of communications between
    // two or more people
    ArrayList<Communication> communications;

    // how the customer views the communication
    // out of five stars
    int rating;

    // optional feedback from customer
    String feedback;

    // indicator if channel has been closed
    boolean isClosed;

    /**
     * Note a communication between the customer and
     * the business.
     * @param comm
     */
    public void addCommunication(Communication comm);
}


class PersonDatabaseHandler {
    /**
     * Register person into database.
     * @param firstName
     * @param lastName
     * @param phone
     * @param email
     * @param address
     */
    public static void registerPerson(String firstName, String lastName, String phone, String email, String address);

    /**
     * Register employee into database.
     * @param firstName
     * @param lastName
     * @param phone
     * @param email
     * @param address
     * @param department
     */
    public static void registerEmployee(String firstName, String lastName, String phone, String email, String address, String department);

    /**
     * Delete person from database.
     * @param id
     */
    public static void deletePerson(UUID id);
}

class ProductDatabaseHandler {
    /**
     * Add a product to business.
     * @param name
     * @param type
     * @param description
     * @param price
     */
    public static void addProduct(String name, String type, String description, long price);
    
    /**
     * Remove a product from business.
     * @param productID
     */
    public static void removeProduct(UUID productID);

    /**
     * Make a transaction for a customer.
     * @param customerID
     * @param productID
     */
    public static void makeTransaction(UUID customerID, UUID productID);
}

class ChannelHandler {
    /**
     * Start a channel between two or more people.
     */
    public static void openChannel();
    
    /**
     * Mark a channel as closed.
     * @param channelID
     */
    public static void closeChannel(UUID channelID);
}

There are a few key data classes that are covered within this low-level design:

• Person

The Person class represents a person registered into the business system. For a CRM software solution to get in contact with customers, it may need to send messages or other information to their email addresses or phone numbers. As such, the Person class is responsible for holding details such as name, email, and phone number.

• Product

The Product class represents a product (i.e. service, good) offered by the business. Every product should have a unique identifier no matter its name. In addition, the product would also need details on the type of product, its name, its description, and the price of the product.

• Communication and CommunicationChannel

In customer relationship management software, a customer may need to raise an issue with customer service, or perhaps an employee may need to speak to a customer. In such cases, it may be helpful to have information on what the conversation entailed and/or when such conversations took place. The Communication class implements this conversation, each instance having a unique identifier, an identifier for the sender, an identifier for the receiver, text content, and the date it was sent.

To make a collection of Communications (i.e. a chat room, chain of emails, etc), a CommunicationChannel class is also needed to contain all these communications.

Database Design

We can design our database(s) like so:

Generally speaking, the tables contain the same properties as their respective Java classes (with rows highlighted in blue as primary keys). For instance, the people table has a primary key person_id with the same properties as the Person class in Java (with the exception of password, which is necessary for any user who needs to log into a system).

Some tables, such as employees, phones, and emails, have the same properties as other tables, such as people and communications. Such tables would necessitate table inheritance, as without table inheritance, the database would become far more complicated, having to keep track of two nearly identical tables (i.e. employees and people) with the only difference being a few properties. The relational solution to this problem is PostgreSQL, which has table inheritance as a feature.

One table in particular, comm_channel_pairs, assigns every communication in communications to a communication channel id. This is to indicate membership of one communication in a communication channel: that is, if a communication with some id "a" wasn't sent in a channel with id "A", then there will be no pair (A, a) in the table.

Conclusion

In conclusion, we've made a basic design for a customer relationship management software. While certain features may vary depending on the client, the general necessities of CRM software should be implemented in this guide.