Sage CRM and Referential Integrity

2 minute read time.

One of the great strengths of Sage CRM is its openness. Its data model is documented and published and available to members of the 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.

There is strong referential integrity within some of the tables responsible for holding meta data.

Sage CRM needs to map from the classes used within the application layer to the underlying Sage CRM meta data tables.

Sage CRM uses Hibernate as its object-relational mapping tool to maintain the CRM metadata in memory so that it is database independent and fully managed.

Hibernate 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.

Implications for Components and the Component Manager

Because of Referential Integrity the order of sections within components is 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