Salesforce Data Model/Database Design

 Custom Fields :

The No. of custom fields  allowed per object varies according to your Salesforce Edition.

Essentials	Professional	Enterprise	Unlimited	Performance	Developer
100	100	500	800	800	500

There is a 900 maximum hard limit on the total custom fields per object allowed.

 

You can add fields from the AppExchange apps, but the total should be less than 900 fields in total.

 

Ex :

1.For the Unlimited Edition, you can create 800 custom fields on an object plus install 100 fields from a managed package.

2.For the Enterprise Edition, you can create 500 custom fields on an object plus install 400 fields from a managed package.

Note :

 

Master-Detail Relationship not available for standard objects or external objects.

 

Roll-up Summary field not available for external objects.

Special Fields Allowed Per Object :

	Professional	Enterprise	Unlimited	Performance	Developer
Activities	20	100	100	100	100
Relationship	40	40	40	40	40
Roll-up Summary	25	25	25	25	25

 

Relationship Limits :

 

Master-Detail Relationship Fields, per Object (Standard or Custom) --> 2

Total Relationship Fields (Master-Detail + Lookup), per Object (Standard or Custom) --> 40

 

Ex:  38 Lookup and 2 MD

       39 Lookup and 1 MD relationship fields on an object.

 

Note : The absolute limit that you can request is 50

Default Limit: 40

Maximum Hard Limit: 50

Relationship Types :

Type of Relationship	Child Object	Parent Object
Hierarchical Lookup	User	User
Lookup	Standard,Custom or External	Standard or Custom
External Lookup	Standard,Custom or External	External
Indirect Lookup	External	Standard or Custom
Master-Detail	Custom	Standard or Custom

Schema Builder :

 

1.Schema Builder gives a visual high-level view of the data model.

2.Create custom objects, fields and object relationships.

3.A powerful drag and drop tool.

4.Far more efficient than using the object manager.

 

Note :

The only thing you cannot do from Schema Builder is to create a tab, but that can be done in the standard way

Data and File Storage Allocations :

 

In Salesforce, storage is divided into two categories.

 

Data Storage : allocated space used to store Standard and Custom Object records.

File Storage : allocated space to store files and attachments. It includes:

   1.Files in attachments and in File Home

   2.Salesforce CRM Content

   3.Chatter files (including user photos)

   4.The Documents tab

   5.The custom File field on Knowledge articles

   6.Site.com assets

 

Edition	File Storage Allocation Per Org	File Storage Allocation per User License
Developer	20 MB	N/A
Professional	10GB	612MB
Enterprise	10GB	2GB
Performance	10GB	2GB
Unlimited	10GB	2GB

Ex :

Enterprise Edition with 10 standard license users :

 

10GB baseline File Storage

10 users x 2 GB = 20 GB

Total = 30 GB of File storage

Big Object Storage :

 

1.All editions are allocated storage for 1 million big object records per org.

2.Contact Salesforce to increase the limit.

3.Big object storage is calculated asynchronously, so new records aren't immediately reflected.

4.While big object record limits are not actively monitored, Salesforce reserves the right to enforce the limit if necessary.

Note :

Big objects do not count against the storage limit.

 

Person Account :

Person accounts count against both account and contact storage because each person account consist of one account as well as one contact.

 

Note :

 

—> Person Accounts consume a record in both the Account and Contact objects. Each record allocates 2KB, so each Person Account record will require 4KB of storage space.

—> Archived activities count against storage.

—> Active or archived products , price books, price book entries and assets don't count against against storage.

Salesforce Record Size :

 

1.Most records are roughly 2KB.

2.When you create an Account, 2KB is Used.

 

Most records use 2KB of space despite how many fields are actually used. There are a few exceptions to this rule

 

• Person Accounts - 4KB
• Campaigns - 8KB
• Campaign Members – 1KB
• Articles - 4KB

 

Ex :

5,00,000 person accounts will require around 2GB of storage.

 

Storage needed = (500000 x 4 KB)/1024 = 1953.125 MB

 

1953.125 MB/1024 = 1.9073 GB which is around 2GB

 

Note :

1.Price books, Pricebook Entries, Opportunity Line Items and Quote Line Items all appear to not count against storage.

2.Data Storage is not calculated by the number of fields or content in the fields.

Person Account :

 

Person Accounts store information about individual people by combining certain account and contact fields into a single record.

 

Mix between Account and Contact that forms a single record for an individual's data while also being flexible enough to act like an Account when necessary.

 

It's used when selling to individuals, in a B2C model.

 

When a Person Account is created, a contact record with the same name is also created.

 

Clicking on the Contact record will redirect the user to the Person Account record.

 

A person Account is not a typical object.

 

Need to only navigate to the Account tab to manage both Business Account records and Person Accounts.

 

No need to associate a Person Account to a company via the Account lookup.

 

Considerations before enabling Person Account :

 

—> Cannot disable Person Account after enabling it.

—> A person Account record count against both account and contact storage. It consumes 4KB - Orgs with very large number of B2C individuals should confirm that enough data storage is available to handle this.

—> There are changes to the OWD sharing settings that must take place before Person Accounts are enabled. Organisations that  don't have a private sharing model or do not have Contact's set as "Controlled by parent" in their sharing settings should confirm that it's ok to update their sharing model.

 

—> Person Account's can't be included in account hierarchies, contact hierarchies ,or have direct relationships with other accounts or contacts.

 

—> Creating or editing a person account triggers account workflow rules.

 

—> Contact fields from person accounts will continue to be exposed on account list views and reports even if your org stops using person accounts.

 

Note :

Every custom field created on the Contact object is available for Person Accounts using the __pc. If you want a field for person account only create the field on the Contact object.

 

For Person accounts, custom fields ends with "__pc" and custom relationship ends with "__pr".

 

__pc Custom Person Account Field

__pr Used for traversing custom Person Account relationship fields

 

Complete the Pre-requisites for Person Account :

 

--> Review and acknowledge the org impact of person Account.

--> Make sure that the account object has at least one record type (like Business Account for example).

--> Make sure that the OWD is set so that either Contact is 'Controlled by parent' or both Account and Contact are 'Private'.

 

Person Account Considerations :

--> The Account object automations like Flows and Triggers will be fired on Person Account.

--> The Boolean field 'IsPersonAccount' can be used to determine if an Account record is a Person Account. It can be used to create logic specific to Person Account.

--> The following fields are not available for Person Accounts :

Parent Account

View Hierarchy

Reports To

 

Person Account Considerations :

--> Person Account can be associated with activities using either the Name or Related To fields.

--> Person Account can be invited to group events and requested meetings.

--> Can be added to campaigns.

--> For cases, Person Accounts can be entered in the Account Name field, the Contact Name field or both.

--> you can add person Accounts to the Contact Roles related list on cases, contracts and opportunities.

--> Custom objects with relationships to either accounts or contacts can be added as related lists on Person Accounts.

--> you can send individual emails and mass emails to Person Accounts.

--> Contact sharing is not available if you have enabled Person Accounts and if the Contact OWD is set to Controlled by Parent.

Big Objects :

 

Big Objects allow you to store and manage a massive amount of data on the Salesforce Platform.

 

Big Objects provide consistent performance for a billion records or more.

 

Big Objects records are accessible with a standard set of APIs to your org or external system.

 

There are two flavours of big objects :

 

Standard Big Objects are defined by Salesforce and are included in Salesforce products.

 

Ex: FieldHistoryArchive, part of salesforce Field Audit Trail Product, is an example of a standard big object.

 

Custom Big objects are defined and deployed in setup.you can create a custom big object in setup, where you set its definition,fields and index.

 

Big Object limitations :

 

You can create upto 100 big objects per org, and the limits for big object fields are similar to the limits on custom objects and depend on you org's  license type.

 

Once you've deployed a big object, you can't edit or delete the index.

 

To change the index, start over with a new big object.

 

Standard UI not available : page layouts, tabs ,list views

 

However, you can develop custom visualforce pages and Lightning Component.

 

Automation tools like triggers , flows, processes and the salesforce app are not supported on big objects.

 

Big objects support only object and field permissions , not regular or standard sharing rules.

 

No support for salesforce report Builder.

 

Support for Tableau CRM ( Einstein Analytics).

 

SOQL relationship to a parent lookup record are not available in SOQL filters or Subqueries.

 

Big objects are available in Enterprise,Performance,Unlimited and Developer Editions with one million records limit but by using an add-on license the record capacity can be increased.

 

Big Object Index :

 

1. A Big Object record does not include a Salesforce Id.
2. Every Big Object has an index.
3. The Index is used for querying and filtering the big object data and must be designed properly.
4. An index must include at least one custom field and can have up to five custom fields total.
5. All custom fields that are part of the index must be marked as required.
6. You can't include Long Text Area and URL fields in the index.
7. The total number of characters across all text fields in an index can't exceed 100.
8. After you've created the index, you can't edit or delete it.
9. To change the index, you must start over with a new big object.
10. Design your index so that you assign the most frequently used field in a query filter to index Position 1.
11. The order in which you define the fields determines the order that they're listed in the index.

 

Note :

--> The index will act as the primary key and unique identifier.

--> All custom fields that are part of the index must be marked as required.

--> Build an index query starting from the first field defined in the index, without gaps between the first and last field in the query.

 

SOQL and Async SOQL :

 

--> Big objects can be queried using SOQL or Async SOQL.

--> Async SOQL uses a subset of SOQL commands. And it was designed from the ground up to handle the massive volume of data that kept within a big object.

--> Async SOQL queries are run asynchronously, you don't have to worry about queries timing out.

--> Async SOQL queries run in the background, and can be run over Salesforce entity data, standard objects, custom objects and big objects.

--> Async SOQL is implemented via the Chatter REST API.

--> Async SOQL is included only with the licensing of additional big object capacity (more than 1 M records).

 

Note :

SOQL – Can retrieve records within the governor limits

Async SOQL – Can retrieve billions of records

 

Note: Only if the asynchronous query is enabled for the Org, we will be able to use async query operations.

 

Async SOQL is included only with the licensing of additional big object capacity.

 

To execute an Async SOQL, it should be included in a POST request as a JSON-encoded list of key-value pairs.

 

Async SOQL supports a subset of commands in the soql language . The subset includes the most common commands that are relevant to key use cases.

 

Comparison Operators  :   =,!=,<,<=,>,>=,LIKE

Logical operators.           : AND , OR

Date formats                   : YYY-MM-DD, YYYY-MM-DDThh:mm:ss-hh:mm

 

Aggregate Functions : AVG(field),COUNT(field),COUNT_DISTINCT(field),

                                       SUM(field),MIN(field),MAX(field).

 

Having : used to filter results from aggregate functions.

 

GROUP BY : to avoid iterating through individual query results.

 

To Run Async Queries you need to make the POST REST API Call to /services/data/v41.0/async-queries the endpoint with JSON encoded list of the request body.

 

{
"query": "SELECT  Amount ,ClosedDate ,StageName  FROM Opportunity ",
"operation": "insert",
"targetObject": "Sales_OrderBO__b",
"targetFieldMap": {"Amount ":"Actual_Cost__c",
"StageName ":"Saleschannel__c ",
"ClosedDate":"Expired_Date__c"
}
 
}

 

 

The response of an Async SOQL query displays the jobID,  SOQL command, details of the field mapping, and the target object. It also displays the query status, which can have one of three values: Running, Complete, or Failed.you can use Job Id to track the status of your Async SOQL.

 

Big objects require a composite primary key to be deployed to a Salesforce instance. The composite primary key is also referred to as an Index for the particular big object.

The big object’s index is used to query and/or delete records from the big object.

It is crucial that the index be made up of fields that can uniquely identify records. Also note that the Index can only be up to a maximum of 100 characters in length.

 

Setting the index position and index direction will determine the future SOQL and DML structure that interact with the big object. The index direction is important for performance benefits. Set the index direction for fields to ascending or descending based on whether the big object records with field’s lower order values or higher order values will be most accessed. The index position is critical as well because big objects will only allow querying records with filters specified for all the fields that make up the index. You cannot apply query filters in SOQL that skip the fields at lower positions.