Relational to NoSQL: CRM Application Data Visibility

DZone 's Guide to

Relational to NoSQL: CRM Application Data Visibility

In this article, see how the activity management model can be best represented in JSON.

· Database Zone ·
Free Resource

As a follow up to my previous webcast on the subject of Relational to NoSQL database, I discussed that we are in the third phase of the NoSQL adoption, the “Broad Replatforming” of Enterprise Application. I want to provide an example in this article on how an application can leverage JSON data model and Couchbase N1QL (a SQL++ Implementation) to address the complex data visibility rule of a CRM Application.


One critical aspect in a CRM application, but one that is often overlooked, is the activity management process. To manage the customer relationship, and to do so effectively, the application needs to keep track of all the activities directly or indirectly associated to the task of relationship management. A CRM activity captures all the interactions that a business has with its customers throughout the entire relationship. It is also used to record different activities that are in the CRM system, some of which may not be directly associated to the accounts, such as the lead generation process, quota management, and order fulfillment. It is also used by the Marketing campaign and Services to track all the support activities.

In this article, I will show how the activity management model can be best represented in JSON. Then using Couchbase N1QL as a query language to satisfy the functional requirements for direct and team data visibility.

The Activity Management Challenges

Due to the fact that activities can be associated with all other key objects, activity management is considered a common component in the CRM application. But unlike other CRM objects, activity is often configured to depend on other key objects for its data security. Access to activity is therefore determined by the object or objects that they are associated with. For instance, a user could be expected to be able to see all the activities that are associated with the accounts or opportunities that the user owns. For these reasons, over time, many Enterprise CRM deployments can result in a large volume of activity records. Note that the data volume of the business activities by itself is not always a cause for concern. However, when it is compounded with complex data visibility rules, it becomes a more challenging issue.

Relational queries to retrieve the activities a user can see can become complex because of the indirect data access rules. This often results in a slow system response time in the activity management process as well as in the generation of business reports for this function.


Because activity interactions are captured at every stage in CRM, it is by far the most heavily used object. Often times, trying to access all activities that a user can see can result in slow system responses due to the volume of data and complex business rules.

Data Access

In CRM, data access is defined in several ways. Direct and Indirect ownership, as well as team and hierarchical access.

Direct/Owner Access

A user can access a data object via direct ownership of such object. In CRM all key objects, such as account, contact, opportunities, etc. has a clear definition of direct ownership. An account manager owns an account, a sales representative owns an opportunity, the support team member owns service request.

Indirect Access

In this model, access to an object is inferred from the parent object. For instance, a CRM system can be configured to allow users to access to all contacts of an account if the user has access to the account. This could also extend to other related objects to account such as opportunity and service requests.

Team Access

A variation of direct ownership is where an object has multiple team members. For instance, while an account has an account owner, there could also be an account team with multiple sales representatives who manage the account. This is also very common with territory base sales.

Hierarchical Access

An example of this type of access is the management hierarchy. A manager should have visibility into all of the activities of the direct reports. Similarly, a user at the parent territory node should be able to see the activities that are associated with the child territories.

Custom Business Rule Access

Organizations can also have custom rules to govern data visibility, such as a product line or a custom drawn geographical region.

Activity Management Data Model

Activity JSON Document

One of the characteristics of a JSON document is its flexibility with the data structure. For the CRM Activity object, the relational database restriction would require all the activity attributes to be predefined in the table definition. This can be confusing when looking at the activity record, as not all of the attributes would be filled out. However, in JSON, since it does not have a schema, each document can have a different set of columns.

The two activity documents below show the benefits of the flexible schema of JSON. An activity of type "Appointment" includes contextual attributes such as a set of contacts, start time, duration, and "Participants." Whereas "Task" has attributes that are specific to them, such as Due Date and ToDoList.


The activity object is also one of the key objects that often get extended for different CRM needs. In generic sales automation, it can represent a call report with specific attributes, such as outcome and follow-up details. For pharmaceutical sales, it can represent a physician visit which may include capturing a list of drug sample drops.

The mutable nature of the activity object required by different CRM verticals can result in several variations of the schema structure for this object. JSON is, therefore, an ideal means to model this object for this single reason alone.

Owner and Participant Model

Data Visibility

The user should see: All activities

  1. That the user owns
  2. That the user is a participant of

Owner and Participant, With an Associated Account Owner and Account Team

Data Visibility

The user should see: All activities

  1. That the user owns
  2. That the user is a participant of
  3. Belong to the account that the user owns
  4. Belong to the account where the user is on the account team

Owner and Participant, Associated Account Owner and Account Team, Associated Territory Owner and Territory Team

Data Visibility

User should see: All activities

  1. That the user owns
  2. That the user is a participant of
  3. Belong to the account that the user owns
  4. Belong to the account where the user is on the account team
  5. Belong to the account territory that the user owns
  6. Belong to the account territory where the user is on the territory team


The above examples illustrate the following key points

  • The JSON flexible schema data model is well suited for the ambiguous nature of the activity object
  • There are fewer JSON objects than that in the relational data model
  • The team member concept works well with JSON array
  • The query construct of N1QL is quite similar to SQL, and somewhat simpler

In the next article, I will discuss the hierarchical data security challenge and how that can best functionally be modeled in JSON.

database ,tutorial ,activity management challenges ,crm application ,json

Published at DZone with permission of Binh Le , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}