As a follow up to my previous webcast on the subject of Relational to NoSQL database where I discussed that we are in the third phase of the NoSQL database adoption, the “Broad Replatforming” of Enterprise Application. I want to provide an example in this article on how 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 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 to 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 user to access to all contacts of an account if the user has access to the account. This could also extends to other related objects to account such as opportunity and service request.

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 access is the management hierarchy. A manager should have visibility to all the activities of the direct reports. Similarly, a user at the parent territory node should be able to see the activities that are associated to the child territories.

Custom business rule access

Organizations can also have custom rules to govern data visibility. Such as Product line, or custom drawn geographical region.  

Activity Management Data Model  

Activity JSON document

One of the characteristic of 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 shows 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 object 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 drop.

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

User should see: All activities

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

Owner and Participant, with associated Account Owner and Account 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

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 modeled in JSON.


Posted by Binh Le

Binh Le is a Principal Product Manager for Couchbase Query service. Prior to Couchbase, he worked at Oracle and led the product management team for Sales Clould Analytics and CRM OnDemand. Binh holds a Bachelor's Degree in Computer Science from the University of Brighton, UK.

Leave a reply