Microsoft CRM 4.0 database structure

Having become so familiar with the CRM database structure I almost take it for granted. There is great value in how Microsoft designed and built the database and how it handles creation of new entities. I found myself explaining the benefits of how the database is structured twice this week so I figured that warrants a post.

The CRM database is structured with views that prevent the need to query the tables directly. Each entity in CRM consists of two tables in the database. For example, the account entity consists of the AccountBase and AccountExtensionBase tables. The AccountBase table contains the out of the box attributes and any custom attributes are in the AccountExtensionBase table. The database has a view named Account that automatically joins those two tables so when querying for data you would query the view and use a query such as ‘select * from account’. This view also has joins to related tables based on foreign key ids to retrieve the name text value of the related entity. For instance, it contains the ownerid but also contains the owneridname which returns the name of the owner. This can prove handy when creating reports.
Another view also exists named Filtered (i.e. FilteredIncident). This view has the same structure as the Incident view yet also applies the system security to that view. This allows someone to write a report using the FilteredIncident view named ‘My Cases’ and distribute it to the entire organization. When executed, the report is filtered to only show Cases that the executing user has access to.
Most tables and views in CRM bear the same name as it is labeled in CRM yet a system customizer can rename an entity. Case is one object out of the box that has underlying tables with a different name. The Incident tables and views represent the Case entity in CRM. Any custom entites you create contain the customization prefix in the table and view name as defined in the CRM System Settings. Once the new entity is created, the supporting tables and views as I defined above are automatically created for you and no additional work is needed on the database end.
If ever you are unable to locate the tables and views that support an entity, you can locate that in the Microsoft Dynamics CRM interface. In CRM, click “Settings” in the Wunderbar, click “Customization” in the navigation links on the top left of the screen, and then select “Customize Entities”. Find the entity you are interested in by viewing the “Display Name” column as that represents the name used in the user interface. The “Name” column will show you what the underlying table/view name is in the database.
When you open an entity in the CRM customization area, you can locate the 1:N, N:1, and N:N relationships for that entity in the left navigation area. It will display the name of the foreign key id field in the related entity if you are viewing a 1:N relationship for instance.
This post should give you an idea of the database structure in Microsoft Dynamics CRM and the value it provides for reporting or other purposes.