One-To-Many

In One-To-Many Relationships, the data is modeled using a Foreign Key in the "One" which matches the Primary Key in the "Many".

Example: Contacts > Contact Statuses

A Contact references one Status. Statuses can be referenced by many Contacts.

Example: Contacts > Households > Congregations

A Contact references one Household. Households can be referenced by many Contacts.

A Household references one Congregation. Congregations can be referenced by many Households.

Many-To-Many

For modeling Many-To-Many relationships, a third (Associative) table is used to associate two indirectly related tables. The Associative table contains a Foreign Key for the Primary Keys in the two "Many" tables.

Example: Groups > Group Participants > Participants

A Group can have many Participants. A Participant can be in many Groups.

Audit Log Tables

The following tables store the Audit Log. The tables cannot be accessed directly 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 on which your View exists, define the Table_Name. For example, Table_Name = 'Events'.

Contact Tables

The follow tables are closely related to Contacts. This is only a partial representation.

Files Table

This table stores information about all attached Files. The table cannot be accessed directly 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. The tables cannot be accessed directly 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 will match the name of the table and Record_ID will match the primary key for the sequence. For example, the following SQL would return the sequence for the Event 1783:

SELECT SR.Sequence_ID 
FROM dp_Sequence_Records SR 
WHERE SR.Table_Name = 'Events' AND SR.Record_ID = 1783

See the Events In Series View Example for an example of this in action.

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.