Answers.Body: <p>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 <em>separate</em> question for it)</p>
  
  <h2>Why an ER (Entity Relationship) diagram might <em>not</em> be helpful</h2>
  
  <p>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 <em>generate</em> such a diagram with a special tool, but in the end, my attempts to learn from this diagram were a fantastic waste of time.</p>
  
  <p>In my opinion, CiviCRM's schema is just <em>too big</em> 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.</p>
  
  <h2>Other ways to learn about CiviCRM's schema</h2>
  
  <h3>Using MySQL workbench</h3>
  
  <p><a href="https://www.mysql.com/products/workbench/" rel="nofollow noreferrer">MySQL workbench</a> is an awesome tool that has helped me tremedously in understanding CiviCRM's schema. It's similar to phpMyAdmin, but IMHO <em>way</em> 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 <em>copy</em> 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.</p>
  
  <p>A typical workflow would be: </p>
  
  <ol>
  <li><p>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.</p></li>
  <li><p>Pick one table and look closer. Let's look at <code>civicrm_country</code> (a relatively simple example) by opening the "Table inspector" for that table. </p>
  
  <ul>
  <li><p>We can look at the list of columns in the table. </p>
  
  <p><a href="https://i.sstatic.net/G8oVB.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/G8oVB.png" alt="View of columns"></a></p>
  
  <p>We can see that columns have <em>comments</em> which explain (to some extent) the meaning of the data stored in the columns. </p>
  
  <p>From the column names (and comments), we can also get <em>some</em> idea of the relationships between tables. For example, we can see that each <code>country</code> has an associated <code>address_format</code>. Cool! </p></li>
  <li><p>We can look at the foreign keys which are relevant to this table.</p>
  
  <p><a href="https://i.sstatic.net/EsFzn.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/EsFzn.png" alt="View of foreign keys"></a></p>
  
  <p>Here, we get an even more comprehensive picture because, not only do we see tables referenced by <code>civicrm_country</code>, we see tables <em>which reference</em> <code>civicrm_country</code>. So we can see that each <code>country</code> has <em>multiple</em> <code>state_province</code>s.</p></li>
  </ul></li>
  </ol>
  
  <h3>Using other tools (e.g. phpMyAdmin)</h3>
  
  <p>While MySQL Workbench is the <em>best</em> tool I've found for inspecting the CiviCRM data structure, other tools (such as phpMyAdmin) offer lots of the same functionality.</p>
  
  <p>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: </p>
  
  <ul>
  <li>Every table has an <code>id</code> column as a primary (and thus unique) key. </li>
  <li>Columns which reference other tables are named (usually) with the table name + <code>_id</code>. For example when you see a column called <code>address_format_id</code>, you can be relatively sure that it references <code>civicrm_address_format.id</code></li>
  <li>Many-to-many relationships use "join tables" as intermediary tables. For example, a <code>contact</code> can have <em>many</em> <code>activity</code> records, and an <code>activity</code> can have <em>many</em> <code>contact</code> records. So the table <code>civicrm_activity_contact</code> is used as the glue because it has foreign keys to both.</li>
  <li>In some places CiviCRM defines schema using a construct called <a href="https://docs.civicrm.org/dev/en/latest/framework/schema-definition/#table-field-pseudoconstant" rel="nofollow noreferrer">pseudoconstants</a> which produces some slightly more complex logic 
  
  <ul>
  <li>Lots of columns reference <code>civicrm_option_values</code> when they just need a simple (and user-configurable) list of options. For example, look at <code>civicrm_contribution</code> which has a column called <code>payment_instrument_id</code>. You'll notice there's no table called <code>civicrm_payment_instrument</code>. So in this case the <code>payment_instrument_id</code> column actually references the <code>value</code> column in <code>civicrm_option_values</code> (but only for records in <code>civicrm_option_values</code> with the appropriate <code>option_group_id</code>.) Here there is no foreign key, so referential integrity is managed at the application layer, not the database layer. </li>
  <li>Some tables use "dynamic foreign keys". For example, look at <code>civicrm_note</code> which has columns <code>entity_id</code> and <code>entity_table</code>. This is because a <code>note</code> can be attached to different entities (e.g. <code>contact</code>, <code>contribution</code>, etc). So <em>two</em> columns are used to indicate what the note references. Here again, the <em>application layer</em> is responsible for ensuring referential integrity, so you won't find any foreign keys. </li>
  </ul></li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-29T00:32:57.053Z
Answers.Text: I generally concur with Sean, but also understand why seeing the linkages around complex features like Contributions, Transactions, Line items etc could shed some quick light for newbies. A total map would be overkill, but some sub-maps could be v useful. Also, as a Mac user, I have found Sequel Pro to be very helpful in learning about and navigating around databases, as well as moving on to writing queries etc
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-01T17:16:21.710Z
Answers.Text: Thanks for this detailed answer! For others that might be interested, perhaps you could briefly explain what ER is?
Answers.UserDisplayName: William Mortada
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-02T12:36:50.487Z
Answers.Text: An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. An entity in this context is a component of data. In other words, ER diagrams illustrate the logical structure of databases https://www.smartdraw.com/entity-relationship-diagram/
Answers.UserDisplayName: Parthibaraj Rajasekar
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2017-07-28T18:25:47.490Z
Answers.LastActivityDate: 2018-12-21T00:24:02.030Z
Answers.LastEditDate: 2018-12-21T00:24:02.030Z
Answers.UserDisplayName: Sean
Answers.Body: <p>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. </p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-10T18:03:32.087Z
Answers.Text: Thanks Joe - I had a go at seeing if I could get the financial maze out using MySQL Workbench but it didn't play nicely with my set up
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-10T14:36:04.537Z
Answers.LastActivityDate: 2017-08-10T14:36:04.537Z
Answers.UserDisplayName: Joe Murray
Answers.Body: <p>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.</p>
  
  <p>You can see an example (and use) of SchemaSpy doc generated for CiviCRM 4.7 <a href="https://doc.symbiotic.coop/dev/civicrm/v47/schema/index.html" rel="nofollow noreferrer">https://doc.symbiotic.coop/dev/civicrm/v47/schema/index.html</a></p>
  
  <p>EDIT: For those interested, there is a <a href="https://github.com/schemaspy/schemaspy/releases" rel="nofollow noreferrer">more recent version</a> of SchemaSpy - see with latest CiviCRM database : <a href="https://doc.symbiotic.coop/dev/civicrm/latest/schema/index.html" rel="nofollow noreferrer">https://doc.symbiotic.coop/dev/civicrm/latest/schema/index.html</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-30T19:30:08.250Z
Answers.Text: very interesting, esp being able to get the diagram with either just one or two degrees of relationship once you click on a table name
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-05-29T18:48:37.803Z
Answers.Text: Up to date ERDs available here:  
  https://docs.civicrm.org/dev/en/latest/api/ERDs/#contact-info
Answers.UserDisplayName: gotjosh
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2017-08-30T13:51:34.953Z
Answers.LastActivityDate: 2018-06-06T15:55:03.127Z
Answers.LastEditDate: 2018-06-06T15:55:03.127Z
Answers.UserDisplayName: samuelsov
Body: <ol>
  <li><p>Where can I get the ER diagram for the latest CiviCRM? I need it for internal reporting purposes in our organization. </p></li>
  <li><p>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? </p></li>
  <li><p>Is there any Test plans/ Cases written, which I can catch hold off?</p></li>
  </ol>
  
Tags: <developer><mysql><documentation><testing>
Title: ER diagram for the latest Civicrm , Foreign key in Structure, test cases/plans
ThreadText: # ER diagram for the latest Civicrm , Foreign key in Structure, test cases/plans
  
  (Asked by: username_0 on 2017-07-28)
  
  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?
  
  **Tags:** developer, mysql, documentation, testing
  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_5 on 2017-08-30. Score: 4 | Accepted answer
  
  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](https://github.com/schemaspy/schemaspy/releases) of SchemaSpy - see with latest CiviCRM database : <https://doc.symbiotic.coop/dev/civicrm/latest/schema/index.html>
  
  > **Comments:**
  > **username_2**: very interesting, esp being able to get the diagram with either just one or two degrees of relationship once you click on a table name  
  > **username_6**: Up to date ERDs available here:   https://docs.civicrm.org/dev/en/latest/api/ERDs/#contact-info  
  
  ---
  
  ### Answer by username_1 on 2017-07-28. Score: 7
  
  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](https://www.mysql.com/products/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.
  
       [![View of columns](https://i.sstatic.net/G8oVB.png)](https://i.sstatic.net/G8oVB.png)
  
       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.
  
       [![View of foreign keys](https://i.sstatic.net/EsFzn.png)](https://i.sstatic.net/EsFzn.png)
  
       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_province`s.
  
  ### 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](https://docs.civicrm.org/dev/en/latest/framework/schema-definition/#table-field-pseudoconstant) 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.
  
  > **Comments:**
  > **username_2**: I generally concur with username_1, but also understand why seeing the linkages around complex features like Contributions, Transactions, Line items etc could shed some quick light for newbies. A total map would be overkill, but some sub-maps could be v useful. Also, as a Mac user, I have found Sequel Pro to be very helpful in learning about and navigating around databases, as well as moving on to writing queries etc  
  > **username_3**: Thanks for this detailed answer! For others that might be interested, perhaps you could briefly explain what ER is?  
  > **username_0**: An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. An entity in this context is a component of data. In other words, ER diagrams illustrate the logical structure of databases https://www.smartdraw.com/entity-relationship-diagram/  
  
  ---
  
  ### Answer by username_4 on 2017-08-10. Score: 2
  
  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 @username_1 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.
  
  > **Comments:**
  > **username_2**: Thanks Joe - I had a go at seeing if I could get the financial maze out using MySQL Workbench but it didn't play nicely with my set up  
  
  ---
  
  