ERD basics

Microsoft Dynamics CRM 2011 uses SQL Server as its database backend. There are two main databases that are created after the CRM server is initially set up. The MSCRM_CONFIG database, which is the one that holds different organizations' data and general configurations. The other is a database for the organization itself, with a name like <organizationname>_MSCRM. Depending on the number of CRM organizations you have in the system, you will have the same number of databases ending with _MSCRM with similar database schemas. The only difference between the organizations' databases would be the specific customizations on the entities and fields.

Microsoft Dynamics CRM 2011 uses the concept of entities to name, which in a database language would be like a table. An entity is usually a noun such as a person or house. Dynamics CRM comes with lot of entities already created in the system, such as Account, Contact, and Invoice.

As in any table, the entities contain fields. Each field represents an attribute in the entity. Some examples of fields are First Name, Name, and Address.

Dynamics CRM 2011 has the following types of fields:

Dynamics CRM is a structured system and hence it manages everything in entities. Once you create a field in CRM, you won't be able to change the type of field as you would be able to in Microsoft SharePoint.

For any entity you create in CRM, the system will create different objects in SQL: it will create two tables, one with the name of the entity plus the Base suffix and another table with the same name plus the ExtensionBase suffix.

For example, if we create an entity called Project, the following two tables will be created:

Notice that the New_ prefix comes with the default publisher of the solution on which we created the custom entity, and can be changed by creating another publisher.

Apart from the tables, there will be two views that CRM will also create in the SQL Server database:

The first view, new_project, will return the join of the two tables New_ProjectBase and New_ProjectExtensionBase as these two tables have a one-to-one relationship because they share the same primary key, new_projectId. All the IDs in CRM are GUIDS, which are unique identifiers.

The second view is similar to the first one but with the addition of capacity to control the security of the results based on the calling user. So if this view is selected by a customer service representative, it will return only the records this user has permissions to see, and the first view won't validate any permissions and would always return all the records.

For these reasons, it is recommended to always use the filtered views when querying the records in our reports. That way we can be sure that we will only display information that the user who runs the report is allowed to see.

Another benefit of using views is that you not only get the fields of one table, such as the Base table, but also the fields from the ExtensionBase suffix plus the option set values that are also stored on a separated table.