Where can I get the ER diagram for the latest CiviCRM? I need it for internal reporting purposes in our organization.
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?
Is there any Test plans/ Cases written, which I can catch hold off?
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
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:
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.
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
countryhas an associated
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
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
idcolumn 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
- Many-to-many relationships use "join tables" as intermediary tables. For example, a
contactcan have many
activityrecords, and an
activitycan have many
contactrecords. So the table
civicrm_activity_contactis 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_valueswhen they just need a simple (and user-configurable) list of options. For example, look at
civicrm_contributionwhich 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_idcolumn actually references the
civicrm_option_values(but only for records in
civicrm_option_valueswith 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_notewhich has columns
entity_table. This is because a
notecan be attached to different entities (e.g.
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.
- Lots of columns reference
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.