One of the great strengths of Sage CRM is its openness.  Its data model is documented and published and members of the Developer Program can download the latest data model for Sage CRM v7.

There are a couple of recorded lessons that discuss the database concepts used in Sage CRM.

(The links above are accessible through the Training page which is available to members of the Sage CRM Developer Program.)

In some applications relationships between the different entities may be fixed or defined within the database.  This is not the case within Sage CRM for the application tables.

This was a conscious decision made by the original designers of Sage CRM.  The application is not a static application system, but is a very flexible and customizable engine to create any kind of business process automation where entities and fields can be added at run-time in an adhoc manner from within the application.

An example of that flexibility is the Advanced Customization wizard that allows new customer entities to be created with just a few clicks and selections.

Sage CRM v7 changed the nature of the data model slightly by introducing strong referential integrity within some of the tables responsible for holding meta data.

This change is discussed in the What's New in Sage CRM v7 Technical Deep Dive presentation that can be downloaded by members of the Developer Program here.

Amongst the new features introduced in Sage CRM v7 was the Interactive Dashboard and the SData provider.  

These new required a new set of supporting technologies which included

These in turn have necessitated changes in the Meta Data referential integrity and physical data model which have impacted on the Component Manager including the requirement to order the code in components correctly.

Of particular importance is the introduction of Hibernate.  Hibernate is an object-relational mapping (ORM) library for Java.  Within Sage CRM v7 the use of Hibernate is to allow mapping from the Java classes used within the Tomcat Layer to the Sage CRM meta data tables.  

Hibernate enables Sage CRM v7 to maintain the CRM metadata in memory so that it is database independent and fully managed.  

It maps Java data types to SQL data types and provides data query and retrieval facilities used in the Interactive Dashboard and SData provider.

To allow the persistence of the Meta Data Model within Hibernate strict referential integrity needed to be enforced within Sage CRM meta data tables.

The following tables are used by Hibernate

  • Custom_ContainerItems
  • Custom_Edits
  • Custom_Tables
  • Custom_Lists
  • Custom_Pages
  • Custom_Relationships
  • Custom_Screens
  • Custom_ScreenObjects
  • Custom_Scripts
  • Custom_SysParams
  • Custom_Tabs
  • Custom_Views
  • AdvancedFinds
  • AdvancedFindFields
  • Custom_Reports
  • Custom_ReportBands
  • Custom_ReportCharts
  • Custom_ReportFavourites
  • Custom_ReportFields
  • Custom_ReportGroups
  • Custom_ReportSearches
  • ActionSecurity
  • Channel
  • FieldSecurity
  • TerritoryProfiles
  • Territories
  • TerritoryPermissions
  • Users
  • UserSettings
  • LPCategory
  • LPGadget
  • LPLayout
  • LPUserLayout
  • LPTemplateLayout
  • LPTemplateChannelLink
  • LPTemplateUserLink

This  has created new Meta Data dependencies.

  • Custom_Edits 
    • (Custom_Tables)
  • Custom_Views 
    • (Custom_Tables)
  • Custom_ScreenObjects 
    • (Custom_Tables, Custom_Views[optional])
  • Custom_Lists 
    • (Custom_ScreenObjects, Custom_Edits)
  • Custom_ContainerItems
    • (Custom_ScreenObjects x2)
  • Custom_Tabs 
    • (Custom_ScreenObjects)
  • Custom_Screens 
    • (Custom_ScreenObjects, Custom_Edits)
  • FieldSecurity 
    • (Custom_Edits)
  • UserSettings 
    • (Users)
  • TerritoryPermissions 
    • (Custom_Tables ,Users, TerritoryProfiles, Territories)
  • Channel_Link
    •  (Users, Channel)
  • Users 
    • (Channel, TerritoryProfiles, Territories)

For example any insert on TerritoryPermissions will require its parent records to exist first. 

Changes to Component and the Component Manager

Because of the changes in the Meta Data Data Model in Sage CRM v7 and the introduction of Referential Integrity the order of sections within components has become very important.

These sections are:

  • Database links that have been added or updated
  • Tables that have been added or updated
  • Views that have been added or updated
  • Columns that have been added or updated
  • Screen Objects that have been added or updated
  • Lists/Grids that have been added or updated
  • Fields on screens that have been added or updated
  • Tab groups that have been added or updated
  • Container Block items that have been added or updated
  • Table level scripts that have been added or updated
  • Reports that have been added or updated
  • Workflows that have been added or updated
  • Translations that have been added or updated

The tools that are provided to members of the Developer Program to help write Components have been updated to reflect these requirements.  The latest Textpad snippets for Sage CRM v7 can be downloaded here.  

Other changes to the Component Model have been documented in the Developer Guide and What's New Guides.  The latest documentation can be downloaded from the User area of the community.