Part 2 – How to Organize a Large Database Diagram

In Part 1 of this series, we successfully imported the SuiteCRM database structure into our online database modeling tool. That’s when we saw that the model contains 201 tables without relationships between them. We got a wild bunch of tables that looked really messy. In this article, I will show you how you can organize such a large model.

Just after importing to Vertabelo, the SuiteCRM database model looks as follows:

The model does work – but not efficiently. We will need to modify it to make it really useful. Since we want to analyze the SuiteCRM database after actions are performed on its GUI, we need to understand table definitions and the relationships between tables. Let’s start by grouping tables into subject areas and establishing the most important relationships.

Step 1. Disable the Automatic Generation of Foreign Keys

First of all, we’ll disable the automatic generation of foreign keys. By default, Vertabelo generates foreign key attributes when we pull relations from a primary table to a referenced table. This is usually a good thing, but not here. We already have attributes that represent foreign keys. What we lack is “real” relationships between tables. To turn this option off, click “My account” in the top menu and find the “Personal preferences” section.

The option is off. Now, when we draw a reference line between tables, the line is created – but we’ll have to specify which attributes are used, on both the primary and foreign sides.

Step 2. Group Prefixed Tables With Subject Areas

Next, let’s group some tables. We’ll do this using the Subject area tool that lets to associate tables based on selected criteria. In our case, we’re trying to identify tables that are either related or part of the same process. This will result in groups like “Calls”, “Meeting”, and “Campaigns”.

We can create a subject area by clicking the “Add new area” icon in the toolbox:

and then drawing a rectangle on our model:

The subject area is created. We can see it in the “Model structure” panel on the left:

Each subject area contains a list of all the objects that are inside its borders; in this case, these are tables and reference types.

In SuiteCRM, there are many tables that share a common prefix. So, I started grouping the prefixed tables together. Take a look at the “acl” tables as an example. In the “Model structure” panel, I found all the tables which names started with “acl_“:

Then I created the “acl” subject area in the model and dragged all the appropriate tables into it. (For better visibility, I set the background color to purple.)

Now, we can now see the “acl” group, with a list of all tables belonging to it, under “Subject areas” in the “Model structure”:

I repeated the same procedure for all the remaining prefixed tables.

Step 3: Arrange the Remaining Tables.

Same Table Twice in the Diagram? Table Shortcuts!

There are about 80 prefixed tables. After grouping them, I was left with around 120 ‘wild’ tables. These are meaningful: they store information about users, clients, calls, meetings, and other CRM stuff. That’s a lot of information to remain at large, so let’s get these tables sorted.

The feature I found most useful for arranging these tables is called table shortcuts. Sometimes you want to use same table more than once in a model. (Why? To flatten the model and avoid overlapping.) We can easily do this by using the “Copy” and “Paste as shortcut” buttons.

Just select the table for which you want to create a shortcut and click “Copy” in the top toolbar (or press Ctrl + C):

To create a shortcut, click “Paste as shortcut” (or press Ctrl + K). After that, a new table with a dotted outline will appear:

This is not a copy of the table, but another instance of the original table. We can place it anywhere in our model. I used instances of the same table in different subject areas to avoid overlapping references. It’s worthwhile to mention that every table instance has an assigned subject area name (next to its name) while it is inside that subject area.

A good example of how this works is the users table. It can be found in “User and Accounts”, “Roles”, “Documents”, and other subject areas. We’ll see this later in the model.

I use table shortcuts extensively when creating subject areas with established relationships between tables. To see how this works, look at the “Opportunities” subject area mapped out below. Notice that all the tables within that subject area are named following this rule: {table name} : {subject area name}.

When we expand the {subject area name} in the “Model structure” panel, we can clearly see that it contains tables and references:

I did this for the following subject areas: “Calls”, “Cases”, “Campaign”, “Contacts”, “Documents”, “Meeting and leads”, “oauth”, “Projects”, “Prospects and email marketing”, “Roles”, and “Users and accounts”. All of these areas share a light blue background.

The remaining tables are grouped based on their name and presumed meaning: “Emails”, “Users (extra)”, and “Other tables”. These groups have their background color set to light red.

The Arranged Model

I used the previously-described options to flatten the model as much as possible while grouping tables logically. The result is 26 subject areas, some of which contain only tables while others have tables and relations. Let’s have a quick review of each category:

Subject Areas That Contain Tables and Relations:

“Calls”, “Campaigns”, “Cases”, “Contacts”, “Documents”, “Meetings and leads”, “Opportunities”, “Projects”, “Prospects and email marketing”, “Roles”, “Users and accounts”

All relations are set as non-mandatory. This keeps the information that these tables are related and via what attribute(s).

Subject Areas That Contain Tables Only:

“acl”, “am”, “aod”, “aok”, “aop”, “aor”, “aos”, “aow”, “Emails”, “fp”, “jwg”, “oauth”, “security_groups”, “Users extra”

This doesn’t mean that relations don’t exist here; they’re just not being emphasized.

The “Other tables” subject area is for tables that don’t really fit into a specific group.

What Does the Model Look Like?

The rearranged model looks like this:

Obviously a naming convention has been used. Here’s an overview of the guidelines we followed:

1. Table names are mostly plural: users, contracts, folders, roles, tasks. Some table names are singular, such as project.
2. The primary key in most tables is called simply id and is a char(36) type.
3. When a one-to-many relation occurs, the foreign key is usually named parent_id. (Example: contacts_audit.parent_id is a reference to contacts.id.)
4. In many-to-many relations, “parent_id” can’t be used as name for multiple columns. Instead, a singular table name with the suffix “_id” is used. (Example: contacts_bugs.bug_id is reference to bug.id.)
5. There are situations when the same column is used as a foreign key for multiple tables. (Example: calls.parent_id is referenced to the id column in each of the following tables: accounts, bugs, cases, contacts, leads, tasks, opportunities and prospects. I haven’t checked the values in the database, but my guess would be that there are no same key values in these tables. Since all are char(36) type, probably some combination of table name and autoincrement is used. We’ll check that in upcoming articles.)
6. We use the same names for columns that have the same meaning in different tables. (Example: modified_user_id, created_by and assigned_user_id can be found in many tables in the model. All of them are referenced to users.id.)

What’s Next?

In the upcoming articles, we’ll use the SuiteCRM GUI and keep an eye on the changes that this causes within the database. With that information, we’ll try to make changes in the model, reorganize subject areas, and establish connections where needed. Also, we’ll look for other SuiteCRM-specific rules, such as the way primary keys are generated.

Handling large database diagrams is never an easy job. Like building a good foundation for a home, spending more time on the fundamentals now will bring advantages later. If we want to analyze models like the one behind SuiteCRM, analyzing before we have organized the model structure and defined table relationships is doing it Sisyphus-style.