Back to articles list
- 4 minutes read

Part 1 – How to Install SuiteCRM and Reverse Engineer Its Database

Customer Relationship Management applications (CRMs) have been around for some time. We could argue a lot about their advantages and disadvantages when comparing them to each other. In this series, we’ll take a look at the database behind one popular open-source CRM: SuiteCRM. We will also show how to install everything we need. In upcoming articles, we’ll organize the data model and see how it reacts to changes made on the user interface.

SuiteCRM: an Open-Source CRM

A CRM is a system that helps handle relationships with clients. CRMs are used to automate sales, marketing, support, and collaboration activities; they can also be used to analyze data. We can use previously stored data (client data, sales history) and contacts (calls, emails, messages, and chats) to define our target audience and to find the right way to present our products and services to them. Besides operational and analytical CRMs, collaborative CRMs are sometimes used. They can serve as a communication platform between our company and our suppliers and distributors.

SuiteCRM is the open-source version of SugarCRM. Both of them are modern CRMs that provide ways to significantly improve our business. We’ll now go through the SuiteCRM installation process and then import the CRM database into Vertabelo to see what’s inside.

How to Install SuiteCRM Locally

You can find everything you need on the SuiteCRM website. However, I decided to go with the Bitnami SuiteCRM version because it’s user friendly and easier to install. Personally, I would recommend Bitnami’s site for many other open-source packages.

The installation process is very straightforward. The Bitnami package will install everything needed to support SuiteCRM, including its MySQL and phpMyAdmin instances. The only manual job is to select a different port for MySQL. (Of course, that’s only needed if you already have MySQL installed locally.) You’ll also need to choose a username and password to access the GUI and phpMyAdmin.

What’s Next?

After a successful installation, you can access SuiteCRM via http://localhost/suitecrm URL. If everything went okay, you’ll see something like the form below:

Suite CRM login screen

Enter the username and password you chose during the installation process. After login, you’ll see the SuiteCRM administration screen, which looks like this:

SuiteCRM administration screen

We can already see “Sales”, “Marketing”, “Support”, “Activities” and “Collaboration” on the menu. So we really have the CRM installed!

This is the user part; we’ll come back to it later. Right now, we’ll take a look at the database. First of all, we’ll login to the phpMyAdmin instance that was installed with the SuiteCRM. We can access it at Type in “root” at the username prompt and enter the same password that you chose earlier.

We’ll export the whole database as an SQL script using the phpMyAdmin Export option.

Exporting the whole database as an SQL script with the phpMyAdmin Export option

The next step is to use Vertabelo to import our model and take a better look at it. After we click on Create new model in Vertabelo, we’ll be redirected to a screen like the one below.

Creating a new database model in Vertabelo

We’ll input the model name and choose the database engine (MySQL). After that, we’ll use the Choose file button in the “From SQL” section to select the file with the SQL script from the disk. Click on Import SQL and if everything went fine we’ll see “The model was imported successfully.”:

Importing SQL code as an initial database model in Vertabelo

Click on the START MODELING button to begin modeling in Vertabelo.

The Imported Database Model

Now we’re into the Vertabelo model structure screen. We can take a quick look at the model:

SuiteCRM database model after importing to Vertabelo

The first thing you’ll probably notice is the five (!) warnings. They are all caused by the same problem: the missing primary key in 5 tables – the address_book, config, custom_fields, email_cache and users_feeds tables. These missing keys will not cause any problems in Vertabelo and we can continue our work as usual. As in other systems, warnings are not errors, but they are used to point out something that could be a logical error or an inconsistency.

Another obvious thing is that we have 201 tables in our model. So far, they are just placed around on the screen. In the next instalment, we’ll reorganize the table layout to make our Vertabelo model more user-friendly.

Notice that this model lacks references. The reason is that SuiteCRM uses the MyISAM storage engine, which doesn’t support foreign keys. Relations are defined at the application level in the vardefs.php file.

Up Next: Rearranging Our Model

Vertabelo offers 3 ways to organize our model:

  1. By subject areas
  2. By table and view shortcuts
  3. By reference shortcuts

Details are presented in this YouTube video. In the next article, we’ll use these tips to organize our model in a few functional areas.

CRMs are complex and they need to cover many different purposes. That is the main reason why the SuiteCRM database has more than 200 tables. MyISAM is used as a storage engine because it offers better performance. The authors chose to lose the foreign key constraints and make this performance possible. Next time, we’ll explore a few more Vertabelo features designed to handle models of this size.

go to top