ER diagram for the latest Civicrm , Foreign key in Structure, test cases/plans

Question

1. Where can I get the ER diagram for the latest CiviCRM? I need it for internal reporting purposes in our organization.

2. I am able to download the Table structure from phpMyAdmin. However, it does not give details about foreign key references. Can I get it automated? Or I have to manually get it done?

3. Is there any Test plans/ Cases written, which I can catch hold off?

Answer 1

2) You can use a tool like SchemaSpy to extract the structure of the database, have the foreign key displayed in a table or on a schema and navigate easily between related tables.

You can see an example (and use) of SchemaSpy doc generated for CiviCRM 4.7 https://doc.symbiotic.coop/dev/civicrm/v47/schema/index.html

EDIT: For those interested, there is a more recent version of SchemaSpy - see with latest CiviCRM database : https://doc.symbiotic.coop/dev/civicrm/latest/schema/index.html

Answer 2

I will answer your questions #1 and #2. (I'm not sure what you mean by #3. If #3 is sufficiently unrelated to #1 and #2, I would suggest creating a separate question for it)

Why an ER (Entity Relationship) diagram might not be helpful

As a visual learner, I too looked for an ER diagram when first trying to understand the CiviCRM database schema. I was eventually able to generate such a diagram with a special tool, but in the end, my attempts to learn from this diagram were a fantastic waste of time.

In my opinion, CiviCRM's schema is just too big to make any sort of diagram useful. There are too many tables. (Currently over 150, and more when you add custom fields!) At this scale, a diagram becomes a rat's nest of confusion.

Other ways to learn about CiviCRM's schema

Using MySQL workbench

MySQL workbench is an awesome tool that has helped me tremedously in understanding CiviCRM's schema. It's similar to phpMyAdmin, but IMHO way better. It's a desktop application. You can use it to connect to a remote server like your live CiviCRM site, but for purposes of learning I'd recommend that you install MySQL locally and load a copy of your live CiviCRM database onto your local computer. Then you inspect this local database with MySQL workbench and it will be both faster and safer than looking at the live one.

A typical workflow would be:

1. Look at the list of tables in the database. The table name generally gives you a good idea of the type of data it stores.

2. Pick one table and look closer. Let's look at civicrm_country (a relatively simple example) by opening the "Table inspector" for that table.

   • We can look at the list of columns in the table.

     

     We can see that columns have comments which explain (to some extent) the meaning of the data stored in the columns.

     From the column names (and comments), we can also get some idea of the relationships between tables. For example, we can see that each country has an associated address_format. Cool!

   • We can look at the foreign keys which are relevant to this table.

     

     Here, we get an even more comprehensive picture because, not only do we see tables referenced by civicrm_country, we see tables which reference civicrm_country. So we can see that each country has multiple state_provinces.

Using other tools (e.g. phpMyAdmin)

While MySQL Workbench is the best tool I've found for inspecting the CiviCRM data structure, other tools (such as phpMyAdmin) offer lots of the same functionality.

Specifically, looking at the names of the columns in each table goes a long way towards understanding the relationships between tables. For the most part, the following patterns hold true:

• Every table has an id column as a primary (and thus unique) key.
• Columns which reference other tables are named (usually) with the table name + _id. For example when you see a column called address_format_id, you can be relatively sure that it references civicrm_address_format.id
• Many-to-many relationships use "join tables" as intermediary tables. For example, a contact can have many activity records, and an activity can have many contact records. So the table civicrm_activity_contact is used as the glue because it has foreign keys to both.
• In some places CiviCRM defines schema using a construct called pseudoconstants which produces some slightly more complex logic
  • Lots of columns reference civicrm_option_values when they just need a simple (and user-configurable) list of options. For example, look at civicrm_contribution which has a column called payment_instrument_id. You'll notice there's no table called civicrm_payment_instrument. So in this case the payment_instrument_id column actually references the value column in civicrm_option_values (but only for records in civicrm_option_values with the appropriate option_group_id.) Here there is no foreign key, so referential integrity is managed at the application layer, not the database layer.
  • Some tables use "dynamic foreign keys". For example, look at civicrm_note which has columns entity_id and entity_table. This is because a note can be attached to different entities (e.g. contact, contribution, etc). So two columns are used to indicate what the note references. Here again, the application layer is responsible for ensuring referential integrity, so you won't find any foreign keys.

Answer 3

We strongly discourage interacting directly with tables and fields in the database. Instead it is much safer to access data via the API, which has a convenient explorer.

For those doing core work, and sometimes for extension developers who wish to extend functionality, an ERD is a useful idea. I don't think we have created a set recently due to the problems mentioned in @Sean Madsen's answer. Still, it would be good documentation to have. It's been more successful in the past to limit each diagram to a set of more tightly coupled tables, like the contact, email, location, address tables, or the financial tables. I've put it on my To Do list to create one for the financial tables, and will update this answer if/when I get to that.