Document toolboxDocument toolbox

(2022.1) Data Dictionary

Owned by Jeremiah Sokolowski
Jan 08, 2024

About the Data Dictionary

Overview

The Data Dictionary allows an Admin User to view the Database Definitions for all tables within the primary Database Schema that PCR-360 uses in order to display information to all Users. The Data Dictionary itself is built dynamically, so as new tables are added or updated between versions, it remains current.

Each Table Definition lists the:

  • Column Names: Field name within the Database Table for accessing the values.
  • Expected Data Type for each Column: Only compatible values to the data type list will be allowed to be saved here. The Application enforces this with validation rules on forms and in the API.
  • Default Values: The default value the Application writes to the Databases every time a new record is created. 'NULL' or '0' are often set as default to indicate no User input was made.
  • Required Field Indicator: Indicates if a value is expected to be in this field. This corresponds with required fields within Application Forms.
  • Field Label: Shorthand reference for verbally communicating values.
  • Field Description: This indicates the use of this field within the Application.
  • Foreign Key Table Reference: This indicates what table the data found in this field comes from. The Application uses this constraint to prevent someone from removing information other records depend on.

By using the Data Dictionary, a User can quickly identify related tables and the information stored within them to assist in the writing and updating of custom data queries using AdHoc Grids, the API and Custom Logic. For ease of use, the Data Dictionary is sorted Alphabetically, and a set of quick jump navigation links are available for quickly locating Table Definitions.

Tips and Tricks

Here are some usual details to keep in mind when going through the Schema:

  1. Within the Data Dictionary are multiple Database Views. These Views are searchable objects that do not store data on their own, but reference data found else where within the Database Schema. Views can be thought of as Virtual Database Tables, and can assist a User in getting related data without having to go digging through multiple tables individually.
  2. Record Identification Numbers (RECIDs) are unique IDs for records within the table. When one table references a specific record from another table the Table Name is usually also part of the RECID the other table is referencing.
    1. IE: SERVICES_RECID in any given table is referencing the RECID value from the SERVICES Table
    2. Some table names are very long, and need to be abbreviated to meet the 30 character limit imposed by Oracle Databases.

Using the Data Dictionary

AdHoc Grids

When using AdHoc Grids, the Query Builder only displays all tables within the Database, and all columns contained within a selected table. In order to locate other related data, the User would need to look in each table to attempt to find what tables might be desirable to access for any given Database lookup. This makes writing and maintaining custom grids slow and inefficient. The Data Dictionary rectifies those problems by letting a User jump from table definition to table definition by data links the Application is already using for data relationships between tables to generate any given data record.

For more information on Adhoc Grid usage, please see the Reporting:AdHoc Grids page .

API

All API data querying is intended to use the SQL End Point. This end point only expects the User to send a POST request with a valid SQL SELECT statement. In order to filter the response from the API, and efficiently view all expected related data, a User would either need to utilize AdHoc Grids or other API pages in order identify linkages. The Data Dictionary simplifies this by giving the User a high level overview of the Database Table Definitions. With this access, the User is better able to develop API calls to lookup desired records.

For more information on the API, please see the API "Getting Started" page .

Custom Logic

In order to implement complex Custom Logic. For complex look up where data is needed that is spread out across tables, a User would be limited to what documentation is present for Application features intended to be used. The Data Dictionary opens up the documentation so that everything is readily available for creating user defined API Calls, Events and Validations.

For more information on Custom Logic, please see the Custom Logic "Getting Started" page .