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.

TIP: Tables and columns that are being phased out are flagged as deprecated in both the table list and the table detail view, giving you advance notice to update your reports before they are removed.

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.

5

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.

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, U1, U2, etc. indicate a unique constraint, and FK1, FK2, etc. indicate a foreign key.
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.

Composite Foreign Keys

Most foreign keys use a single column, but some reference another table through two or more columns together. These composite foreign keys are shown in full:

• Every column that participates in the foreign key carries the same FKn badge in the Keys column. A two-column foreign key shows FK1 on both of its columns.

• Each of those columns shows the referenced table in the Referenced Table column, naming the specific column that corresponds to its own position in the constraint.

• A column that participates in more than one foreign key shows every FKn it belongs to, and each of its references is listed in the same Referenced Table cell, labeled with the FKn it belongs to.

For example, event_session references event_session_group through event_session_group_id and event_id together — a rule ensuring a session and the breakout block it belongs to are always on the same event. Both columns are badged FK1, and each shows its own half of the reference.

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.

A composite foreign key appears as a single row naming all of its column pairs, rather than one row per pair.

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.

TIP: For a composite foreign key, the labels consider all participating columns together. The referencing side is 0..1 only when the full set of referencing columns is itself a key, and the referenced side is 0..1 if any one of the participating columns is 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.

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.