Lesson 1Supporting dimension tables: provider, location, facility, code lookupsThis part explains supporting dimension tables like provider, location, facility, and code lookups. It covers hierarchies, slowly changing features, and how good dimensions improve filtering, grouping, and detailed analyses in Botswana clinics.
Provider and specialty dimensionsLocation and facility hierarchiesClinical code and value set lookupsManaging slowly changing dimensionsLesson 2Encounter/Visit entity: admit/arrival, discharge, visit type and timestampsThis part describes the encounter or visit entity, including admission, arrival, discharge, visit type, and timestamps. It covers linking encounters to patients, locations, and payers, supporting stay length and flow metrics in Botswana hospitals.
Encounter types and classificationsAdmission, transfer, and discharge timesLinking encounters to patientsVisit grouping and episode logicLesson 3Canonical patient entity: identifiers, demography, merges and survivorshipThis part defines a standard patient entity for analysis, covering identifiers, demographics, merges, and survivorship rules. It explains mastering across sources, handling duplicates, and keeping historical changes safe in Botswana systems.
Core patient identifiers and keysDemographic attributes for analyticsPatient matching and merge logicSurvivorship and source precedenceLesson 4Procedures and orders entities: procedure codes, order IDs, performing providerThis part covers modelling procedures and orders, including codes, order IDs, and performing providers. It explains linking orders to results, scheduling, and status, supporting use, quality, and flow analytics in Botswana.
Order header and line item structureProcedure and order coding standardsLinking orders, procedures, and resultsOrder status, timing, and priorityLesson 5Keys and relationships: patient_id, encounter_id, result linking and referential integrityThis part details how patient, encounter, and result keys keep referential integrity across clinical datasets. It covers natural vs surrogate keys, cascading rules, and handling late or corrected records in Botswana analytics.
Patient and encounter key designResult and order linkage patternsSurrogate keys vs natural identifiersCascades, deletes, and orphan recordsLesson 6Diagnoses and problem list entities: fields, code system, severity, onset and resolutionThis part focuses on diagnoses and problem list entities, including fields for codes, status, severity, onset, and resolution. It addresses coding systems, chronic vs acute issues, and handling changes over time in Botswana EHRs.
Core diagnosis and problem fieldsICD, SNOMED, and other code systemsOnset, resolution, and episode timingActive, historical, and resolved problemsLesson 7Lab result entity design: test code, specimen, collection time, result value, units, reference range, statusThis part details lab result entity design, including test codes, specimens, collection times, values, units, ranges, and statuses. It addresses abnormal flags, panels, and handling corrected results for analysis in Botswana labs.
Test, panel, and component structureSpecimen type and collection detailsResult value, units, and reference rangeResult status, flags, and correctionsLesson 8Schematic examples: star schema for analytics and entity relationship mappingThis part introduces star schemas for clinical analysis and contrasts them with entity-relationship diagrams. Learners see how facts, dimensions, and relationships map to EHR concepts for effective queries in Botswana health data.
Clinical fact and dimension tablesStar vs snowflake in healthcareMapping EHR entities to factsBridging many-to-many clinical linksLesson 9Principles of analytics data modeling vs transactional modelingThis part compares analytic and transactional data models in healthcare. It explains normalisation, denormalisation, query patterns, and workload traits, guiding choices for performance, flexibility, and quality in Botswana systems.
OLTP vs OLAP workloads in EHRsNormalization and denormalization tradeoffsSlowly changing clinical attributesModeling for longitudinal patient views
