Ctrlk
Contact Us

Data Dictionary

Browse the tables, columns, and relationships of your data mart from within PEARS.

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

TIP: Access to the Data Dictionary is included with the data mart. If you do not see it in the Support and Resources menu, contact the PEARS Support team.

Open the Data Dictionary

1

Navigate to Data Dictionary

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

2

Browse the table list

A set of tables is displayed. From this view, you can click on a row to see the details of a table.

There are four options in the drop-down filter:

  • 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.

  • 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.

  • Custom Tables — Includes all tables that are required to store responses to custom modules.

  • 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 use the Search box to the left.

3

View table details

Once a table is selected, click on its row to display the table details. At the top of the page, you will see the description of the table and an estimated row count. Below that is a tabular layout of the table's columns.

4

Review incoming references

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.

Columns Section

The Columns section includes the following:

Column
Description

Keys

An indication of whether the column is part of a key. PK indicates a primary key and U1, U2, etc. indicate 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.

Incoming References Section

The Incoming References section includes the following:

  • 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.

Multiplicity Labels

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 be exactly one referenced. That is, the referencing foreign key column is not nullable.

Example

The incoming reference of action_plan_outcome (action_plan_id) 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.

Last updated