Data Dictionary

The Data Dictionary provides information about the tables in the data mart, including the columns, descriptions, and relationships.

From the PEARS homepage, hover the cursor over the Support and Resources menu, displayed as a question mark icon, then click Data Dictionary.
A set of tables is displayed. From this view at any time, you can click on a row to see the details of a table.
Note there are three options in the drop-down filter:
1. Primary Tables – Includes all high-level tables that can serve as the “starting point” for browsing related tables. For example, when opening action_plan, there will be options to follow links to related tables such as action_plan_collaborator or action_plan_outcome.
2. List Tables – Includes all tables with a list_ prefix. The list tables contain the options that appear in drop-down lists throughout PEARS, such as list_county or list_program_area.
3. All Tables – All tables in the data mart will be displayed. This is helpful if you want to scroll through the complete set or know a specific table you would like to see. In the latter case, select “All Tables” then utilize the Search box to the left.
Once a table is selected and you click on its row, the table details will be displayed.
At the top of the page, you will see the description of the table and an estimated row count. Additionally is a tabular layout of the table’s columns. This section includes:
1. Keys – An indication of whether the column is part of a key. PK indicates a primary key and U1, U2, etc. indicates a unique constraint.
2. Column Name – The name of the column.
3. Data Type – The data type of the column, such as integer or text.
4. Is Required – Indicates whether a value is required in the column for every row. A checkmark indicates the column is required, which means the column is defined in the table with a NOT NULL constraint.
5. Referenced Table – If the column is a foreign key referencing another table, the referenced table and the specific column referenced in that table is displayed. For example, organization (id) shows that the column is referencing the id column of the organization table.
6. Description – A description of the column.
Below the Columns section is the Incoming References section. This section lists all columns, typically from other tables, referencing this table. Each reference is implemented as a foreign key constraint in the database.
The following columns in this section are displayed:
1. 1. Referencing Table – The table referencing the current table displayed above and which column in that referencing table.
  2. Description – The description of the referencing table.
  3. Referenced Column – The column of the currently displayed table that is referenced.
  4. Multiplicity – Indicates the type of relationship between the referencing table and the referenced table.
    - - Possible labels on the referencing (left) side:
        0..* – zero or more – The referencing table may have multiple rows referencing a single row in the referenced table.
        0..1 – zero or one – The referencing table can have no more than one row referencing a single row in the referenced table. This is determined by whether the referencing column(s) makes up a key in the referencing table.
      - Possible labels on the referenced (right) side:
        0..1 – zero or one – The referencing column is not required. That is, the referencing foreign key column is nullable.
        1..1 – one and only one – The referencing column is required, so for every referencing row, there will exactly one referenced. That is, the referencing foreign key column is not nullable.
For example, the incoming reference of action_plan_outcome (action_plan_id) is shown below and provides the following information.
1. - The action_plan_id column of the action_plan_outcome table is referencing the id column of the currently displayed action_plan table.
  - There can be zero or more rows in action_plan_outcome referencing a single row in action_plan.
  - Because action_plan_id is a required field in table action_plan_outcome, there will be one and only one action_plan row referenced.

✓ TIP: At any time on the table detail view, click the button "Back to List" to navigate back to the list of tables to select another one.

INFO: A key is one or more columns that uniquely identifies a row in the table. Primary keys (PK) are typically a single id column. A set of columns all labeled with "U1", for example, forms a composite key, meaning the columns together uniquely identify a row.

✓ TIP: The referenced tables are links! You can click on a referenced table to navigate to the detailed view of that table, seeing its columns.

✓ TIP: The referencing tables are links! You can click on a referencing table to navigate to the detailed view of that table, seeing its columns.

Need Help?

Contact PEARS Support

The Data Dictionary provides information about the tables in the data mart, including the columns, descriptions, and relationships.

From the PEARS homepage, hover the cursor over the Support and Resources menu, displayed as a question mark icon, then click Data Dictionary.

Note there are three options in the drop-down filter:

List Tables – Includes all tables with a `list_` prefix. The list tables contain the options that appear in drop-down lists throughout PEARS, such as `list_county` or `list_program_area`.

All Tables – All tables in the data mart will be displayed. This is helpful if you want to scroll through the complete set or know a specific table you would like to see. In the latter case, select “All Tables” then utilize the Search box to the left.

Once a table is selected and you click on its row, the table details will be displayed.

At the top of the page, you will see the description of the table and an estimated row count. Additionally is a tabular layout of the table’s columns. This section includes:

Keys – An indication of whether the column is part of a key. PK indicates a primary key and U1, U2, etc. indicates a unique constraint.

Column Name – The name of the column.

Data Type – The data type of the column, such as integer or text.

Is Required – Indicates whether a value is required in the column for every row. A checkmark indicates the column is required, which means the column is defined in the table with a `NOT NULL` constraint.

Referenced Table – If the column is a foreign key referencing another table, the referenced table and the specific column referenced in that table is displayed. For example, `organization (id)` shows that the column is referencing the `id` column of the `organization` table.

Description – A description of the column.

Below the Columns section is the Incoming References section. This section lists all columns, typically from other tables, referencing this table. Each reference is implemented as a foreign key constraint in the database.

The following columns in this section are displayed:

Referencing Table – The table referencing the current table displayed above and which column in that referencing table.

Description – The description of the referencing table.

Referenced Column – The column of the currently displayed table that is referenced.

Multiplicity – Indicates the type of relationship between the referencing table and the referenced table.

Possible labels on the referencing (left) side:

0..* – zero or more – The referencing table may have multiple rows referencing a single row in the referenced table.

0..1 – zero or one – The referencing table can have no more than one row referencing a single row in the referenced table. This is determined by whether the referencing column(s) makes up a key in the referencing table.

Possible labels on the referenced (right) side:

0..1 – zero or one – The referencing column is not required. That is, the referencing foreign key column is nullable.

1..1 – one and only one – The referencing column is required, so for every referencing row, there will exactly one referenced. That is, the referencing foreign key column is not nullable.

For example, the incoming reference of `action_plan_outcome (action_plan_id)` is shown below and provides the following information.

The `action_plan_id` column of the `action_plan_outcome` table is referencing the `id` column of the currently displayed `action_plan` table.

There can be zero or more rows in `action_plan_outcome` referencing a single row in `action_plan`.

Because `action_plan_id` is a required field in table `action_plan_outcome`, there will be one and only one `action_plan` row referenced.

Need Help?

Contact PEARS Support

The Data Dictionary provides information about the tables in the data mart, including the columns, descriptions, and relationships.

From the PEARS homepage, hover the cursor over the Support and Resources menu, displayed as a question mark icon, then click Data Dictionary.

Note there are three options in the drop-down filter:

List Tables – Includes all tables with a list_ prefix. The list tables contain the options that appear in drop-down lists throughout PEARS, such as list_county or list_program_area.

All Tables – All tables in the data mart will be displayed. This is helpful if you want to scroll through the complete set or know a specific table you would like to see. In the latter case, select “All Tables” then utilize the Search box to the left.

Once a table is selected and you click on its row, the table details will be displayed.

At the top of the page, you will see the description of the table and an estimated row count. Additionally is a tabular layout of the table’s columns. This section includes:

Keys – An indication of whether the column is part of a key. PK indicates a primary key and U1, U2, etc. indicates a unique constraint.

Column Name – The name of the column.

Data Type – The data type of the column, such as integer or text.

Is Required – Indicates whether a value is required in the column for every row. A checkmark indicates the column is required, which means the column is defined in the table with a NOT NULL constraint.

Referenced Table – If the column is a foreign key referencing another table, the referenced table and the specific column referenced in that table is displayed. For example, organization (id) shows that the column is referencing the id column of the organization table.

Description – A description of the column.

Below the Columns section is the Incoming References section. This section lists all columns, typically from other tables, referencing this table. Each reference is implemented as a foreign key constraint in the database.

The following columns in this section are displayed:

Referencing Table – The table referencing the current table displayed above and which column in that referencing table.

Description – The description of the referencing table.

Referenced Column – The column of the currently displayed table that is referenced.

Multiplicity – Indicates the type of relationship between the referencing table and the referenced table.

Possible labels on the referencing (left) side:

0..* – zero or more – The referencing table may have multiple rows referencing a single row in the referenced table.

0..1 – zero or one – The referencing table can have no more than one row referencing a single row in the referenced table. This is determined by whether the referencing column(s) makes up a key in the referencing table.

Possible labels on the referenced (right) side:

0..1 – zero or one – The referencing column is not required. That is, the referencing foreign key column is nullable.

1..1 – one and only one – The referencing column is required, so for every referencing row, there will exactly one referenced. That is, the referencing foreign key column is not nullable.

For example, the incoming reference of action_plan_outcome (action_plan_id) is shown below and provides the following information.

The action_plan_id column of the action_plan_outcome table is referencing the id column of the currently displayed action_plan table.

There can be zero or more rows in action_plan_outcome referencing a single row in action_plan.

Because action_plan_id is a required field in table action_plan_outcome, there will be one and only one action_plan row referenced.

List Tables – Includes all tables with a `list_` prefix. The list tables contain the options that appear in drop-down lists throughout PEARS, such as `list_county` or `list_program_area`.

Is Required – Indicates whether a value is required in the column for every row. A checkmark indicates the column is required, which means the column is defined in the table with a `NOT NULL` constraint.

Referenced Table – If the column is a foreign key referencing another table, the referenced table and the specific column referenced in that table is displayed. For example, `organization (id)` shows that the column is referencing the `id` column of the `organization` table.

For example, the incoming reference of `action_plan_outcome (action_plan_id)` is shown below and provides the following information.

The `action_plan_id` column of the `action_plan_outcome` table is referencing the `id` column of the currently displayed `action_plan` table.

There can be zero or more rows in `action_plan_outcome` referencing a single row in `action_plan`.

Because `action_plan_id` is a required field in table `action_plan_outcome`, there will be one and only one `action_plan` row referenced.