This ERD is contributed and maintained by Ben Vautier from http://www.sqlrecipes.com
Pictures have been updated for version 1.7
It is worth noting that some tables have a 'entity_table' and a 'entity_id' field, such as the 'civicrm_location' table. This allows multiple tables to have a relationship with 'civirm_location'. The 'entity_table' will store the name of the table name, such as 'civirm_contact' and the 'entity_id' field stores the 'id' of the entity contained in the referenced 'entity table'.
An example should clarify this 
. Let's say we want to add a new location for contact_id 7. In the 'civicrm_location' table we would have 'entity_table' = 'civicrm_contact' and 'entity_id' = '7'.
This means that we can add a location to any entity contained in any table.
The picture below focuses on the "group" tables.
The "civicrm_contact" table is missing because it would be too messy to include it. However you can see it's relationship in the picture above.
The "civicrm_group_contact" is a child of "civicrm_contact".
This is a picture of the relationship between the civicrm_uf_* tables. The "uf" letters stand for "User Framework". The tables are used to manage 'users' from Drupal or Joomla with contacts in CiviCRM. The civicrm_uf_match is especially important as it stores the information that matches the contact_id with the user id (or uf_id) in Drupal or Joomla.
Contribution Tables
Custom Tables
Mailing List Tables
ACL Tables
Membership Tables
As many tables as possible
By now you should be able to work yourself around... hope it is not too confusing
This ERD is contributed and maintained by Ben Vautier from http://www.sqlrecipes.com
Thanks Ben!
Great contribution.
Thanks, this is great. BTW, what package produces these ERD's from a MySQL db?
I used dbWrench. It is cross platform and works with Postgres, MS SQL as well as MySQL. www.dbwrench.com
This is awesome. Wish I'd seen it a week ago! Please, please, please keep this updated for each revision!
We could not use "source" to store data in the civicrm_contact "source" field using crm_create_contact or crm_update_contact. Per Dave Greenberg, "contact_source" should be used instead.
Updated by Ken Lyle
Nov 26, 2007 06:21
I have been staring and staring, and didn't see any relation between Contact and Email.
Here is what I have found in 1.9 - sorry if the terminology isn't right:
contact.id=individual.contact_id=location.entity_id
(where entity_table="civicrm_contact") and
location.id=email.location_id
This information will let you get information from contact and associate it to email.
Here is a query in Access that strips spaces from the Display Name and associates it with the primary email:
SELECT Replace(LCase(Trim([display_name]))," ","") AS Expr2, civicrm_email.email
FROM (civicrm_contact INNER JOIN civicrm_individual ON civicrm_contact.id = civicrm_individual.contact_id) INNER JOIN (civicrm_location INNER JOIN civicrm_email ON civicrm_location.id = civicrm_email.location_id) ON civicrm_individual.contact_id = civicrm_location.entity_id
WHERE (((civicrm_email.is_primary)=1) AND ((civicrm_location.entity_table)="civicrm_contact"));
Enjoy!
Ken
In tinkering with a Joomla to Drupal Conversion, I generated an Access-ODBC connection to Drupal53-Civi19, and used the Access Documentor to dump a primitive data dictionary.