CiviCRM uses the PEAR package DB_DataObject to access the mysql database. Doing so reduces the amount of sql we need to write to fetch / store values for a single table. For queries involving more than one table, we write them manually since since it was fairly painful to try to force complicated queries using DB_DataObject (we did use DB_DO for multiple table queries when we wrote EmailNow, in the end we felt the hoops we had to jump through was not worth the effort and hence the current scheme)
The basic PHP classes that represent a table are called DAO's (Data Access Objects). These are generated automatically by (xml/GenCode.php) which also generates the sql structure for that specific table. The xml files representing the schema is stored under CIVCRM_ROOT/xml/schema. The sql file generated (civicrm_41.mysql and civicrm_40.mysql) are stored under CIVICRM_ROOT/sql (note that the generated files are not present in the svn repository). The DAO files are stored under CIVICRM_ROOT/CRM/Core/DAO (and under appropriate directories based on what table it is, this is configured in the xml representation.
Each table has the following properties
Each table is made up of a combination of field, index, primaryKey and foreignKey.
Each field has the following properties
Each DAO file inherits from the base class CRM_Core_DAO, which in turn inherits from DB_DataObject.
Some of the important DAO properties are:
DAO objects inherit quite a few functions from the parent class (CRM_Core_DAO) and the grandparent class (DB_DataObject). These include functions to save and retrieve the object from the database along with a fair number of helper functions. For folks keeping track of CiviCRM issues and bugs, the memory issue was due to DB_DataObject caching all query results and CiviCRM not releasing them back to the system on a regular basis. (we still need to do some work to clean this part of the codebase)
Since DAO objects are auto generated, we cannot add any additional functionality to them. So we introduce a new object called the BAO (business access object) which inherits from the corresponding DAO object and adds business logic to this object. Thus the CRM_Contact_BAO_Contact knows all about the mysteries and links of a contact object and we add high level functions to create/edit/delete a contact object in the BAO.
For more details please browse the civicrm_41.mysql and the BAO/DAO files of your favorite table. An ERD of the CiviCRM schema (contributed by Ben V) can be seen here