Database Relationships
A brief overview of relationships mean in a Relational Database like MinistryPlatform.
Each table in a Relational Database contains one kind of record. Those records relate to each other using special columns to tie them together. These special columns are known as primary and foreign keys. Below is a partial representation of the table relationships centered around Contacts. For a data dictionary of all database tables in HTML format, email Support.
Finding Relationships in MinistryPlatform
There is much evidence of these primary and foreign key relationships in the Platform. Here are three:
Advanced Search
The expandable folders in Advanced Search represent foreign keys. When you select these, the Platform adds a JOIN to your query to get fields from related tables. The Advanced Search uses the Table Lookup Convention to represent table joins.
Link Navigation
The small link icons represent foreign keys and related tables. Click these links to follow the relationships to pages which represent the related tables. Click the link next to Contact Status: Active to go to the Contact Statuses page, landing on the record "Active."
Drop-Down Lists & Pick-Lists
When editing a record, drop-down lists and pick-lists represent foreign keys. When you select an item, you store the primary key value of a related table in the foreign key column of the page table.
Data Models & Tables
A guide outlining the different types of SQL relationships and tables you'll find in the Platform.
One-To-Many
In one-to-many relationships, the data uses a foreign key in the "one" which matches the primary key in the "many".
A Contact has one Contact Status. Many Contacts can have Contact Statuses.
A Contact has one Household. Households can have many Contacts.
A Household has one Congregation. Congregations can have many Households.
Many-To-Many
To model many-to-many relationships, use a third table (an associative table) to associate two indirectly related tables. The associative table contains a foreign key for the primary keys in the two "many" tables.
A Group can have many Participants. A Participant can be in many Groups.
Audit Log Tables
The following tables store the Audit Log. You can't directly access the tables in the Platform, but you can use them in your views.
See the Contact Changes View Example.
To establish a relationship between dp_Audit_Log and the page that has your view, define the Table_Name. For example, Table_Name = 'Events'.
Contact Tables
The following tables are closely related to Contacts. This is only a partial representation.
Files Table
This table stores information about all attached files. You can't directly access this table in the Platform, but you can use it in your views.
See the Contacts With Files View Example.
Series Tables
The following tables store series. You can't directly access these tables in the Platform, but you can use them in your views.
Series records use a soft join because they can refer to multiple tables. The Table_Name column matches the name of the table and Record_ID matches the primary key for the sequence. For example, the following SQL returns the sequence for the Event 1783:
SELECT SR.Sequence_ID
FROM dp_Sequence_Records SR
WHERE SR.Table_Name = 'Events' AND SR.Record_ID = 1783See the Events In Series View Example for an example.
Task & Submission Tables
This group of tables stores information about all tasks and the status of active process submissions, including the current step.
See the Contacts With Tasks View Example.