Product Support

Multi-Relational Database

diagram of a hierarchical arrangement of record types

Expands Participants Database into a relational database. Define different types of record to represent and interact with complex data structures.

This plugin also provides a way to use Participants Database as several separate databases.

Product Setup

How the Plugin Works

This plugin works by making it possible to define “types” for your records. A record’s type declares what the record represents, for example what job or role the person in the record has, or to what group the record belongs. These types or groups can be linked to each other, giving you a way to create an interrelated database representing different things and how they are related to and interact with each other.

The example we will use to explain how to set up and use the plugin is a database for a school. In this example, the school database has 4 types: Teachers, Students, Parents and Books. Teachers will have Students, and Students will have Books. Teachers can have Books too, but not if a Student already has it. Parents will also have students, and this gives the Teacher a way to know who each Student’s Parent is, and possibly that other Students have the same Parents. This model could be expanded to include things like Administrators, Teams, Clubs, Lockers, etc.

Before You Begin

This plugin will be easier to set up if you have figured out the structure of your records beforehand. Like the example above, you will end up with several different types of record that can be related to each other in various ways. It will be helpful to take some time to think about how you want the data represented and what is going to give your users an intuitive understanding of how things are related. This plugin can lead to extremely complex structures (maybe even too complex!), so it is best to think it all through first so that you end up with the simplest effective database structure that fills your needs.

All type relations are modeled as “parent/child” relations, where a record of a parent type includes, controls, or possesses records of the child type. A record can be parent to some types and child to others. For example a Student is a child to a Teacher (Teachers can have Students), and also a parent to a Book (Students can have Books). Records can also be of several types at once, for example a record for a person who is a Teacher and also a Coach.

When independent or automatic types are used, these types can be used as ways to group records together and are often based on some fact about a person, such as where they live or what department they work for. Independent record types can also work as “tags” which are useful for assigning traits to a record. Records can and often will have several type designations of that kind.

Setting Up the Plugin

Here is a brief overview of the steps to take to set up the plugin. For each type you need to define, repeat these steps:

  1. Select the “Add New Type” tab and define a record type with its names
  2. Open the tab for the new type
  3. Set the mode and determination method of the type
  4. Determine the field groups the type uses
  5. Set up the record title
  6. Configure how the type displays its members

Once you have your types defined, go back and set up the relations for the types that are relational.

Defining a New Record Type

To define a record type, go to the Multi-Relational Db settings page, under the Add New Type tab you’ll see two text inputs. This is where you set the plural and singular names for your record type. For example “Student” and “Students” Type in your names and click “+Create Type” to create it. The configuration for the new type will be under its own tab on the settings page. The displayed name of the type can be changed any time, but the internal name for the type (which cannot be changed) is established with the singular name you chose. It’s best to keep that simple initially.

Setting up the Type’s Mode and Determination Method

Open the Multi-Relational Db settings page to configure your types. Each type will have a tab to access the configuration of the type.

The Record Type Mode setting determines the basic way the type behaves, and can be configured as “relational” or “independent.”

A relational type is related to other relational record types in ways which you define in each type’s configuration.

An independent record type is not related to other types, which provides a way to set up an independent database. Independent types can also be used for passive grouping, such as to create a group based on location where all records that are in the group’s configured location will be members of that group. Another way to use independent types is to function as “tags” that assign specific shared traits to records.

The Record Type Determination setting sets up how a particular record knows which type it is. The determination can be automatic or manual. The record’s type determination is stored in the Type Determination field, found in the Internal field group.

Manual determination means that an administrator will set the record’s type manually, which can be done in the admin record edit page or, for multiple records, on the admin List Participants page using a “with selected” operation. This is a good way to go for types where the record’s type doesn’t need to change: the admin sets what type it is and that’s it. It is always possible to change a record’s type, an administrator can add or delete type designations at any time.

Automatic determination is based on values in the record. This is well suited to types that work as a logical container or group of records. For example, you can define a type that is based on the record’s location, such as the country or state. If you set up the determination this way, a record’s type can change when the record is changed. For example, if the city of the record changes, that might put it into or out of a type that is defined by city.

The automatic determination uses the same format as the Participants Database List Shortcode Filter uses, so you can set up complex conditions for determining the type.

Records with Multiple Types

A record may have several types at once, although it’s best not to get too complicated with this. It works well for a record to have two or three type designations. For example, a Teacher (manual type) that is also a member of the Senior Staff (manual type) and UK country group (automatic).

Setting the Type’s Database Fields

Each type may have its own set of database fields to use, including the use of fields that other types are using. This is enabled with the Use Field Groups setting. Checking this setting opens the field groups selector.

The Enabled Field Groups configuration is based on field groups, so it may be necessary to visit the Manage Database Fields page to create and organize the fields you will need for each group. If you want to use some fields in common with other types, the common fields should be in their own group, since it is not possible to assign individual fields to a type. It is much more efficient to use common fields where possible. If, for example, several of your types have a “name” field, you should set that up as a single field that all the types are using, instead of the unnecessary complexity of having a separate “name” field for each type.

image of the field group selector

To set the fields the type uses, there is a multi-checkbox interface with all the available field groups. When when a record of the type is displayed or edited on the frontend, only these fields will be shown.

Setting Up a Relational Type

Defining the Record’s Title

When setting up a relational database, it’s necessary for each record to have a name or title that is determined for it’s type. This is so lists of related records can be shown, also so that relations can be added/removed from a record. The Record Title Template setting determines how the title is constructed. This setting uses “value tags” to represent data from the record that will be used to build the record’s title. For example: [first_name] [last_name] Title templates may also include static text, but no HTML tags.

Bear in mind that if the source data for the title of a record changes, the title will be changed too, so it might be a good idea to choose something that won’t change to avoid confusion. Internally, the record is represented by its ID, so the title getting changed will not affect the record’s relations, the title is for display and interaction purposes only.

The record’s title is defined by the type, so if a record has more than one relational type, it’s title could be different for each type. The record title is always displayed in the context of the type, so it should be clear that the same record can be known by more than one title.

Configuring the Type’s Relations

To do this, you will need to have more than one relational type configured, so before you set this part up, create all the relational types you need to use.

Each record type can be put into a “parent” or “child” relation to other types. A “parent” type has or owns its “child” records. You can also think of the type relations as “container” or “contained” since you can use record types as a way to group records.

Setting the Relations

If the record type is supposed to be a child to other types, you select which parent types can have it as a child record using the Parent Types control. This is a series of checkboxes that determine which type the current type is a child to, and you can select multiple parent types.

The Child Types setting is read-only. If the type you are editing should be set as a parent to another type, you must do that in the child type’s configuration and select it as the parent.

The Exclusivity setting determines how many parents the child record can have. This would be used if the situation is such that if one parent claims a child record, no other parent record can claim that same child record. See the Settings section on this page for more detail on using this setting.

Configuring the Relations Field Display and Interactions

A record’s relations are displayed using the relations field for the type. Each type has its own relations field, and so displaying the record’s type relations is controlled in the same way as any other database field. Generally speaking, a relations field shows the list of type relations that a record has, for example the Student relations field for a Teacher’s record would show a list of all the Teacher’s Students.

There are three contexts in which relations are shown: Record Edit, Single Record Display, and the List Display. For each one, you can set how it displays and (for the Record Edit display) what kind of interactions are allowed.

More information about configuring how the relations field is displayed in each context is found in the Settings section.

Relations fields are not shown in the Signup form because there is no way to determine the relations to a record that does not yet exist. It is, however, possible to set up a signup form that pre-sets the type of record that the submitted record will be. Automatic types will also be applied to records submitted through the signup form if the values needed to determine it are included in the submission.

The Type’s Relations Field

Each record type has a corresponding “Relations” type field in the main database.  The type’s field provides you with a way to control how those relations are displayed and interacted with. The field stores the number of relations so you can filter and search by that number…for example show all Teachers with more than 5 Students.

You can configure the field’s visibility, title, etc. on the Manage Database Fields page. In particular, you will probably want to set the field group for the relations field, since the plugin makes a guess as to which group to place the field in when it is created.

The Relations field is used to display the list of records of the type that are related to the displayed record. In other words, the Student relations field can show a list of all the Teacher’s Students when viewing the Teacher’s record. Each Student record will show the names of their Teachers in the Teachers relations field.

Relations Field Configurations

There are several optional configurations that can be made in the relations field definition on the Manage Database Fields page. These configurations are placed in the attributes setting for the field.

new_child_page sets the destination page for creating a new record of the type. This page must have the [pdb_record] shortcode so that the data for the new record can be filled in. The page slug name or page ID is expected here.

new_child_button sets the text for the “add new child record” button. This has a special format for placing the type name in the button: “%1$s” for the singular type name, “%2$s” for the plural name. For example: Add a new %1$s to your %2$s which would display something like: “Add a new Student to your Students”

layout selects the layout mode for the relations list. This can have one of two values: “column” generates a vertical list, while “row” generates a horizontal list. The default layout is “column.”

max-height sets a limit to the height of a relations list. If the list is longer than the max height setting will allow, it will be scrollable.

All the normal attributes can be used here as well.

Viewing Record Types in the Admin Participant List

Once you have your types defined and record have their types assigned, you can view records by type in the admin List Participants page. There is a new selector in the filter “Type Designation” where you can select the record type to display.

Select “None” to show all records that have no type designation.

Using Record Types in Shortcodes

This plugin adds a new shortcode attribute: “type”. This can be used to focus the action of the shortcode on a specific record type. This can be used with both independent and relational types, giving you an easy way to set up registration for a specific type of record. For example to set up a registration for Students, you can use a shortcode like this:

[pdb_signup type="student"]

You can use the “type” attribute for other shortcodes too, for example to show a list of all the Teachers, you could use a shortcode like this:

[pdb_list type="teacher"]

You can set multiple types too:

[pdb_list type="teacher,student"]

Setting the Type in the URL

You can also set type value in the URL for a page with a Participants Database shortcode. This is done using the “pdbtype” variable. For example if your signup form is on /signup you would add it to the end of the url like this:

/signup?pdbtype=student

You might do this if you wanted to make it possible for users to create records of different types, but using the same page for all of them. You’d do that by providing a link to the signup page that included the name of the type that the new record should be.

This technique can also be used for other shortcodes, for example the list shortcode as explained above. This is a way to use the same page for several different types, but it can be tricky unless you have some technical knowledge how to set up special URLs in WordPress.

Using the REST API

The multi-relational database is fully accessible to the Participants Database REST API. You can use all of the base REST endpoints to list, search, add, delete, and edit any record in the database. You can set the record’s type designations using the record edit or add endpoints. Please consult the Participants Database REST API page for the details.

The Multi-Relational plugin adds it’s own endpoints for interacting with the relations between records.

Do not attempt to use REST requests that change or create records to set relations, only the specialized endpoints below should be used for that purpose. This means that in general you should not include any “relations” fields in your REST requests, because these fields are maintained automatically by the plugin.

Get Requests

These are used to get information about the current relations that a record has. The request must include a parameter that names the relation you want to query.

Get Children: record/children/{record_id}?relation={relation_name} – this will return a list of the record IDs of all the children of the named type for the record.
Get Parents: record/parents/{record_id}?relation={relation_name} – this returns the record IDs for all parent records of the named type for the record.

For example, if you want to find the Students for a Teacher, and the Teacher’s record ID is 640, you’d use a request like this:

https://{your domain}/wp-json/participants-database/v1/record/children/640?relation=student

Post Requests

These are used to set the relations for a particular record. You can use these to add child records to a parent record, remove them, etc. Each post request must include the name of the relation in the “relation” key of the body. The ID or IDs of the records to add or delete must be in the “related_record” key of the request body. Multiple IDs are sent as a comma-separated list of record IDs.

You should use caution with these, as it is possible to (for example) add a child record of the wrong type to a record. Make sure that the relations configuration agrees with what you’re trying to do with these requests.

Add Child Record: record/add_child/{record_id} – adds child records of the specified type to the record
Add Parent Record: record/add_parent/{record_id} – adds a parent of the specified type to the record
Delete Child Record: record/delete_child/{record_id} – removes the child records from the record
Delete Parent Record: record/delete_parent/{record_id} – removes the parent record from the record

Product Settings

Add New Type Tab

This is where you initialize a new record type. Type in the singular and plural name for the type and submit. This will create the type’s tab where you can configure it.

Record Type Tabs

Each record type will have its own tab for its configuration.

Record Type Name

This is the read-only display of the identifier name for the type. This is the name you will use to identify the type in direct configurations, for example in the “type” shortcode attribute.

The type’s “name,” once established, cannot be changed, but the “labels” defined below can be changed at any time.

Record Type Label Singular

The singular display name for a single member of the type.

Record Type Label Plural

The display name for more than one member of the type.

Record Type Mode

The type’s mode can be either “Relational” or “Independent” This basic setting determines the behavior of the type: An Independent type won’t have other records related to it, although it will have members (records that are of the type). Independent types can be used to set up virtual multiple databases, each with their own registration forms and displays.

Record Type Determination

This sets the method used to determine what type a particular record is.

Manual means that the type is set by an administrator either when editing the record or on the admin List Participants page, using the “with selected” function “Add type designation” or “Remove type designation” and then selecting the type to add or remove. When editing a record in the admin, you can set the record’s type designations under the Record Info tab, using the Type Designation field.

A manual type can also be created with a signup form configured to create the type using the “type” shortcode attribute.

Automatic means that the record’s type is determined by values in the record. The criterion used to determine the record type is configured using a filter configuration string. This filter works the same as List Shortcode Filters which makes it easy to create a complex set of conditions for the type determination.

In the example shown, the type would include all records from the city of Seattle.  These determinations are updated whenever the record is updated. If the automatic determination filter setting is changed, all the records are checked for possible changes to the type assignments.

Use Field Groups

For some record types, you will want to only use field groups that are relevant to the type. The is often the case for types that are the main designation for the record. When this is enabled, you can control which field groups are used when displaying or editing a record of this type.

This is usually unselected for types that are used as general groupings or tags.

Enabled Field Groups

When “Use Field Groups” is enabled, this configures which field groups the type uses. This gives you a way to create independent datasets for different types of record.

Field groups are also used to provide fields that are common to multiple types.

The rules for which field groups are used can be complicated if the record has multiple types that use field groups. The general rule is that all the field groups for all types will be used, so if you are showing a record that is both a Student and a Teacher, enabled field groups from both types will be shown. If you need to choose a single type that determines the field groups, you can use the “type” attribute of the shortcode for that.

Record Title Template

For relational records, it is necessary to have a title for the record so the related records can be shown in lists. This setting defines how that title is constructed for each type.

The template works the same way as other templates in Participants Database work: you use a “value tag” to access a value in the record, the rest of the template is made up of literal characters such as spaces. For example, a basic title template for a record that represents a person could be:

[first_name] [last_name]

It is possible to add static text to the title template, but no HTML tags are allowed, since the title will often be wrapped in a link. Keep it simple, these titles are used to display lists, a too-long title will cause display issues.

Parent Types

This setting determine which types may be related to the currently edited type as a parent. For example if you’re editing the Student type, “Teachers” would be checked here because Teachers can have Students.

A warning when allowing multiple parent types: if a parent record has type designations for more than one parent type for a child, there will be configuration issues if an individual parent record is a parent to a child record in more than one way (this is called a “parallel relation”). It leads to a lot of complexity that may be unnecessary, so we recommend you avoid a setup like that.

To give an example of a parallel relation: a teacher in the school has their own child in a class. The teacher’s record ends up as both a Teacher and a Parent to a single Student record. If this is something you need to do, it is best to define additional child types, so each parent has a single relation to a particular child record via a single type. For the example given, you’d add a Child type which has Parent as a parent type (you’d remove Parent from the parent types for the Student type). Then the Student whose Teacher is also their Parent would have two types: Student and Child. The Teacher would be the parent of the Student and the Parent would be the parent of the Child. This will work much more intuitively than allowing the parallel relation.

Child Types

This is a read-only display of all the types that can have a child relationship to the currently edited type.

For example, a Student type would have “Books” listed here. To assign a child type to the current type, you need to save your changes and open the child type tab and select the parent type there.

Child Limit

It is possible to set a limit to the number of child records that can be attached to a record of the currently edited type. Each registered child record type has a limit setting. A setting of 0 means there is no limit to the number of child records of the type that can be attached.

For example, if you’re setting up the Teacher type, you can set the maximum number of students that a teacher can claim by setting the “Students” value in the Child Limit setting.

Exclusivity

This controls what child records will be available to a parent type based on whether another parent record has it.

Not Exclusive means that there are no restrictions on which parent can choose a particular child record.

Exclusive to the Parent means that the child record can only belong to a single parent of a type.

For example, if you have Teachers and Counselors and this option is selected for Students, then if a Teacher has a particular Student, no other Teacher can have them, but a Counselor can. Once a Counselor has selected their Student, no other Counselors may claim that same Student.

Exclusive means that only one parent of any type may have a particular child. For example, both Teachers and Students can have Books, but if either a Teacher or a Student has a particular Book, no other type can have it.

Relations Field Display Options

Every relational record type has a corresponding database field, which will be of the “Relations” type. This field provides control over how lists of relations are displayed. For each of the 3 display contexts there is a group of settings.

There are two built-in display templates that are used when displaying the list of related records: “responsive” and “table”. The responsive template is the default, if you want to use the table template, you can set that in the relational field’s definition by adding a “template” attribute…for example template::table. If you want to customize your own template, and you have some coding skills, there is a filter pdbmrdb-relations_list_template_{template_name} that you can you to supply your own template structure for the relations list display.

Record Edit Display

In the record edit display, a record can choose its relations based on its type. These controls configure those choices.

Child can Choose this Type: when checked, a record type that is a child of the currently edited type can choose it as a parent record.

For example if the current type is Club, this would be checked because Students (as a child type of Club) can choose which club to belong to.

Parent can Choose this Type: when checked, a record type that is a parent of the currently edited type can choose it as a child record.

For example, if the current type is Student, this would be checked because a Teacher can choose their Students.

Parent that can Create this Type: It is possible to allow parent-type records to create child type records. This control selects which parent type can do this for the currently edited type.

Show Related Record List: enabled showing a list of the related records. The link settings determine what kind of link is placed on the items in the list: Detail Link takes the user to the single record page to display the record. Edit Link takes the user to the participant record page to edit the record. No Link leaves the item unlinked.

Show Related Count: when enabled, shows a summary statement with the number of related records.

Single Record Display

Show Related Record List: enabled showing a list of the related records. The link settings determine what kind of link is placed on the items in the list: Detail Link takes the user to the single record page to display the record. No Link leaves the item unlinked.

Show Related Count: when enabled, shows a summary statement with the number of related records.

List Display

Show Related Record List: enabled showing a list of the related records. The link settings determine what kind of link is placed on the items in the list: Detail Link takes the user to the single record page to display the record. No Link leaves the item unlinked.

Show Related Count: when enabled, shows a summary statement with the number of related records.

Update Record Type

After making any changes to the configuration of the type, you must click this button to submit the changes.

Delete Record Type

Use this button to delete the type. It will remove all references to the type, but it will not delete any records. This action cannot be undone!

If you want to delete all records of a type, do this on the admin List Participants page: Use the filter to show all records of a type, then select all and use the “with selected” control to delete the records.

F.A.Q.

Can a parent record have multiple relationships to a child record?

Yes and no. You can set it up, but you won’t have separate control over each relationship.

While a record can have multiple types, a single record cannot have multiple relations to the same child type record…for example if a person (record) is both a Teacher and a Parent, it cannot be both Parent and Teacher to the same record that is a Student type.

If you need to have a setup like that, you can do that by creating more child types: for the example above, the child record would need to have two types such as Student and Child, that way the parent record can establish each relationship it has with the child record.

Support Discussions for Multi-Relational Database

  • Is there a way to interact with multi-relational database via the API? I have a use case where I need to be able to write data to the database using the API, but I would like to be able to assign a Staff Member to a School when importing via the API. If the School does not yet exist, I would want to create it. I know I could just do all of this using MySQL queries, but I would prefer to use the API if possible.

    • I have not tested this, so I checked to see how this works.

      Yes, you can interact with the relational database via the REST API. In every way, all records in the multi-relational database are the same, so you can add or edit records and set or change their type values (a record can have multiple types).

      What you cannot currently do with the API is set a specific relationship. For example, you can create a student record, but you cannot set which teacher or teachers they have. I would like to make this possible, but as it is now, if you try this, you’ll get an error. I will be working on an update to make this possible.

  • Hi there! I’m most of the way through setting up my Multi Relational DB for a petcare website – Client records, Pet records, and Booking records, with both Pets and Bookings being child records to Clients.
    I’m struggling though to set up the ability for Clients to Add New Child records. When accessing the Manage DB Fields page, the Relations fields are in the Record Info group and do not have an Attributes field to adjust.

    • I had a lightbulb moment and deleted all of my Record types and recreated them. The Relations fields populated in Administrative Info, which means I can actually set them up now!

      • Yes, makes sense, the relations fields will not normally be in the “Record Info” group, which shouldn’t be modified by the user.

Got a Support Question?

Your email address will not be published. Required fields are marked *

Would you like to be notified of followup comments via e-mail? You can also subscribe without commenting.