Award Sounds

I posted earlier about a diagram of vtiger's data schema. The actual scheme is referenced at vtiger's wiki.

It is not an ERD (Entity Relationship Diagram) nor is it a Class Model. That's what I was originally looking for since I spend more of time working at the logical or conceptual levels rather than the physical.

Reverse-engineering a diagram from the tables in the database provides a physical table model, which is what I presented earlier as the schema diagram.

I would still like to see an ERD or Class Model. And so I dug around some more.

Some Background

vtiger's origins are in Business-to-Business (B2B) sales and marketing. This is what initially turned me off from using vtiger more heavily a few years ago. Although I could make a good estimate of what customisations would need to have been performed in order to get vtiger to work in a B2C (Business-to-Consumer) model, I couldn't see how the customisations would be propagated through upgrades. So I would have been left with a older customised B2C installation or a new, upgraded B2B installation.

However, the vtiger team have been busy.

Contact and Account

First of all, let's get some terminology sorted for the vtiger world:

• A Contact is a Person
• An Account is an Organisation, whether a commercial company, charity, co-operative, government department, etc.

In the initial vtiger, the Contacts were almost an afterthought. It was all about the Account. This restricted the usefulness of the application, e.g. a Quote required an Account, but what happens when you're selling to a person who doesn't have a Account?

The vtiger team have broken the links between Accounts and many of the other entities (e.g. Sales Order, Quote, Trouble-Ticket). On the physical model, this means that many of the columns that you'd think of as foreign key columns now allow nulls. Depending on the diagramming tool, this results in a diagram with lots of isolated tables, without links to other tables. This is configured in the user interface in the module configuration editor. The administrator selects for a given module (read "entity) whether Account is a mandatory field.

How useful would the generic ERD be?

So let's say you extrapolate the physical table schema to an ERD, you'd see lots of entities connected to Accounts by weak (i.e. zero or more to many) relationships. Although it would show what vtiger handles, it wouldn't show what you want to do with it. So yes, it's definitely possible to obtain an ERD/Class Model for vtiger, but the weak relationships would mean that the diagram is not that useful. The conclusion I've come to with vtiger is that I'll develop ERD/Class Models for a specific vtiger installation, not for the generic application.

Develop an ERD for vtiger as you wish to use it

In the case of a B2C model, I'd develop an ERD that shows the fact that Account is not mandatory for many of the entities. It would show Accounts relating to Contacts and that most of the entities would relate to Contacts instead as mandatory. This is useful documentation for other people who would have to support that particular installation.

A word of warning

The implication of this devolution of Account and Contact is that the referential integrity is now not managed at the database level. There are fewer foreign key constraints to ensure your data remains internally consistent (note the number of un-connected tables in the schema diagram).

Since it's on MySQL (well it is on my version), then there aren't any database procedures either, not like Oracle's PL/SQL or similar database-resident code. It means that the data's integrity is managed through application-server/webserver-side code. Fortunately, vtiger have released an API library, services and a development library (I think the API is part of this). You can see more in the 5.1 release post.

Now that the referential integrity is managed through code rather than foreign-key constraints, updates (including import routines) should make use of the same code. I am concerned about the number of references to updating the database through SQL on the vtiger wiki and forums. I am sure that some of the posts including SQL statements pertain to the previous versions and, with vtiger being open-source, documentation isn't always up-to-date. And as always, some things you can only fix with SQL.

I am more concerned that there are still remains of code (at best) and developers (at worst) who are still coding SQL into the application that accesses the database directly rather than through services. Not really being a developer, I've yet to investigate fully whether there is adherence to the concept of data encapsulation, i.e. that data for a class can only be updated by the class' methods.