Cross-Database Relationships Analysis

iCentral Multi-Tenant Healthcare System Architecture


Document Information

Version: 1.0
Last Updated: 2025-07-25
Source: SchemaSpy Meta File Enhancement Analysis
Scope: Sandbox, iCS2, and iCentralMarketPlace databases

Table of Contents

Executive Summary

The iCentral healthcare system employs a sophisticated multi-tenant architecture with three primary databases that work together to provide comprehensive healthcare service management, billing, and support functionality. This analysis documents the intricate cross-database relationships, business logic formulas, and data integration patterns that enable seamless operation across the entire platform.

Architecture Overview

  • Sandbox Database: Client-specific healthcare service delivery and case management
  • iCS2 Database: Central authority for master data (agencies, service types, modifiers, program types)
  • iCentralMarketPlace Database: Shared resources for support tickets and application marketplace

Key Integration Patterns

  1. Central Authority Pattern: iCS2 serves as the master data source for standardized lookup tables
  2. Shared Resource Pattern: iCentralMarketPlace provides cross-tenant services (tickets, applications)
  3. Multi-Tenant Segregation: AgencyID-based data isolation with centralized reference data
  4. Cross-Database Views: Complex views that join local and centralized data for unified business processes

Cross-Database Relationship Mapping

Sandbox → iCS2 Relationships

Core Authorization and Service Management
Sandbox View/Table iCS2 Reference Business Purpose
Action_VW iCS2.dbo.ClaimActionsNew, iCS2.dbo.ActionInBillingMethod, iCS2.dbo.ActionInLevel Workflow management with centralized action definitions
ActionN_VW iCS2.dbo.ClaimActionsNew, iCS2.dbo.ActionInLevel Simplified action tracking with market-specific filtering
AuthorizationWoutAssignments_vw iCS2.dbo.Modifier, iCS2.dbo.ServiceType Service authorization with standardized billing codes
Authorize_Simple_vw iCS2.dbo.ServiceType, iCS2.dbo.Modifier, iCS2.dbo.placeservice Streamlined authorization reporting with centralized lookups
Authorize_vw iCS2.dbo.Modifier, iCS2.dbo.ServiceType, iCS2.dbo.DisTypeIDInProgType Comprehensive authorization management
Authorize (table) iCS2.dbo.ServiceType, iCS2.dbo.Modifier Core authorization records with centralized service and modifier definitions
Claims and Billing Management
Sandbox View/Table iCS2 Reference Business Purpose
Claim (table) iCS2.dbo.StatusType, iCS2.dbo.ServiceType, iCS2.dbo.Modifier, iCS2.dbo.ClaimType, iCS2.dbo.Agency Primary claims processing with centralized status, service, modifier, claim type, and agency management
ClaimNew (table) iCS2.dbo.StatusType, iCS2.dbo.ServiceType, iCS2.dbo.Modifier New claims processing with centralized definitions
ClaimArchive (table) iCS2.dbo.StatusType Archived claims with centralized status management
claimtypeinprogtype (table) iCS2.dbo.ClaimType, iCS2.dbo.ProgramType Claim type program configurations with centralized definitions
Agency and Program Management
Sandbox View/Table iCS2 Reference Business Purpose
AgencyOutsideSrc_vw iCS2.dbo.agency Unified agency directory combining internal and external organizations
agencySites_vw iCS2.dbo.ProgramType Site-program relationships with centralized program definitions
ServiceCoordinatorByPatient_VW iCS2.dbo.Agency Service coordinator assignments with agency context
Agency (table) iCS2.dbo.Agency Local agency table referencing centralized agency definitions
AgencySite (table) iCS2.dbo.Agency Agency sites with centralized parent organization management
AgencyProgramType (table) iCS2.dbo.Agency, iCS2.dbo.ProgramType Agency program configurations with centralized definitions
User and Access Management
Sandbox View/Table iCS2 Reference Business Purpose
AccessUser (table) iCS2.dbo.Agency User accounts with centralized agency affiliation for multi-tenant access control
Patient (table) iCS2.dbo.Agency Patient records with centralized healthcare provider organization management
UserGroup (table) iCS2.dbo.ProgramType User groups with centralized program type associations
Billing and Financial Management
Sandbox View/Table iCS2 Reference Business Purpose
BillingRate_vw iCS2.dbo.billingrate Unified rate lookup across systems
BillingRate (table) iCS2.dbo.ServiceType, iCS2.dbo.Modifier Billing rates with centralized service and modifier definitions
PayrollRateRule_VW iCS2.dbo.ServiceType, iCS2.dbo.Modifier Payroll rate rules with centralized service and modifier definitions
PayrollRatesSetting_VW iCS2.dbo.ServiceType, iCS2.dbo.Modifier Payroll rate settings with centralized definitions
ManualPayrollRates (table) iCS2.dbo.ServiceType, iCS2.dbo.Modifier Manual payroll rates with centralized service and modifier definitions
RateCodeRule_VW iCS2.dbo.ratecodelookup, iCS2.dbo.ClaimTypeInProgType, iCS2.dbo.ClaimType, iCS2.dbo.ProgramType Rate rule validation with centralized configurations
RateSetting_VW iCS2.dbo.ClaimTypeInProgType, iCS2.dbo.ClaimType, iCS2.dbo.ProgramType, iCS2.dbo.Modifier, iCS2.dbo.ServiceType, iCS2.dbo.ProcCode Comprehensive rate management
Invoice (table) iCS2.dbo.ProgramType Invoices with centralized program type definitions
Status and Workflow Management
Sandbox View/Table iCS2 Reference Business Purpose
PayrollPreview (table) iCS2.dbo.StatusType Payroll previews with centralized status management
BillingInvArchive (table) iCS2.dbo.StatusType Archived billing invoices with centralized status management
ClaimStatusSummary_VW iCS2.dbo.StatusType Claim status summaries with centralized status definitions
ClaimSummary (table) iCS2.dbo.StatusType Claim summaries with centralized status definitions
InvoicePayment (table) iCS2.dbo.StatusType Invoice payments with centralized status management
Goal (table) iCS2.dbo.StatusType Patient goals with centralized status management
Objective (table) iCS2.dbo.StatusType Patient objectives with centralized status management
Document and Form Management
Sandbox View/Table iCS2 Reference Business Purpose
ScanFormHeader (table) iCS2.dbo.ServiceType, iCS2.dbo.Modifier Scanned form headers with centralized service and modifier definitions
Activity_Visit (table) iCS2.dbo.ServiceType, iCS2.dbo.Modifier Activity visits with centralized service and modifier definitions
ScanArchiveHeader (table) iCS2.dbo.ServiceType, iCS2.dbo.Modifier Archived scan headers with centralized definitions
ScanArchiveSC (table) iCS2.dbo.ServiceType, iCS2.dbo.Modifier Service coordination scan archives with centralized definitions
Program and Service Configuration
Sandbox View/Table iCS2 Reference Business Purpose
Category (table) iCS2.dbo.ProgramType Categories with centralized program type associations
PatientDiagnosServiceType (table) iCS2.dbo.ServiceType Patient diagnosis service types with centralized definitions
ProcCodeInCPT (table) iCS2.dbo.ServiceType Procedure code mappings with centralized service type definitions
ServiceTypeInCPTCode (table) iCS2.dbo.ServiceType Service type CPT code mappings with centralized definitions
IFSPProvReviewNew (table) iCS2.dbo.ServiceType IFSP provider reviews with centralized service type definitions

Sandbox → iCentralMarketPlace Relationships

Sandbox View/Table iCentralMarketPlace Reference Business Purpose
CaseListing_VW iCentralMarketPlace.dbo.Apps Case-to-billing application integration via CategoryApp

Formula Logic Documentation

Authorization Unit Tracking

UnitRemain Calculation
UnitRemain = CASE WHEN (UnitUsed IS NULL) OR (Unitused = 0)
             THEN Units
             ELSE (Units - UnitUsed)
             END
Business Context: Calculates remaining authorized service units by subtracting used units from total authorized units, supporting service delivery tracking and authorization management across healthcare service delivery processes.

Used In: AuthorizationWoutAssignments_vw, Authorize_Simple_vw

Cross-Database Context: This calculation relies on local authorization data but uses service types from iCS2.dbo.ServiceType for consistent unit tracking across the multi-tenant system.

Payment Application Analytics

Service Type-Specific Payment Tracking
-- Amount Applied for EVALS Claims (ClaimTypeId = 1)
ISNULL(SUM(CASE WHEN cl.ClaimTypeId = 1 THEN ABS(ct.Amount) ELSE 0 END), 0) AS [AmountAppliedEval]

-- Amount Applied for EI Service Claims (ClaimTypeId = 2)
ISNULL(SUM(CASE WHEN cl.ClaimTypeId = 2 THEN ABS(ct.Amount) ELSE 0 END), 0) AS [AmountAppliedVisit]

-- Amount Applied for SC Claims (ClaimTypeId = 3)
ISNULL(SUM(CASE WHEN cl.ClaimTypeId = 3 THEN ABS(ct.Amount) ELSE 0 END), 0) AS [AmountAppliedSC]
Business Context: Categorizes payment applications by service type (Evaluations, Early Intervention Services, Service Coordination) for financial reporting and accounts receivable analysis.

Used In: AmountAppliedClaimsByCheckLog_vw

Cross-Database Context: Uses ClaimTypeId references to iCS2.dbo.ClaimType for consistent claim type classification across all payment processing.

Invoice Status Management

Daycare Invoice Status Logic
Status = (CASE WHEN Summary.Outstanding = 0 THEN 'Paid'
               WHEN Summary.DueDate < GETDATE() THEN 'Overdue'
               WHEN Summary.OutStanding = Summary.Total THEN 'Open'
               ELSE 'Partial Paid'
          END)
Business Context: Determines invoice payment status based on outstanding balance and due date for daycare financial management.

Used In: DaycareInvoiceSummary_vw

Patient Age and Eligibility Tracking

Age Calculation for Early Intervention
YearsOld = (SELECT FLOOR(DATEDIFF(DAY, DateOfBirth, GETDATE()) / 365.25))
ThirdBirthDay = DATEADD(year, 3, DateOfBirth)
Business Context: Calculates patient age and third birthday for early intervention eligibility tracking (services typically end at age 3).

Used In: PatientAdmin_vw

Case Management and Status Tracking

Case Status with Open/Closed Indicator
Status = (Case when ps.OpenClosed Is Not Null
          then ps.StatusName + ' (' + ps.OpenClosed + ')'
          Else StatusName
          End)
Business Context: Enhances case status display with open/closed indicators for case management workflows.

Used In: CaseListing_VW

Program Type with Case Sequence
LTRIM(row_number() over(PARTITION BY PatientID ORDER BY AdmitId ASC)) + ' - ' + bt.ProgramName AS ProgramType
Business Context: Numbers multiple cases for the same patient to track service progression and case history.

Used In: CaseListing_VW

Central Authority Tables (iCS2 Database)

Agency - Master Organization Directory

Table: iCS2.dbo.Agency
Role: Central authority for all healthcare provider organizations
Cross-Database Usage: Referenced by Sandbox tables and views for unified agency directories and multi-tenant data segregation

Key Relationships:

  • Sandbox.dbo.AgencyOutsideSrc_vw - Unified organization directory
  • Sandbox.dbo.ServiceCoordinatorByPatient_VW - Agency-based coordinator assignments
  • Sandbox.dbo.Claim.AgencyID - Claims linked to centralized agency management
  • Sandbox.dbo.Patient.AgencyID - Patient records with centralized provider organization
  • Sandbox.dbo.AccessUser.AgencyID - User accounts with centralized agency affiliation
  • Sandbox.dbo.AgencySite.AgencyID - Agency sites with centralized parent organization
  • Sandbox.dbo.AgencyProgramType.AgencyID - Agency program configurations
  • All core tables use AgencyID for multi-tenant data isolation

ServiceType - Standardized Service Definitions

Table: iCS2.dbo.ServiceType
Role: Central authority for healthcare service type definitions
Cross-Database Usage: Referenced by authorization, billing, and claims tables for consistent service classification

Key Relationships:

  • Sandbox.dbo.Authorize_vw - Comprehensive authorization management
  • Sandbox.dbo.AuthorizationWoutAssignments_vw - Service authorization details
  • Sandbox.dbo.Authorize_Simple_vw - Streamlined authorization reporting
  • Sandbox.dbo.Claim.ServiceTypeID - Claims with centralized service type definitions
  • Sandbox.dbo.Authorize.ServiceTypeID - Authorization records with standardized service codes
  • Sandbox.dbo.BillingRate.ServiceTypeid - Billing rates with centralized service definitions
  • Sandbox.dbo.ScanFormHeader.ServiceTypeid - Scanned form headers with centralized service classification
  • Sandbox.dbo.Activity_Visit.ServiceTypeID - Activity visits with standardized service types
  • Sandbox.dbo.PatientDiagnosServiceType.ServiceTypeId - Patient diagnosis service mappings
  • Sandbox.dbo.ProcCodeInCPT.fkServiceTypeID - Procedure code mappings
  • Sandbox.dbo.ServiceTypeInCPTCode.fkServiceTypeID - Service type CPT code mappings

Modifier - Billing Modifier Standards

Table: iCS2.dbo.Modifier
Role: Central authority for medical billing modifier codes
Cross-Database Usage: Referenced by authorization, billing, and claims tables for standardized billing practices

Key Relationships:

  • Sandbox.dbo.Authorize_vw - Authorization with billing modifiers
  • Sandbox.dbo.AuthorizationWoutAssignments_vw - Service authorization with modifiers
  • Sandbox.dbo.Authorize_Simple_vw - Authorization reporting with modifier context
  • Sandbox.dbo.Claim.ModifierID - Claims with centralized modifier definitions
  • Sandbox.dbo.Authorize.ModifierID - Authorization records with standardized modifier codes
  • Sandbox.dbo.BillingRate.ModifierID - Billing rates with centralized modifier definitions
  • Sandbox.dbo.ScanFormHeader.Modifierid - Scanned form headers with centralized modifier classification
  • Sandbox.dbo.Activity_Visit.ModifierID - Activity visits with standardized modifiers

StatusType - Centralized Status Management

Table: iCS2.dbo.StatusType
Role: Central authority for all status definitions across the healthcare system
Cross-Database Usage: Referenced by claims, billing, payroll, and workflow tables for consistent status management

Key Relationships:

  • Sandbox.dbo.Claim.StatusID - Claims with centralized status management
  • Sandbox.dbo.ClaimNew.StatusID - New claims with standardized status definitions
  • Sandbox.dbo.ClaimArchive.StatusID - Archived claims with centralized status tracking
  • Sandbox.dbo.PayrollPreview.StatusId - Payroll previews with standardized status
  • Sandbox.dbo.InvoicePayment.StatusID - Invoice payments with centralized status management
  • Sandbox.dbo.Goal.StatusID - Patient goals with standardized status definitions
  • Sandbox.dbo.Objective.StatusID - Patient objectives with centralized status management

ProgramType - Healthcare Program Classifications

Table: iCS2.dbo.ProgramType
Role: Central authority for healthcare program definitions and billing rules
Cross-Database Usage: Referenced for consistent program classifications across all client databases

Key Relationships:

  • Sandbox.dbo.agencySites_vw - Site-program relationships
  • Sandbox.dbo.CaseListing_VW - Case-program associations
  • Sandbox.dbo.PatientAdmin_vw - Patient program assignments
  • Sandbox.dbo.AgencyProgramType.ProgramID - Agency program configurations
  • Sandbox.dbo.Category.ProgramID - Categories with centralized program associations
  • Sandbox.dbo.UserGroup.programID - User groups with program type associations
  • Sandbox.dbo.Invoice.ProgramID - Invoices with centralized program definitions
  • Sandbox.dbo.claimtypeinprogtype.fkProgramID - Claim type program configurations

ClaimType - Claim Classification Standards

Table: iCS2.dbo.ClaimType
Role: Central authority for claim type definitions and billing classifications
Cross-Database Usage: Referenced by claims and billing configuration tables for consistent claim classification

Key Relationships:

  • Sandbox.dbo.Claim.ClaimTypeID - Claims with centralized claim type classification
  • Sandbox.dbo.claimtypeinprogtype.fkClaimTypeID - Claim type program configurations

Shared Resource Tables (iCentralMarketPlace Database)

CentralizedTicket - Unified Support System

Table: iCentralMarketPlace.dbo.CentralizedTicket
Role: Shared resource for customer support across all client databases
Multi-Tenant Pattern: Uses CreatedByCompany for agency-specific ticket filtering

Business Integration:

  • Accessible across all client databases for unified support
  • Agency-specific data segregation through company associations
  • Cross-system SLA tracking and business analytics
  • Supports both public customer-facing and internal development tickets

Apps - Application Marketplace

Table: iCentralMarketPlace.dbo.Apps
Role: Central authority for application definitions and marketplace management
Cross-Database Usage: Referenced by client databases for billing application integration

Key Relationships:

  • Sandbox.dbo.CaseListing_VW - Links cases to billing applications via CategoryApp
  • Supports comprehensive app lifecycle management across multi-tenant platform
  • Enables cross-system application deployment and licensing

Multi-Tenant Architecture Patterns

Agency-Based Data Segregation

Pattern: All core tables include AgencyID for multi-tenant data isolation

Implementation:

  • Cross-database references maintain agency context through joins with iCS2.dbo.Agency
  • Views filter data based on agency assignments to ensure proper data security
  • Shared resources include agency identification for proper routing

Enhanced Agency References:

  • Sandbox.dbo.Claim.AgencyID → iCS2.dbo.Agency - Claims linked to centralized agency management
  • Sandbox.dbo.Patient.AgencyID → iCS2.dbo.Agency - Patient records with centralized provider organization
  • Sandbox.dbo.AccessUser.AgencyID → iCS2.dbo.Agency - User accounts with centralized agency affiliation
  • Sandbox.dbo.AgencySite.AgencyID → iCS2.dbo.Agency - Agency sites with centralized parent organization

Centralized Lookup Management

Pattern: iCS2 serves as the central authority for standardized lookup data

Benefits:

  • Consistent service definitions across all client databases
  • Standardized billing codes and modifiers
  • Unified agency and program type management
  • Simplified maintenance and updates

Enhanced Centralized References:

  • Status Management: iCS2.dbo.StatusType referenced by 13+ tables for consistent workflow status
  • Service Classification: iCS2.dbo.ServiceType referenced by 12+ tables for standardized service codes
  • Billing Standards: iCS2.dbo.Modifier referenced by 10+ tables for consistent billing modifiers
  • Program Definitions: iCS2.dbo.ProgramType referenced by 8+ tables for unified program management
  • Claim Classification: iCS2.dbo.ClaimType referenced by claims and configuration tables

Cross-Database Consistency Patterns

Status Standardization:

  • All workflow status managed through iCS2.dbo.StatusType
  • Claims, billing, payroll, and case management use consistent status definitions
  • Centralized status ensures uniform workflow management across all systems

Service and Billing Standardization:

  • Service types centrally defined in iCS2.dbo.ServiceType
  • Billing modifiers standardized through iCS2.dbo.Modifier
  • Rate tables, authorization records, and claims all reference centralized definitions
  • Ensures consistent billing practices across all client databases

Program and Configuration Management:

  • Program types centrally managed in iCS2.dbo.ProgramType
  • Claim types standardized through iCS2.dbo.ClaimType
  • Configuration tables reference centralized definitions for consistency

Function-Based Relationships

Key Functions:

  • getProgramTypes_fn() - Used in PatientAdmin_vw for agency-specific program filtering
  • getAgencyID_fn() - Referenced in Action_VW for agency-based data filtering
  • getUserId_fn() - Used in CaseAssigned_vw for encrypted user identification

Business Process Integration

Authorization Workflow

  1. Service Authorization Creation - Local authorization tables with references to centralized service types and modifiers
  2. Unit Tracking - Calculated remaining units using standardized formulas
  3. Billing Integration - Cross-database rate lookup and billing rule application
  4. Reporting - Unified views combining local and centralized data

Case Management Workflow

  1. Case Creation - Local case data with centralized program type definitions
  2. Status Tracking - Dynamic status calculation with business logic
  3. Application Integration - Links to billing applications via marketplace
  4. Coordinator Assignment - Agency-based coordinator relationships

Financial Management Workflow

  1. Rate Management - Centralized rate definitions with local overrides
  2. Payment Processing - Service type-specific payment categorization
  3. Invoice Management - Dynamic status calculation based on payment state
  4. Analytics - Cross-database financial reporting and business intelligence