> For the complete documentation index, see [llms.txt](https://support.pears.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://support.pears.io/analyze/data-mart/data-dictionary.md).

# Data Dictionary

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

{% hint style="info" %}
**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.
{% endhint %}

## Open the Data Dictionary

{% stepper %}
{% step %}

### 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**.
{% endstep %}

{% step %}

### 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.
  {% endstep %}

{% step %}

### 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.
{% endstep %}

{% step %}

### 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.
{% endstep %}

{% step %}

### Review other constraints

Beside the **Incoming References** section is the **Other Constraints** section, which lists check constraints and unique filtered indexes defined on the table. When a column participates in one of these constraints, a superscript link such as `[1]` appears next to the column name in the **Columns** section—click it to jump to the matching constraint.
{% endstep %}
{% endstepper %}

## 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.
* **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.

## Other Constraints Section

Displayed beside the **Incoming References** section, the **Other Constraints** section lists additional constraints defined on the table that are not represented by keys or foreign keys:

* **Check constraints** — Rules that restrict the values allowed in one or more columns. For example, a check constraint might require that a numeric value be greater than zero.
* **Unique filtered indexes** — Enforce uniqueness across one or more columns, but only for the subset of rows that match a filter condition.

The section includes the following:

| Column         | Description                                                                                                                                                                                                                                          |
| -------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **#**          | A number identifying the constraint. When a column participates in a constraint, this number appears as a superscript link—for example, `[1]`—next to the column name in the **Columns** section. Click the link to jump to the matching constraint. |
| **Constraint** | The expression that defines the constraint.                                                                                                                                                                                                          |

### Example

The following constraints could appear on a table that supports either an event-level fee or a session-level fee:

* **Check constraint** — `(event_fee_id IS NOT NULL AND event_session_fee_id IS NULL) OR (event_session_fee_id IS NOT NULL AND event_fee_id IS NULL)`
  * This ensures exactly one of `event_fee_id` or `event_session_fee_id` is set for each row.
* **Unique filtered index** — `UNIQUE (event_fee_id, accounting_code_id, is_discountable) WHERE event_fee_id IS NOT NULL`
  * This ensures the same combination of `event_fee_id`, `accounting_code_id`, and `is_discountable` appears only once when `event_fee_id` has a value.

{% hint style="info" %}
**TIP:** If a table has no check constraints or unique filtered indexes, the section displays a message indicating there are no other constraints on the table.
{% endhint %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://support.pears.io/analyze/data-mart/data-dictionary.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.