Data modeling

If you are a database administrator, you will certainly be able to map your existing knowledge to some of the concepts we are going to talk about. However, if you have no database administration experience or prior knowledge, don't worry, as this will require no prerequisites. When I began my career, I had minute knowledge about this platform as well.

So, let's start with a simple day-to-day scenario of credit card transactions that we do online while we are shopping on an online portal. You might have wondered where all the data that you type in the form goes. The answer is a database. A database for a layman is a dark disk or chip where the data resides. Let's be more logical at this point and think from an application's point of view; the database will consist of various objects.

Your organization, once purchased from Salesforce, will already consist of some out-of-the-box objects that are used in normal sales and service processes. You will see that you don't have to do anything to create the account, contact, opportunity, lead, campaign objects, or the case object. Depending on whether you have a feature license or a standard license, you will already see a set of objects, tabs, and apps.

Anything custom (such as custom objects) will require a build; a build here is a point and click. To add a new object and relate it to our existing data model, we will go to the Build (Create for older orgs) section by navigating to Setup | App Setup (Build for new orgs) | Create | Objects:

There are four types of relationships possible in Salesforce, which are described in the following table:

Lookup relationship

As a database administrator, we know that to relate any two tables, we need a foreign key between the two tables (for one-to-many relationships), as depicted in the following screenshot:

In the table shown in the preceding screenshot, DepartmentID is the foreign key and relates the two tables.

From a Salesforce perspective, lookup relationships are defined between two objects by creating a field of the lookup relationship data type on the child, and then selecting the proper parent to establish a relationship between the two objects.

Consider the following steps to link a Department object and a Course object through a simple lookup in Salesforce:

1. Create the Department object (let's consider this as the parent object, as shown in the previous screenshot). Navigate to Setup | Create | Objects to create this new object.
2. Create the Course object (let's consider this as the child object).
3. Create a lookup field on the child object and select the parent object as Department. The data type of the field has to be lookup:
   
4. In a lookup relationship, we have three major settings. We can create the required lookups that will enforce a user to mandatorily set a parent for any child record.
5. By default, the Clear the value of this field option is selected. This means when a parent is deleted, all the associated parent references are deleted from its corresponding child records.

Master-detail relationships

A master-detail relationship is also used to build one-to-many relationships like lookups but the relationship is quite rigid. Rigidity implies the child cannot exist independently of the parent, and once the parent is deleted, the child records are automatically deleted. One of the greatest features of a master-detail relationship is its ability to provide the roll-up summary data type to roll the count or summation of some fields directly onto the parent record. This feature is not possible to use in a lookup relationship.

The procedure to create a master-detail relationship between two objects is very similar to the lookup relationship; just the data type of the field needs to be selected as Master-Detail Relationship instead of Lookup Relationship:

Many-to-many relationships (concept of a junction object)

In a real-life scenario, we often need many-to-many relationships to solve a business problem. Consider an example in which we have sports and players as two objects. A sport can be played by many players; similarly, a player can play more than one sport.

A many-to-many relationship involves defining a third table (called a junction or join table), whose primary key is composed of the foreign keys from both the related tables. In a one-to-one relationship, the primary key acts additionally as a foreign key and there is no separate foreign key column for either table.

In Salesforce, we also define a third table and call it the junction table, which will have any one of the following combinations defined with the other two tables:

• Two lookup relationships
• Two master-detail relationships
• One lookup relationship and one master-detail relationship

It is important to note a few interesting points about this concept. The first master relationship is known as the primary relationship, and it governs the look and feel of the junction records. The steps to create many-to-many relationships are as follows:

1. Create an object named A.
2. Create an object named B.
3. Create a junction object named C, which will have two lookup relationships or two master-detail relationships with A and B, respectively. It can also have one master-detail relationship and one lookup relationship as well.

Hierarchical relationships

A hierarchical relationship is exactly like a lookup relationship, except the only things that are special about this relationship are:

• It is only available on the user object with no lookup or master-detail relationship
• It can only look up a user object

Navigate to User | Fields | New and select Hierarchical Relationship as the field type.

Now that we have discussed the various relationships, we may want to know where to go in Salesforce to review the schema structure that is built-in (or the one that is already existing in my instance).

The short answer is the schema explorer utility provided by the platform. You can use this to study the schema structure by navigating to App Setup | Schema Builder: