PPP360 Database Schema Overview
Table of Contents
- Introduction
- Schema Structure
- Entity Relationships
- Schema Details
- Data Flow
- Technical Implementation
- Business Logic
Introduction
The PPP360 database schema is designed to support 360 feedback reviews within the Professional Performance Portfolio (PPP) system. It enables the storage, retrieval, and management of comprehensive feedback data collected from multiple reviewers about medical professionals. The schema follows a hierarchical structure with three primary tables that store reviews, reviewer information, and their responses respectively.
Schema Structure
The PPP360 schema consists of three interconnected tables:
ppp360/
├── ppp360 # Main 360 review records
├── ppp360reviewers # Reviewers associated with each 360 review
└── ppp360reviews # Individual review responses from reviewersEntity Relationships
Schema Details
ppp360 Table
The ppp360 table represents the main 360 review record created by a user who wishes to collect feedback.
Fields
| Field | Type | Description | Constraints |
|---|---|---|---|
| id | integer | Primary key identifier | PK, Generated |
| date | timestamp | Date of the 360 review | |
| name | varchar | Name of the 360 review (typically format: "360 Review - Month YYYY") | length: 255 |
| status | varchar | Current status of the review (draft, pending, completed) | length: 255 |
| userId | integer | ID of the user who created the review | FK to users.id |
| createdAt | timestamp | Timestamp when the record was created | Audit field |
| updatedAt | timestamp | Timestamp when the record was last updated | Audit field |
| createdBy | integer | ID of the user who created the record | Audit field |
| updatedBy | integer | ID of the user who last updated the record | Audit field |
Relationships
- One-to-Many with ppp360reviewers: A 360 review has multiple reviewers
- Many-to-One with users: A 360 review is created by one user
Business Purpose
The ppp360 table serves as the main record for a 360 review assessment. It tracks the date, name, and current status of the review process. The status field transitions through various states as the review progresses from creation to completion.
ppp360reviewers Table
The ppp360reviewers table stores information about individuals who are invited to provide feedback on a medical professional as part of a 360 review.
Fields
| Field | Type | Description | Constraints |
|---|---|---|---|
| id | integer | Primary key identifier | PK, Generated |
| ppp360Id | integer | ID of the 360 review this reviewer is associated with | FK to ppp360.id |
| name | varchar | Name of the reviewer | length: 255, Not Null |
| varchar | Email address of the reviewer | length: 255, Not Null | |
| group | varchar | Group classification (Medical Doctor, Medical Non-Doctor, or Non-Medical) | length: 255, Not Null |
| createdAt | timestamp | Timestamp when the record was created | Audit field |
| updatedAt | timestamp | Timestamp when the record was last updated | Audit field |
| createdBy | integer | ID of the user who created the record | Audit field |
| updatedBy | integer | ID of the user who last updated the record | Audit field |
Relationships
- Many-to-One with ppp360: A reviewer is associated with one 360 review
- One-to-Many with ppp360reviews: A reviewer provides multiple review answers
Business Purpose
The ppp360reviewers table maintains a record of each person invited to contribute to a 360 review. The group field categorizes reviewers into Medical Doctor, Medical Non-Doctor, or Non-Medical classifications, which is important for analyzing feedback patterns and ensuring proper representation from different stakeholder groups.
ppp360reviews Table
The ppp360reviews table contains the actual feedback responses provided by reviewers, including ratings and comments.
Fields
| Field | Type | Description | Constraints |
|---|---|---|---|
| id | integer | Primary key identifier | PK, Generated |
| ppp360reviewerId | integer | ID of the reviewer who provided this feedback | FK to ppp360reviewers.id |
| question | varchar | Text of the question being answered | length: 255 |
| answer | varchar | Reviewer's response (rating value or comment text) | length: 255 |
| isComment | boolean | Indicates if this is a comment field (true) or rating (false) | Default: false |
| order | integer | Numerical order/position of the question | |
| createdAt | timestamp | Timestamp when the record was created | Audit field |
| updatedAt | timestamp | Timestamp when the record was last updated | Audit field |
| createdBy | integer | ID of the user who created the record | Audit field |
| updatedBy | integer | ID of the user who last updated the record | Audit field |
Relationships
- Many-to-One with ppp360reviewers: Many review responses are provided by one reviewer
Business Purpose
The ppp360reviews table stores individual responses to assessment questions. Each reviewer typically answers multiple standardized questions (where isComment=false) using a numerical rating scale, plus provides narrative feedback (where isComment=true). The order field enables proper sequencing of questions when retrieving the data.
Data Flow
The data flow within the PPP360 schema follows this general pattern:
- Creation Phase: A new record is created in the
ppp360table with status "draft" and associated metadata (date, name) - Reviewer Assignment: Multiple reviewer records are created in the
ppp360reviewerstable, linked to the 360 review - Feedback Collection: As reviewers provide feedback, responses are stored in the
ppp360reviewstable - Status Progression: The 360 review record's status field is updated as it moves through its lifecycle:
- draft: Initial state when first created
- pending: When reviewers have been assigned and feedback collection is in progress
- completed: When all required feedback has been collected
Technical Implementation
The schema is implemented using Drizzle ORM with PostgreSQL in the TypeScript codebase, featuring:
- PostgreSQL Tables: Defined with appropriate types, constraints, and references
- Relations Mapping: Establishes bi-directional relationships between tables
- Audit Fields: Standard audit trail fields (createdAt, updatedAt, createdBy, updatedBy) on all tables
- Cascading Deletes: Configured to maintain referential integrity (e.g., deleting a 360 review cascades to its reviewers and their responses)
- Foreign Key Constraints: Enforces proper relationships between tables
Business Logic
360 Review Lifecycle
- Creation: A 360 review begins with a professional creating a new record with an initial "draft" status.
- Reviewer Selection: The review creator designates a set of reviewers (typically 5) from various professional groups to provide balanced feedback.
- Feedback Collection: Each reviewer submits responses to a standardized set of questions covering:
- Technical Skills
- Communication Skills
- Collaboration
- Management and Leadership
- Health Advocacy
- Scholar and Teacher
- Professionalism
- Completion: Once all reviewers have submitted their feedback, the review status is updated to "completed".
- Analysis: The aggregated feedback can be analyzed by question, by reviewer group, or across multiple reviews for longitudinal assessment.
Data Constraints and Rules
- Reviewer Requirements: Each 360 review must have at least one reviewer, but typically has five reviewers.
- Group Distribution: Reviewers should include representatives from both Medical and Non-Medical groups to ensure comprehensive feedback.
- Question Standardization: A standardized set of 16 assessment questions plus one comments section ensures consistency across reviews.
- Rating Scale: Numerical ratings follow a standardized 5-point scale:
- 1: Unsatisfactory (Below expectations)
- 2: Borderline
- 3: Satisfactory (Demonstrates competence)
- 4: Proficient
- 5: Outstanding
- UE: Unable to evaluate (represented as "0" in the database)
- Status Transitions: Reviews follow a defined progression:
- draft → pending → completed
- A review cannot be completed until all assigned reviewers have provided feedback