CiviCRM 2.0 and mysql5 ...

We've been busy with the schema redesign for 2.0 and are quite pleased with the modification and the simplified schema. Here are a few of the changes and the impact

• Merging all the "contact_type" tables into one table (civicrm_contact), has simplified the select/insert/update code for edit/view contact. It has also made search more efficient by getting rid of one LEFT JOIN.
• Kurund is still working his way through the view/edit code for location changes. This change has gotten rid of another LEFT JOIN in the search query
• Creating dynamic tables on the fly for custom groups. We now do a select/update of multiple custom values in the same group in one select/update sql statement. Custom tables now have foreign key constraints to the "object" they are extending
• We have started using the ON DELETE CASCASE / SET NULL / RESTRICT to eliminate most of the "delete" code from the PHP code base.
• To handle deletion of the tables linked via entity_id/entity_table (like civicrm_note), we use MySQL triggers. Thus all object deletion is now handled by the database.

An example trigger is:

CREATE TRIGGER   civicrm_contact_ad
BEFORE DELETE ON civicrm_contact
FOR EACH ROW
BEGIN
  -- we delete the corresponding entity_id/entity_table entries
  -- from all the linked tables (note, log etc)
  DELETE FROM civicrm_note
        WHERE entity_id    = OLD.id
          AND entity_table = 'civicrm_contact';
  DELETE FROM civicrm_log
        WHERE entity_id    = OLD.id
          AND entity_table = 'civicrm_contact';
  DELETE FROM civicrm_task_status
        WHERE responsible_entity_id    = OLD.id
          AND responsible_entity_table = 'civicrm_contact';

END;

You can find our current triggers in our svn repository. At some point, we will move these definitions to the xml files of the relevant table to make them more localized. Its kinda cool to see how we are making the database do more stuff that it was designed to rather than mimicking it in PHP code. We'll also start using stored procedures and views when we can figure out how an where to best use them