MySQL is great for building customer resource management (CRM) back ends to Web sites. It’s already an integral part of many Web sites, and its pricing model is almost unbeatable. Furthermore, in active sites, there is probably already an impressive volume of CRM data just waiting to be mined.

Working as Webmaster for a telephone company’s SAP implementation team, I became well versed in its prominent CRM package. I learned that about 90 percent of CRM is getting and keeping the system configured to meet the changing needs of the users. A CRM developer must be well versed in process and structural design. Let’s discuss the process by which you would build a scalable, high-performance CRM system with MySQL.

Designing CRM solutions for MySQL
CRM databases are complex, with your users table linked to your contacts table, which is linked to your addresses and organizations tables, which are linked to your transactions table, which is linked to your catalog table, etc. For some relationships you’ll need to build complex, compound indices. For other relationships you may require only simple indices, or none at all. And updates and deletes in your implementation may or may not be cascaded.

This means that you’ll need to be intimately familiar with the tweaking methods available in MySQL. But even before you tweak, you’ll need to design a CRM process that can take advantage of those tweaking possibilities. One thing you never want to do is to let the craziness of a database engine trick you into building a clunky user interface.

Logic and data flow
As you can see in Figure A, you can use MyISAM tables as the source for report-type data. This is useful because ISAM tables are lightning-fast sources when you’re simply querying the database. The drawback of ISAM is that the table files themselves can become corrupt, and updating their data is an easy trigger for that corruption.

Figure A
CRM design data flow

To deal with the instability of ISAM, you can use InnoDB tables to add, update, and delete records in the data tables. The InnoDB engine is transactional, so failed updates will rollback any changes. InnoDB is also more referentially integral, so data updates won’t violate any relationship rules between tables.

Not shown in the diagram is the fact that you should always back up your data. In this scenario, the gold code data is in the ISAM tables. Those are the tables that you would back up. You could get the same data from the InnoDB tables, but the ISAM ones are better for backup process querying.

Restore operations would go to the InnoDB tables for much the same reasons—they’re better for updates (e.g., referential integrity, speed, stability, etc.) and they would be automatically synchronized with any pending add/update operations. If the InnoDB table somehow becomes corrupt, it can be rebuilt from the ISAM data, which is an excellent reason for breaking the process up like this. After all, redundancy equals safety.

Note in Figure A that the line shown linking Table A and Table B is a one-way synchronization process. It involves locking the Report table (Table A, ISAM), then pushing the Update table (Table B, InnoDB) back out to Table A. This happens quickly because little or no data validation is occurring at this point. By design, MyISAM doesn’t support it.

Shrink-wrapped CRM
Of course, not all CRM software was designed to work with MySQL. Often they’ll support MySQL, but they won’t take advantage of its particular performance and design features. For instance, SAP, PeopleSoft, and Microsoft CRM don’t provide any MySQL optimization features. This is because they are developed according to Oracle and Microsoft’s RDBMS design paradigms.

There are many CRM packages designed around the LAMP (Linux/Apache/MySQL/PHP) foundation. These are often open source projects, with all the resulting benefits and costs that accompany it. Since CRM almost always involves a lot of software customization and business process analysis, it lends itself quite well to open source development. The kind of granularity of design that open source provides is necessary for ensuring that the system is in synch with the actual operation of the enterprise—at least as much as is possible.

Alternative CRM packages for MySQL

The following CRM packages have been optimized for use with MySQL:

Unique design paradigm
If you’re involved in building CRM solutions with MySQL, you’ll need a good mix of technological as well as business skills. Matching the interfaces in the system with their real-world counterparts requires an intimate knowledge of the performance-enhancing features available in MySQL’s unique design paradigm. Understanding MySQL’s transactional and nontransactional table types can be the key to understanding this paradigm, but functions such as index and key composition also come into play.

MySQL can be used as the back end database for large, popular CRM packages, but these packages often fail to take advantage of MySQL’s optimization features and design paradigms. However, many open source packages do take advantage of MySQL-specific features, or can be tweaked to do so at the source code level, making MySQL an excellent choice for your CRM project.