Skip to content

DB ER Analysis

This ER Diagram outlines the entities (tables) and their relationships within the 'infinity-lis' database, incorporating the refinements and essential missing tables discussed in our previous analysis.

Legend:

  • Entity: TableName
  • PK: Primary Key
  • UK: Unique Key/Constraint
  • FK: Foreign Key
  • [New]: Indicates a table suggested in the previous analysis.

Entities & Key Attributes (Focus on PK/UK)

  1. Users

    • PK: UserID
    • UK: Username, Email

  2. Roles

    • PK: RoleID
    • UK: RoleName

  3. UserRoles

    • PK: UserRoleID
    • UK: (UserID, RoleID)
    • FK: UserID (to Users), RoleID (to Roles)
  4. Permissions
    • PK: PermissionID
    • UK: PermissionName
  5. RolePermissions
    • PK: RolePermissionID
    • UK: (RoleID, PermissionID)
    • FK: RoleID (to Roles), PermissionID (to Permissions)
  6. Providers
    • PK: ProviderID
    • UK: NPI
  7. ExternalSystems
    • PK: ExternalSystemID
    • UK: SystemCode
  8. Patients
    • PK: LISPatientID
    • UK: (ExternalSystemID, ExternalSystemMRN)
    • FK: ExternalSystemID (to ExternalSystems), PrimaryLanguageID (to LanguageMasters), MaritalStatusID (to MaritalStatusMasters), RaceID (to RaceMasters), EthnicityID (to EthnicityMasters)
  9. Visits
    • PK: LISVisitID
    • UK: (ExternalSystemID, ExternalSystemVisitID)
    • FK: LISPatientID (to Patients), ExternalSystemID (to ExternalSystems), AttendingProviderID (to Providers), ReferringProviderID (to Providers), PatientClassID (to PatientClassMasters), LocationID (to LabLocations)
  10. Insurances
    • PK: LISInsuranceID
    • FK: LISPatientID (to Patients), InsurancePlanID (to InsurancePlans), InsuranceCompanyID (to InsuranceCompanies)
  11. Allergies
    • PK: LISAllergyID
    • FK: LISPatientID (to Patients), AllergyTypeID (to AllergyTypeMasters), AllergySeverityID (to AllergySeverityMasters), AllergyReactionID (to AllergyReactionMasters), AllergyCodeID (to ClinicalTerminologyMasters or specific allergy code masters)
  12. DXCodes
    • PK: LISDxCodeID
    • FK: LISPatientID (to Patients), LISVisitID (to Visits), DxMasterID (to DiagnosisCodeMasters)
  13. LabOrders
    • PK: LISOrderID
    • UK: (ExternalSystemID, ExternalSystemOrderID)
    • FK: ExternalSystemID (to ExternalSystems), LISPatientID (to Patients), LISVisitID (to Visits), OrderingProviderID (to Providers), OrderPriorityID (to OrderPriorityMasters), OrderStatusID (to OrderStatusMasters), ResultStatusID (to ResultStatusMasters)
  14. OrderedTests [New]
    • PK: OrderTestID
    • FK: LISOrderID (to LabOrders), TestCatalogID (to TestCatalog), TestPanelID (to TestPanels, NULLABLE)
  15. OrderDiagnosticCodes
    • PK: OrderDiagnosticCodeID
    • FK: LISOrderID (to LabOrders), DxMasterID (to DiagnosisCodeMasters)
  16. TestCategories [New]
    • PK: TestCategoryID
    • UK: CategoryName
  17. UnitsOfMeasure [New]
    • PK: UnitID
    • UK: UnitCode
  18. TestCatalog
    • PK: TestCatalogID
    • UK: TestCode
    • FK: TestCategoryID (to TestCategories), UnitID (to UnitsOfMeasure), SampleTypeID (to SampleTypeMasters), ContainerTypeID (to ContainerTypeMasters)
  19. TestPanels
    • PK: TestPanelID
    • UK: PanelName
  20. TestPanelComponents
    • PK: TestPanelComponentID
    • FK: TestPanelID (to TestPanels), TestCatalogID (to TestCatalog)
  21. TestRanges
    • PK: TestRangeId
    • FK: TestCatalogID (to TestCatalog), GenderID (to GenderTypes)
  22. LISRules
    • PK: RuleID
    • FK: TestCatalogID (to TestCatalog), RuleTypeID (to RuleTypeMasters)
  23. Specimens
    • PK: SpecimenID
    • UK: BarcodeID
    • FK: LISOrderID (to LabOrders), CollectedByUserID (to Users), ReceivedByUserID (to Users), RejectionReasonID (to RejectionReasons), CurrentLocationID (to LabLocations), SpecimenTypeID (to SampleTypeMasters), ContainerTypeID (to ContainerTypeMasters), PriorityID (to OrderPriorityMasters)
  24. SpecimenStatusLog [New]
    • PK: StatusLogID
    • FK: SpecimenID (to Specimens), StatusID (to SpecimenStatusMasters), ChangedByUserID (to Users)
  25. AliquotTypes [New]
    • PK: AliquotTypeID
    • UK: TypeCode
  26. Aliquots
    • PK: AliquotID
    • UK: AliquotBarcode
    • FK: ParentSpecimenID (to Specimens), StorageLocationID (to StorageLocations), AliquotTypeID (to AliquotTypes), CreatedByUserID (to Users), LastModifiedByUserID (to Users)
  27. RejectionReasons
    • PK: ReasonID
    • UK: ReasonCode
  28. TestResults
    • PK: ResultID
    • FK: LISOrderID (to LabOrders), LISPatientID (to Patients), LISVisitID (to Visits), SpecimenID (to Specimens), TestCatalogID (to TestCatalog), UnitID (to UnitsOfMeasure), AbnormalFlagID (to AbnormalFlagMasters), ResultStatusID (to ResultStatusMasters), VerificationUserID (to Users), InstrumentID (to LabInstruments), CriticalValueNotificationLogID (to CriticalValueNotificationLog, NULLABLE)
  29. TestResultVersions
    • PK: ResultVersionID
    • FK: ResultID (to TestResults), ChangedByUserID (to Users)
  30. CriticalValueNotificationLog
    • PK: LogID
    • FK: ResultID (to TestResults), NotifiedProviderID (to Providers), NotificationMethodID (to NotificationMethodMasters), NotificationStatusID (to NotificationStatusMasters), LoggedByUserID (to Users)
  31. ControlLots [New]
    • PK: ControlLotID
    • UK: LotNumber
  32. QualityControls
    • PK: QCID
    • FK: TestCatalogID (to TestCatalog), InstrumentID (to LabInstruments), ControlLotID (to ControlLots), ControlLevelID (to ControlLevelMasters), WestgardRuleBreachID (to WestgardRuleMasters), PerformedByUserID (to Users)
  33. ProficiencyTests
    • PK: PTID
    • FK: TestCatalogID (to TestCatalog), PTProgramID (to PTProgramMasters), PerformedByUserID (to Users)
  34. LabInstruments
    • PK: InstrumentID
    • UK: SerialNumber
    • FK: LocationID (to LabLocations), DeviceTypeID (to DeviceTypeMasters), InterfaceProtocolID (to InterfaceProtocolMasters), MiddlewareID (to MiddlewareSystems, NULLABLE)
  35. InstrumentTestMappings
    • PK: MappingID
    • UK: (InstrumentID, TestCatalogID, InstrumentTestCode)
    • FK: InstrumentID (to LabInstruments), TestCatalogID (to TestCatalog)
  36. InstrumentMaintenanceLog [New]
    • PK: MaintenanceLogID
    • FK: InstrumentID (to LabInstruments), MaintenanceTypeID (to MaintenanceTypeMasters), PerformedByUserID (to Users)
  37. MiddlewareSystems [New]
    • PK: MiddlewareID
    • UK: SystemName
  38. ExternalSystemVocabularyMappings (Renamed from ExternalSystemOtherMappings)
    • PK: MappingID
    • FK: ExternalSystemID (to ExternalSystems), MappingTypeID (to MappingTypeMasters), CreatedByUserID (to Users), LastModifiedByUserID (to Users)
  39. MessageLogs [New]
    • PK: MessageLogID
    • FK: ExternalSystemID (to ExternalSystems), MessageTypeID (to MessageTypeMasters), DirectionID (to MessageDirectionMasters)
  40. AuditLogs
    • PK: LogID
    • FK: UserID (to Users, NULLABLE)
  41. ArchivedReports
    • PK: ArchiveID
    • FK: LISOrderID (to LabOrders), LISPatientID (to Patients), LISVisitID (to Visits), ReportTypeID (to ReportTypeMasters), ReportFormatID (to ReportFormatMasters)
  42. StorageLocations
    • PK: LocationID
    • UK: LocationName
    • FK: LocationTypeID (to LocationTypeMasters), ParentLocationID (to StorageLocations, NULLABLE)
  43. SpecimenStorageLog
    • PK: StorageLogID
    • FK: SpecimenID (to Specimens), StorageLocationID (to StorageLocations), ActionTypeID (to StorageActionTypeMasters), ActionByUserID (to Users), CurrentStabilityStatusID (to SpecimenStabilityStatusMasters)
  44. TableMetaData
    • PK: TableMetaDataId
    • UK: (TableName, ColumnName)
    • FK: CreatedByUserID (to Users)

Relationships (Cardinality & Foreign Keys)

Patient & Visit Management:

  • Patients (1) -- (Many) Visits: Visits.LISPatientID FK to Patients.LISPatientID.
  • Patients (1) -- (Many) Insurances: Insurances.LISPatientID FK to Patients.LISPatientID.
  • Patients (1) -- (Many) Allergies: Allergies.LISPatientID FK to Patients.LISPatientID.
  • Patients (1) -- (Many) DXCodes: DXCodes.LISPatientID FK to Patients.LISPatientID.
  • Visits (1) -- (Many) DXCodes: DXCodes.LISVisitID FK to Visits.LISVisitID. (Note: DXCodes is related to both Patients and Visits).
  • Visits (1) -- (Many) LabOrders: LabOrders.LISVisitID FK to Visits.LISVisitID.
  • Providers (1) -- (Many) Visits: Visits.AttendingProviderID, Visits.ReferringProviderID FKs to Providers.ProviderID.

Order & Test Management:

  • Patients (1) -- (Many) LabOrders: LabOrders.LISPatientID FK to Patients.LISPatientID.
  • LabOrders (1) -- (Many) OrderedTests [New]: OrderedTests.LISOrderID FK to LabOrders.LISOrderID. (This is the crucial change for multi-test orders).
  • TestCatalog (1) -- (Many) OrderedTests [New]: OrderedTests.TestCatalogID FK to TestCatalog.TestCatalogID.
  • TestPanels (1) -- (Many) OrderedTests [New]: OrderedTests.TestPanelID FK to TestPanels.TestPanelID (Nullable).
  • LabOrders (1) -- (Many) OrderDiagnosticCodes: OrderDiagnosticCodes.LISOrderID FK to LabOrders.LISOrderID.
  • DiagnosisCodeMasters [New Master] (1) -- (Many) DXCodes: DXCodes.DxMasterID FK to DiagnosisCodeMasters.DxMasterID.
  • DiagnosisCodeMasters [New Master] (1) -- (Many) OrderDiagnosticCodes: OrderDiagnosticCodes.DxMasterID FK to DiagnosisCodeMasters.DxMasterID.
  • TestCategories [New Master] (1) -- (Many) TestCatalog: TestCatalog.TestCategoryID FK to TestCategories.TestCategoryID.
  • UnitsOfMeasure [New Master] (1) -- (Many) TestCatalog: TestCatalog.UnitID FK to UnitsOfMeasure.UnitID.
  • TestPanels (1) -- (Many) TestPanelComponents: TestPanelComponents.TestPanelID FK to TestPanels.TestPanelID.
  • TestCatalog (1) -- (Many) TestPanelComponents: TestPanelComponents.TestCatalogID FK to TestCatalog.TestCatalogID.
  • TestCatalog (1) -- (Many) TestRanges: TestRanges.TestCatalogID FK to TestCatalog.TestCatalogID.
  • TestCatalog (1) -- (Many) LISRules: LISRules.TestCatalogID FK to TestCatalog.TestCatalogID (Nullable).

Specimen & Aliquot Management:

  • LabOrders (1) -- (Many) Specimens: Specimens.LISOrderID FK to LabOrders.LISOrderID.
  • Specimens (1) -- (Many) SpecimenStatusLog [New]: SpecimenStatusLog.SpecimenID FK to Specimens.SpecimenID.
  • Specimens (1) -- (Many) Aliquots: Aliquots.ParentSpecimenID FK to Specimens.SpecimenID.
  • RejectionReasons (1) -- (Many) Specimens: Specimens.RejectionReasonID FK to RejectionReasons.ReasonID (Nullable).
  • StorageLocations (1) -- (Many) Aliquots: Aliquots.StorageLocationID FK to StorageLocations.LocationID.
  • StorageLocations (1) -- (Many) SpecimenStorageLog: SpecimenStorageLog.StorageLocationID FK to StorageLocations.LocationID.
  • Specimens (1) -- (Many) SpecimenStorageLog: SpecimenStorageLog.SpecimenID FK to Specimens.SpecimenID.

Result Management:

  • LabOrders (1) -- (Many) TestResults: TestResults.LISOrderID FK to LabOrders.LISOrderID.
  • Specimens (1) -- (Many) TestResults: TestResults.SpecimenID FK to Specimens.SpecimenID.
  • TestCatalog (1) -- (Many) TestResults: TestResults.TestCatalogID FK to TestCatalog.TestCatalogID.
  • TestResults (1) -- (Many) TestResultVersions: TestResultVersions.ResultID FK to TestResults.ResultID.
  • TestResults (1) -- (Many) CriticalValueNotificationLog: CriticalValueNotificationLog.ResultID FK to TestResults.ResultID.

Quality Control & Proficiency Testing:

  • TestCatalog (1) -- (Many) QualityControls: QualityControls.TestCatalogID FK to TestCatalog.TestCatalogID.
  • LabInstruments (1) -- (Many) QualityControls: QualityControls.InstrumentID FK to LabInstruments.InstrumentID.
  • ControlLots [New Master] (1) -- (Many) QualityControls: QualityControls.ControlLotID FK to ControlLots.ControlLotID.
  • TestCatalog (1) -- (Many) ProficiencyTests: ProficiencyTests.TestCatalogID FK to TestCatalog.TestCatalogID.

Lab Instrument Management:

  • LabInstruments (1) -- (Many) InstrumentTestMappings: InstrumentTestMappings.InstrumentID FK to LabInstruments.InstrumentID.
  • TestCatalog (1) -- (Many) InstrumentTestMappings: InstrumentTestMappings.TestCatalogID FK to TestCatalog.TestCatalogID.
  • LabInstruments (1) -- (Many) InstrumentMaintenanceLog [New]: InstrumentMaintenanceLog.InstrumentID FK to LabInstruments.InstrumentID.
  • MiddlewareSystems [New Master] (1) -- (Many) LabInstruments: LabInstruments.MiddlewareID FK to MiddlewareSystems.MiddlewareID (Nullable).

External System Integration:

  • ExternalSystems (1) -- (Many) Patients: Patients.ExternalSystemID FK to ExternalSystems.ExternalSystemID.
  • ExternalSystems (1) -- (Many) Visits: Visits.ExternalSystemID FK to ExternalSystems.ExternalSystemID.
  • ExternalSystems (1) -- (Many) LabOrders: LabOrders.ExternalSystemID FK to ExternalSystems.ExternalSystemID.
  • ExternalSystems (1) -- (Many) ExternalSystemTestMappings: ExternalSystemTestMappings.ExternalSystemId FK to ExternalSystems.ExternalSystemID.
  • TestCatalog (1) -- (Many) ExternalSystemTestMappings: ExternalSystemTestMappings.TestCatalogID FK to TestCatalog.TestCatalogID.
  • ExternalSystems (1) -- (Many) ExternalSystemVocabularyMappings: ExternalSystemVocabularyMappings.ExternalSystemID FK to ExternalSystems.ExternalSystemID.
  • ExternalSystems (1) -- (Many) MessageLogs [New]: MessageLogs.ExternalSystemID FK to ExternalSystems.ExternalSystemID.

Security & Audit:

  • Users (1) -- (Many) UserRoles: UserRoles.UserID FK to Users.UserID.
  • Roles (1) -- (Many) UserRoles: UserRoles.RoleID FK to Roles.RoleID.
  • Roles (1) -- (Many) RolePermissions: RolePermissions.RoleID FK to Roles.RoleID.
  • Permissions (1) -- (Many) RolePermissions: RolePermissions.PermissionID FK to Permissions.PermissionID.
  • Users (1) -- (Many) AuditLogs: AuditLogs.UserID FK to Users.UserID (Nullable).
  • Users (1) -- (Many) TableMetaData: TableMetaData.CreatedByUserID FK to Users.UserID.
  • Users (1) -- (Many) various CreatedByUserID and LastModifiedByUserID fields across many tables: All such fields (e.g., in Patients, Visits, TestResults, LabInstruments) FK to Users.UserID.

Archiving:

  • LabOrders (1) -- (Many) ArchivedReports: ArchivedReports.LISOrderID FK to LabOrders.LISOrderID.
  • Patients (1) -- (Many) ArchivedReports: ArchivedReports.LISPatientID FK to Patients.LISPatientID.
  • Visits (1) -- (Many) ArchivedReports: ArchivedReports.LISVisitID FK to Visits.LISVisitID.

General Lookup Tables (Implied from VARCHAR fields to be converted):

  • LanguageMasters [New Master] (1) -- (Many) Patients: Patients.PrimaryLanguageID FK.
  • MaritalStatusMasters [New Master] (1) -- (Many) Patients: Patients.MaritalStatusID FK.
  • RaceMasters [New Master] (1) -- (Many) Patients: Patients.RaceID FK.
  • EthnicityMasters [New Master] (1) -- (Many) Patients: Patients.EthnicityID FK.
  • PatientClassMasters [New Master] (1) -- (Many) Visits: Visits.PatientClassID FK.
  • InsuranceCompanies [New Master] (1) -- (Many) Insurances: Insurances.InsuranceCompanyID FK.
  • InsurancePlans [New Master] (1) -- (Many) Insurances: Insurances.InsurancePlanID FK.
  • AllergyTypeMasters [New Master] (1) -- (Many) Allergies: Allergies.AllergyTypeID FK.
  • AllergySeverityMasters [New Master] (1) -- (Many) Allergies: Allergies.AllergySeverityID FK.
  • AllergyReactionMasters [New Master] (1) -- (Many) Allergies: Allergies.AllergyReactionID FK.
  • ClinicalTerminologyMasters [New Master] (1) -- (Many) Allergies: For AllergyCode.
  • DiagnosisCodeMasters [New Master] (1) -- (Many) DXCodes, OrderDiagnosticCodes: DxMasterID FK.
  • OrderPriorityMasters [New Master] (1) -- (Many) LabOrders: LabOrders.OrderPriorityID FK.
  • OrderStatusMasters [New Master] (1) -- (Many) LabOrders: LabOrders.OrderStatusID FK.
  • ResultStatusMasters [New Master] (1) -- (Many) LabOrders, TestResults: ResultStatusID FK.
  • SampleTypeMasters [New Master] (1) -- (Many) TestCatalog, Specimens: SampleTypeID FK.
  • ContainerTypeMasters [New Master] (1) -- (Many) TestCatalog, Specimens: ContainerTypeID FK.
  • RuleTypeMasters [New Master] (1) -- (Many) LISRules: LISRules.RuleTypeID FK.
  • SpecimenStatusMasters [New Master] (1) -- (Many) SpecimenStatusLog [New]: SpecimenStatusLog.StatusID FK.
  • AliquotTypeMasters [New Master] (1) -- (Many) Aliquots: Aliquots.AliquotTypeID FK.
  • AbnormalFlagMasters [New Master] (1) -- (Many) TestResults: TestResults.AbnormalFlagID FK.
  • NotificationMethodMasters [New Master] (1) -- (Many) CriticalValueNotificationLog: CriticalValueNotificationLog.NotificationMethodID FK.
  • NotificationStatusMasters [New Master] (1) -- (Many) CriticalValueNotificationLog: CriticalValueNotificationLog.NotificationStatusID FK.
  • ControlLevelMasters [New Master] (1) -- (Many) QualityControls: QualityControls.ControlLevelID FK.
  • WestgardRuleMasters [New Master] (1) -- (Many) QualityControls: QualityControls.WestgardRuleBreachID FK.
  • PTProgramMasters [New Master] (1) -- (Many) ProficiencyTests: ProficiencyTests.PTProgramID FK.
  • DeviceTypeMasters [New Master] (1) -- (Many) LabInstruments: LabInstruments.DeviceTypeID FK.
  • InterfaceProtocolMasters [New Master] (1) -- (Many) LabInstruments: LabInstruments.InterfaceProtocolID FK.
  • MaintenanceTypeMasters [New Master] (1) -- (Many) InstrumentMaintenanceLog [New]: InstrumentMaintenanceLog.MaintenanceTypeID FK.
  • MappingTypeMasters [New Master] (1) -- (Many) ExternalSystemVocabularyMappings: ExternalSystemVocabularyMappings.MappingTypeID FK.
  • MessageTypeMasters [New Master] (1) -- (Many) MessageLogs [New]: MessageLogs.MessageTypeID FK.
  • MessageDirectionMasters [New Master] (1) -- (Many) MessageLogs [New]: MessageLogs.DirectionID FK.
  • ReportTypeMasters [New Master] (1) -- (Many) ArchivedReports: ArchivedReports.ReportTypeID FK.
  • ReportFormatMasters [New Master] (1) -- (Many) ArchivedReports: ArchivedReports.ReportFormatID FK.
  • LocationTypeMasters [New Master] (1) -- (Many) StorageLocations: StorageLocations.LocationTypeID FK.
  • StorageActionTypeMasters [New Master] (1) -- (Many) SpecimenStorageLog: SpecimenStorageLog.ActionTypeID FK.
  • SpecimenStabilityStatusMasters [New Master] (1) -- (Many) SpecimenStorageLog: SpecimenStorageLog.CurrentStabilityStatusID FK.
  • ProviderTypes [New Master] (1) -- (Many) Providers: Providers.ProviderType FK.
  • LabLocationTypes [New Master] (1) -- (Many) LabLocations: LabLocations.LocationTypeID FK.

This detailed ER description should provide a clear architectural blueprint for your Infinity LIS database, highlighting the relationships and the importance of master tables for data integrity and consistency.