Architecture Guide

SplendidCRM Architecture Guide

Introduction

SplendidCRM is a commercial open-source CRM that is built using the Microsoft technology stack.  SplendidCRM was originally inspired by SugarCRM.  We use the word “inspired” because using the word “derived” implies that some code from SugarCRM was used in the process of developing SplendidCRM.  PHP and C# are so different that derivation is not practical.  SplendidCRM is built from the ground up to be visually identical to SugarCRM but implemented using Microsoft best practices for web application development.

The SplendidCRM database schema is reasonably identical to the SugarCRM database schema.  This was done to allow SugarCRM users to migrate to SplendidCRM.  The SplendidCRM look-and-feel is reasonably similar to SugarCRM 6.x so that our user’s can leverage the community documentation and training that SugarCRM has fostered.

Our strategy is to follow SugarCRM and add features as they add them, but we tend to have a higher priority on performance and robustness, so we tend to be one major version behind.  Throughout this document, we will highlight the differences between SplendidCRM and SugarCRM so that you can better understand the choices we have made.


Basic Design Goals

We have tried to create a system that is simple to learn, yet supremely fast and very robust.  Instead of having a huge object model, we prefer a thin data-access layer that allows us to optimize the data going to or coming from the database.  We try and keep the number of layers to a minimum because too many layers can make the system difficult to learn.

As much as possible, we like the code to be self-documenting.  We use descriptive variable names and we use Hungarian notation so that the developer immediately knows the type of data that is being manipulated.

For SplendidCRM, we believe that we are able to achieve a best-in-class performance by putting our business logic in the database.  Today’s relational database is obscenely powerful and is well adept at handling relationships.  By putting our business logic in the database, we are able to reduce the network chatter and network latency.  (Something to consider is that the 1Gb network standard of today has not changed in the last 5 years, whereas the typical server of today is nearly 10 times faster than the typical server 5 years ago.)

We like to think of SplendidCRM as a 2-tier application, consisting of the web server and the database server.  There are some developers who strongly suggest that all enterprise applications should be n-tier, with the business logic running on a separate application server.  We believe that we can achieve sufficient scalability and unmatched performance using a 2-tier design and by placing the business logic directly into the database.  (For a better description of the pros and cons of multi-tier applications, please read http://www.lhotka.net/weblog/ShouldAllAppsBeNtier.aspx)


Cross Platform – Windows & Linux

From day one, SplendidCRM was designed to run on Linux using the Mono libraries.  As a purely managed application, this was not hard to do.  SplendidCRM was first tweaked to run on Mono in July 2006; the original effort took 5 hours.  One area that required a fair amount of effort had to do with the differences between the file systems of the two operating systems.  Linux uses a different slash than Windows, so we had to make certain to use Path.Combine() to build paths instead of doing it by hand.  Also, file names are case-significant on Linux, so we had to make certain that all references to files matched the file names.  For example, on Linux Default.aspx is not the same as default.aspx.

Mono does not currently support the Microsoft Report Viewer, so reports will not run on Mono.  Also, Mono does not support the Windows Workflow Foundation, so workflows will not run on Mono.  Because of these limitations, we do not support our Professional or Enterprise products on Linux, but our Community Edition will run on Linux.  In fact, you can simply copy the entire SplendidCRM application over to Linux and run it.  There is no need to rebuild the application on Linux.

GUID Primary Keys

SplendidCRM relies heavily on the use of GUIDs (Globally-Unique Identifiers) as its primary keys.  When you have been developing enterprise applications for a while, you quickly realize the benefits of using GUIDs as primary keys, as opposed to using integer primary keys.  While it is true that an integer-only key takes only four bytes of storage, the difference between 16 and 4 bytes is insignificant in most applications today. 

What is more important is that an ID can be assigned a globally unique meaning without there ever being a reason to reuse the ID.  Integers do not have the same luxury.  When integers are used as primary keys, it becomes difficult to determine what the value means.  One of the best reasons to use GUIDs as primary keys is that data can be imported without having to re-key.  You don’t realize how important this is until you have to combine and validate two databases, each containing 50,000 records.

SplendidCRM uses the uniqueidentifier SQL Server database type for all ID fields.  This ensures that the data stored in the database is always in the correct format.  By contrast, SugarCRM uses a char(36) to represent its ID fields.  Even worse, SugarCRM uses invalid GUID values in these ID fields such “1” for the ID of the administrator and “private.will” as the ID for the private team for Will.  SplendidCRM treats all its IDs as GUIDs and will enforce validation using the System.Guid field type in the C# code and the uniqueidentifier field in the database.  As a side note, since SQL Server is the only database platform that supports the uniqueidentifier field type, we are forced to use char(36) in Oracle, PostgreSQL, DB2 and MySQL, but we still validate in the C# code, so all is not lost.


Data Access Layer – Stored Procedures & Views

SplendidCRM relies heavily upon SQL Stored Procedures and SQL Views to achieve superior performance.  Here are some ground rules:  1) anytime we pull data out of a database, we do so using a SQL View; 2) anytime we put data into the database, we do so using a SQL Stored Procedure.  As a general rule, we put the business logic inside the stored procedures and the views.  There are rare exceptions to these rules, such as the use of a stored procedure to read IMAGE or BLOB data, or the use of direct UPDATE statements when dealing with custom fields. 

The primary motivation for putting the business logic in the database is to increase performance by reducing the network traffic and by letting the database do what it does best.  A relational database is all about relations, so the best place to put relationship logic is in a SQL view, where the relationship can be defined in the language of the database. 

Another key motivator for using SQL stored procedures and SQL views is to allow each database platform to have its own implementation.  SplendidCRM currently supports the following database platforms: SQL Server, Oracle, PostgreSQL, DB2 and MySQL.  The procedures and views then become the data-access layer, whose definition provides the necessary separation between logic and data.  While there is a little code in the C# that handles the differences between the database platforms, the major difference is handled by a separate set of stored procedures and views.

By contrast, SugarCRM does not use any SQL views or SQL stored procedures.  Anytime SugarCRM needs to retrieve data from the database, it must dynamically build the select statement.  Anytime SugarCRM needs to update the database it must dynamically build the insert or update statement.  And, if multiple records need to be updated as part of a single transaction, SugarCRM will make multiple calls to the database to complete the operation.

Now that SplendidCRM has the database completely abstracted, the fun can begin.  We can query the metadata of the database and create C# wrappers around all stored procedures.  The C# wrappers provide strongly-typed functions that can ensure that the right data type is used at all times.  And, in the event that the data type changes, the C# compiler generates an error at compile time instead of at run time.  We also use this metadata when importing data.  It allows us to ensure that the proper field names are used without having to manage a separate list of available fields.  The SQL views provide a similar amount of metadata and allow us to dynamically provide lists of available fields so that we can provide our users with a Report generator.

 


Project and Folder Organization

SplendidCRM is built as a single Web Application Project.  We have made a conscious decision to keep things simple.  While the day may come when we may want to separate SplendidCRM into modules, we believe that the single-project approach allows both entry-level and advanced programmers to take advantage of the SplendidCRM platform. 

In the SplendidCRM project, you will find a few common ASP.NET files such as App_Themes  and App_Browsers.  Most of the folders are named after the modules, such as Accounts, Bugs and Contacts.  We use the _code folder to store utility classes and we use the _controls folder to store user controls that are shared throughout the application.

Each module will have a number of common files that deal with the three basic operations of listing, viewing and editing.  In each case, we use two files to render the data.  Default.aspx and ListView.aspx render the accounts as a list.  View.aspx and DetailView.aspx render the details of an account.  Edit.aspx and EditView.aspx render an account in an editable form.  You will also find a user control for each relationship in the module.  An account can have bugs, cases, contacts and leads, so you will file user controls Bugs.ascx, Cases.ascx, Contacts.ascx and Leads.ascx.  Separating the presentation this way allows us to better control the visibility of the data.


Database Schema Naming Convention

SplendidCRM was inspired by SugarCRM, so we have tried to follow their schema as closely as possible.  Most of the tables are named after the modules they support, such as the ACCOUNTS table for the Accounts module.  Tables that define relationships include the names of the two related modules, such as the ACCOUNTS_CONTACTS table.  Every table will have six fields that are used for basic audit tracking.  The following is an excerpt from the ACCOUNTS table that shows these fields:

Create Table dbo.ACCOUNTS

      ( ID                   uniqueidentifier not null default(newid()) constraint PK_ACCOUNTS primary key

      , DELETED              bit not null default(0)

      , CREATED_BY           uniqueidentifier null

      , DATE_ENTERED         datetime not null default(getdate())

      , MODIFIED_USER_ID     uniqueidentifier null

      , DATE_MODIFIED        datetime not null default(getdate())

 

You will also notice that we use all upper case for field names and underscores to separate words.  We do this because our Oracle tools always treat fields as upper case, so by adopting this convention, we are able to ensure that the schema is readable in Oracle.

We follow the Hungarian notation for naming SQL objects such as functions, views and stored procedures.  All our SQL functions start with fn, all our SQL views start with vw and all our stored procedures start with sp.  Our views and stored procedures always include the table in the name, so the base view for the accounts table is called vwACCOUNTS, but we also have views vwACCOUNTS_List and vwACCOUNTS_Edit so that we can better control what fields are available in each area.

The main stored procedure that we use to insert or update an account is called spACCOUNTS_Update.  Similarly, we have a stored procedure called spACCOUNTS_Delete to mark a record for deletion.


Data Driven

SplendidCRM is highly customizable, without any programming.  This is because of its data-driven nature.  It is this data-driven approach where SplendidCRM is vastly different than SugarCRM.  While SplendidCRM stores its layout views in the database with table names such as GRIDVIEWS, EDITVIEWS and DETAILVIEWS, SugarCRM stores its layout views in HTML and PHP files.  So while the SplendidCRM layout can be easily changed by modifying records in the database, SugarCRM requires that the web application files be changed. 

The same applies to the localization data.  While SplendidCRM stores all language translations in the TERMINOLOGY table, SugarCRM stores its language translations in PHP files.  The big benefit to a SplendidCRM administrator is that this terminology can be changed on-the-fly.  It also means that all the configuration of the application can be saved by simply performing a database backup.

SplendidCRM stores lots of configuration in the database.  The CONFIG table is used to store all sorts of configuration data, all of which can be changed at runtime.  The DYNAMIC_BUTTONS table is used to store the button layout across the entire application.  The MODULES table is used to control which modules are available or visible.  The SHORTCUTS table is used to contain the list of shortcuts available in each module area.  The TIMEZONES table is used to store the time zone changes across the world.

One of the biggest benefits to being data driven is that a single web application can point to 100 separate databases for 100 separate companies, each with their own configuration and layout.


Multi-Tenant

SplendidCRM was specifically designed to support multiple customers on a single server.  While there are many ways to create a multi-tenant application, we prefer to have a separate database for each customer.  By having a separate database per customer, we are able to ensure proper separation of data and thereby ensure the security of each customer’s data.  Having a separate database also means that we can provide a backup of the database so that our customer can transition from a hosted environment to a locally managed environment, or vice versa.

Even as we have a separate database for each customer, we generally share a single web application across all customers.  The web application has specific hooks to allow each separate domain or virtual directory have its own database.  While these hooks are especially useful when testing the latest build against all the database platforms we support, this code is primarily written for our hosting partners so that they can easily manage hundreds of customers.


Localization – Language

SplendidCRM can support an unlimited number of languages.  Instead of using the resource system in the typical .NET project, we prefer to store our language translations inside the database.  This approach is part of our data-driven design, but it has the primary benefit of allowing us to manage the data in the same way that we manage any data in the database.  A SplendidCRM administrator can modify any text for any language from our admin area.  As a side note, we are able to automate the translation of the English terminology using the Google Translation Service and store the results back into the same terminology table.

On the web application side, all supported terminology information is loaded into memory at application startup.  This ensures that the system can handle any supported language.  We use the Application cache for this data and it is treated as semi-permanent data and access to this data is very fast.

As stated before, SugarCRM stores its language translation data in PHP files scattered throughout the application.  By storing our language translation data in the database, we are about to ensure that this data gets backed-up along with all the rest of the data in the database.


Localization – Date & Time

Date and time handling is one of those things that most developers take for granted.  However, because of the complicated rules regarding time zones, date and time handling can get very complex.  Sadly, the Microsoft .NET framework provides few tools to properly convert times between time zones.

In SplendidCRM, we have followed the Windows approach to date and time handling by mimicking the time zone data stored in the Windows Registry.  We store this information in the aptly named TIMEZONES table.  This table contains such fields as the bias from GMT as well as the daylight savings bias.  It also includes information as to when a time zone switches to or ends daylight savings.

The most common way that developers manage the localization of date and time is to store all values in the database in GMT.  Then, whenever a date is displayed, it is converted from GMT to the desired local time.  While SplendidCRM can store date values as GMT, we prefer to store date values in Server time.  Server time is based on the time zone specified in the Windows configuration.  We prefer Server time because it is much easier to debug date and time issues when the value stored is in one’s own time zone.

Localization – Currency

SplendidCRM has the ability to display currency related fields in the preferred currency of the current user.  We do this by converting currency values from a base, typically US Dollars, to the user-selected currency for any field marked as a currency field.  In this way, currencies are managed in a way that is very similar to how dates and times are managed.


Authentication

One of the best advantages of being a Windows web application is that our customers can take advantage of Windows Authentication.  This means that their users do not need to maintain a separate login to SplendidCRM.  The web server can be configured to automatically detect the current Windows authentication and SplendidCRM uses this information to automatically log the user into SplendidCRM.  For those customers who provide CRM services over the Internet, we also support our own internal login mechanism.

No matter how the user logs in, the user will always have a record in the USERS table.  We do this because we need a record for each user to be able to define relationships. 


Access Control – Role Management

SplendidCRM supports multiple methods to control access to the system.  Our Role-based management system allows administrators to define access rights at the module level.  Access rights are determined at login and stored in the Session cache to ensure optimum performance.  Role-level access rights are applied at UI level, so you will see this code in the C# code-behind files.

Users can be assigned multiple roles that provide different levels of access.  In the case of two roles with conflicting rights, SplendidCRM defaults to the least-access right.  This is similar to the way Windows access rights work.  If a user is assigned two roles, one that grants a right to an area and the other that takes it away, the result will be that the user does not have access to that area.


Access Control – Team Management

SplendidCRM also supports a Team-based management system that provides a row-level access to data.  Users that are assigned to teams can only see data that is assigned to their teams.  For example, team management allows our customers to define sales teams based on specific geographical areas and ensure that each team can focus only on their area.

While the Role Management logic is applied at the UI level, the Team Management logic is applied at the database level.  When Team Management has been enabled, any time we retrieve records from the database, we make sure that we filter the data by the teams that are available to the current user.

Team Management also supports the concept of a manager.  This is accomplished by creating special private teams for each user in the system.  Then, this private team is assigned to the manager of each user so that the manager can see all the same data.

New in SplendidCRM 4.0 is the ability to assign a record to multiple teams.  This allows you to grant multiple teams access to the same account, contact or lead.  This new feature is called Dynamic Teams and it can be enabled or disabled in the admin area.


Reporting

No CRM would be complete without the ability to report on the data within.  Database reporting has been around for many years, but instead of re-inventing the report designer, we have leveraged the tools provided by Microsoft. 

Many years ago, Microsoft released a report specification called Report Definition Language, otherwise known as RDL.  At SplendidCRM, we have leveraged this technology by integrating the Microsoft ReportViewer control into the web application.  This ReportViewer control can accept RDL and faithfully render the results in HTML or PDF. 

Staying true to our data-driven design, SplendidCRM has a simple report designer that helps our users generate an RDL-based report definition and store this definition in the database for future use.  We also allow our users to import RDL files generated by SQL Server Reporting  Services.  This provides enormous power and flexibility to our reporting system.  The report designer provided with SQL Server Reporting Services has the ability to design reports that include images, graphs and sub reports.  In fact, we use this very same report designer to create the sample Quotes, Orders or Invoices that we include in our Professional edition.


Auditing

SplendidCRM takes an old-school approach to auditing in that it uses triggers in the database to track all changes to all records in a specific table.  One of the big benefits to this approach to auditing is that it is very fast and very reliable.  The big downside to this approach is that it uses lots of hard disk space.  As hard disk space gets cheaper every year, we feel that the positive exceeds the negative.

Auditing data is stored in auditing tables that end in _AUDIT.  These tables should have all the same fields as the base table, plus a few extra fields to track the auditing.

Create Table dbo.ACCOUNTS_AUDIT

      ( AUDIT_ID             uniqueidentifier   not null

      , AUDIT_ACTION         int                not null

      , AUDIT_DATE           datetime           not null

      , AUDIT_VERSION        rowversion         not null

      , AUDIT_COLUMNS        varbinary(128)     null

      , AUDIT_TOKEN          varchar(255)       null

 

By contrast, SugarCRM determines the auditing information by manually comparing the previous record to the current record in the PHP.  Then, SugarCRM stores one row for each field that has changed.  There are many reasons why we do not take this approach with SplendidCRM.  Most significant of these reasons is that the SugarCRM approach means that all data must be processed by the PHP application in order for auditing to be properly added whereas the SplendidCRM allows any external application to insert or update data and still have its auditing information properly recorded.

The design of our auditing system allows us to create a high-performance workflow engine that can be based on any change in the data. 


Workflow

In general terms, a workflow is a background action that is performed based on a specified event or condition.  Workflow events can arise from changes in the database or they can be scheduled to run on a periodic basis.

A SplendidCRM workflow is comprised of two parts, the event that fires the workflow and the action that occurs as part of the workflow.  The workflow engine is triggered from and uses the auditing tables.  This auditing information allows us to compare the before and after records so that we can detect such actions as changing the assignment of an account to a different user.  We can detect an increase or decrease in value and we can simply fire an event when a new record is created.

SplendidCRM Enterprise has been tightly integrated with Windows Workflow Foundation so that the actions performed by the workflow can be just about anything.  Like the rest of the system, we have taken a data-driven approach to workflows in that we store the XAML of the workflow in the database so that it can be read and run as necessary.  And, in the same way that we have created a simple Report Designer, we have also created a relatively simple workflow generate that can be used to build a XAML-only workflow.


Additional Information

The SplendidCRM Application Platform is constantly being improved.  Please visit the SplendidCRM Software website to obtain the most recent version of the software:

http://www.splendidcrm.com

If you have any questions, please post them to the SplendidCRM Support forum:

http://www.splendidcrm.com/Forums/tabid/66/Default.aspx

 

Errors and Omissions

If you discover any errors or omissions in this document, please email support@splendidcrm.com.