Database Schema Documentation

Database Engine: SQL Server 2019+

ORM: Entity Framework Core 9.0

Table of Contents

  1. Schema Overview
  2. Core Entities
  3. Lookup/Reference Tables
  4. Relationship Diagrams
  5. Custom Fields System
  6. Security Tables
  7. Reporting System
  8. SQL Views
  9. Indexes and Performance
  10. Migration Strategy

Schema Overview

Entity Counts (Approximate)

Category Table Count Key Tables
Core Entities 10 Patient, Case, Outbreak, Location, Event
Lookup Tables 25 Disease, Country, Language, Occupation, etc.
Custom Fields (EAV) 10 PatientCustomField*, CaseCustomField*
Laboratory 7 LabResult, SpecimenType, TestType, TestResult
Task Management 5 CaseTask, TaskTemplate, TaskType, TaskCallAttempt
Outbreak System 6 OutbreakTeamMember, OutbreakCase, OutbreakCaseDefinition
Security 8 AspNetUsers, Permission, DiseaseAccess tables
Reporting 6 ReportDefinition, ReportField, ReportFilter
SQL Views 6 Flattened reporting views
Total ~85 tables + 6 views