Columns
| Column | Type | Size | Nulls | Auto | Default | Children | Parents | Comments | |||
|---|---|---|---|---|---|---|---|---|---|---|---|
| AccessUserPatientID | int identity | 4 | √ | null |
|
|
Primary key - Unique identifier for each user-patient assignment |
||||
| PatientID | int | 4 | √ | null |
|
|
Patient reference - Links to Patient table for the assigned patient/client |
||||
| AccessUserID | int | 4 | √ | null |
|
|
User reference - Links to AccessUser table for the assigned healthcare provider |
||||
| UserRankTitle | char | 10 | √ | null |
|
|
User rank - Hierarchical rank or level of the user in relation to patient care |
||||
| SC_Type | char | 10 | √ | null |
|
|
Service coordinator type - Type of service coordination role (primary, secondary, etc.) |
||||
| RelationshipStatus | char | 10 | √ | null |
|
|
Relationship status - Current status of the provider-patient relationship (Active, Inactive, Pending, etc.) |
||||
| DateAssigned | smalldatetime | 16 | √ | (getdate()) |
|
|
Assignment date - Date when the provider was assigned to the patient |
||||
| DateActive | smalldatetime | 16 | √ | null |
|
|
Active date - Date when the assignment became active |
||||
| DateInactive | smalldatetime | 16 | √ | null |
|
|
Inactive date - Date when the assignment was deactivated |
||||
| caseID | int | 4 | √ | null |
|
|
Case reference - Links to PatientAdministration table for the specific case |
||||
| DateCreated | smalldatetime | 16 | √ | null |
|
|
Creation date - Timestamp when the assignment record was created |
||||
| CreatedBy | int | 4 | √ | null |
|
|
Created by user - UserID of the person who created this assignment |
||||
| DateUpdated | smalldatetime | 16 | √ | null |
|
|
Last update date - Timestamp of the most recent assignment update |
||||
| UpdatedBy | int | 4 | √ | null |
|
|
Updated by user - UserID of the person who last modified this assignment |
||||
| fkdistypeid | int | 4 | √ | null |
|
|
Discipline type - Links to discipline type for the provider’s role |
||||
| Comments | varchar | 500 | √ | null |
|
|
Assignment comments - Notes about the provider-patient assignment |
||||
| RoleId | uniqueidentifier | 16 | √ | null |
|
|
Role reference - Links to Roles table for specific role permissions |
||||
| DeletedDate | datetime | 16,3 | √ | null |
|
|
Deletion date - Soft delete timestamp for audit trail purposes |
Indexes
| Constraint Name | Type | Sort | Column(s) |
|---|---|---|---|
| PK_AccessUserPatient | Primary key | Asc | AccessUserPatientID |
| IX_ActiveDate | Performance | Asc | DateActive |
| IX_DisType | Performance | Asc | fkdistypeid |
| IX_InactiveDate | Performance | Asc | DateInactive |
| IX_RelStatus | Performance | Asc | RelationshipStatus |
| Patient | Performance | Asc | PatientID |
| User Index | Performance | Asc | AccessUserID |
Relationships
Close relationships within degrees of separation
