Entity Relationship Diagrams

Entity Relationship Diagrams provide a different view into how the entities accessed through the API are related to each other.

Most of the entities in APIv4 and APIv3 are mapped directly to tables in the CiviCRM database. A few are not, notably the Order API and the Payment API.

Collected below are a number of Entity-Relationship Diagrams. The diagrams were generated by DBeaver, with notes added manually. Each foreign key relationship that is defined in the database between two tables is represented by a line connecting the tables. A table can also have a relationship with itself, for example, a parent_id field linking to a different record in the same table.

The lines do not indicate the specific fields joined by the relationship unfortunately. The solid circle at the end of a relationship line touches the table that has a foreign key referencing another table. So the solid dot indicates the 'beginning' of the relationship, and is touching the 'source' table. When the foreign key field in the souce table is defined as NOT NULL, then the other end of the relationship line will go straight into the destination table. However, if foreign key field in the source table is optional, then there is a rectangle on the end touching the destination table.

CiviCRM has a field naming convention that helps in determining which field is a foreign key to a different table. A table named civicrm_tablename1 has a field named id as its unique primary key. References from other tables to that id field are named tablename1_id. (There are a few exceptions, such as when two fields in one table reference the key of a single other table, for different purposes.)

CiviCRM has non-standard patterns that it uses to implement one table references one amongst several other tables. We term this a pseudo foreign key. In most cases there is a pair of fields, entity_table and entity_id, with entity_table containing the name of the table being referenced and entity_id representing the value of id of the record in that field being referenced. A different pattern is present in the civicrm_value_* tables that are dynamically created to store custom fields that extend various entities in CiviCRM. In that case, entity_id present in the table, and the entity_table value is found in the appropriate civicrm_custom_group.extends field.

To aid in viewing and understanding the 200+ tables in CiviCRM, each diagram collects a handful of related tables.

For convenience, here is a clickable list of the Entity-Relationship Diagrams below:

• ACLs
• Activities
• Batches and Queues
• Cases
• Contact Info
• Contribution Financial
• Contribution Page
• Contributions recur
• Custom Fields
• Deduping
• External Providers
• Groups, Tags and Campaigns
• Interfaces
• Line Items
• Mailings
• Participants
• Pledges
• Price fields and Premiums
• Relationships
• System

ACLs

ACLs are Access Control Lists that define permissions that a user has to view data of different sorts.

Activities

The purpose of an Activity is specific to the activity_type. There can be custom fieldset defined for Activities, including for specific types.

Batches and Queues

The queue and queue_item tables are general purpose, and helpful when long-running processes need to have a backlog of tasks. The batch table was originally created to support financial batches, both those created by navigating to Contributions > Batch Data Entry, and ones created manually at Contributions > Accounting Batches > New Batch.

Cases

These are the primary tables used by CiviCase, though it also makes extensive use of Activities.

Contact Info

This diagram illustrates the various tables containing contact information. These tables are more normalized than some schemas in data sources that contain multiple fields for the same type of content, for example, email1 and email2 or phone1 and phone2.

Contribution Financial

This diagram includes tables related to accounting information for contributions, memberships and event registrations. Double entry bookkeeping is represented by debit and credit accounts in civicrm_financial_trxn and civicrm_financial_item for most entries, and occasionally for some simple transactions by the from and to account fields in civicrm_financial_trxn. These are the authoritative entries for auditing purposes. The amount fields in civicrm_contribution and civicrm_line_item are often more convenient for some reporting purposes. However, the civicrm_contribution.financial_type_id table is a deprecated field since it cannot accurately represent transactions with multiple line items with different financial types.

Contribution Page

This diagram includes tables that store the settings defined when useing Contributions > Manage Contribution Page, and under Contributions > Manage Price Sets.

Contributions recur

Recurring contributions, and memberships that are paid with them, are illustrated here.

Custom Fields

CiviCRM databases typically have many sets of custom fields defined, and many tables named civicrm_value_*.

Deduping

The dedupe_rule_group has one record per rule in the user interface at Contacts > Find and Merge Duplicate Contacts. The dedupe_rule table has one row per 'field' entry for a particular rule.

External Providers

These tables are used to store information used to connect to some standard external services.

Groups, Tags and Campaigns

Groups, Tags and Campaigns help to segment and relate entities in CiviCRM.

Interfaces

These tables define some menu and navigation elements in CiviCRM and permissions for users to see them.

Line Items

There may be one or more line items related to a contribution, and each may relate to a membership, event registration or a different purpose.

Mailings

CiviMail is a powerful bulk emailing solution. Some of the tables in this diagram relate to the content of the emails, others are used during sending, and the remainder contain information on recipients interactions with those emails.

Participants and Events

Participants register in events. The registrations are stored in the same tables regardless of whether the event is paid or free.

Pledges

Pledges are prototypically used during capital campaigns to store promises to donate a certain amount over a period of time through payments to be specified later. When payments are made against the pledge, it reduces the amount owing.

Price fields and Premiums

Price fields are part of the configuration of what people can buy or donate to through a CiviCRM site. Price_field_values are the options for certain types of price_fields; other price field types like a text entry field for an amount do not require price_field_values.

Premiums are used by fundraisers to incentivize people to donate more, with products representing things like mugs or Tshirts that are given away for donating over a certain level.

Relationships

Relationships can be created between contacts. Relationship types can be used to limit which contacts can be involved in a relationship (eg only organizations can be employers and only individuals can be employees). Relationships can be used to define permissions, for example, parents can be allowed to see and edit their children's information.

System

This diagram includes some of the more important system tables.