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.
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)
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
If you discover any errors or omissions in this document,
please email support@splendidcrm.com.