Infinity LIS DB Schema

Table 1: Patients

This table stores core patient demographic and identification details, including external system mappings and personal attributes.

Column	Type	Description
LISPatientID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique internal LIS patient identifier.
ExternalSystemID	SMALLINT, NOT NULL, FOREIGN KEY to ExternalSystems(ExternalSystemID)	Reference to the originating external system.
ExternalSystemPatientID	VARCHAR(100), NULL	Optional external system patient identifier.
ExternalSystemMRN	VARCHAR(100), NOT NULL	External Medical Record Number from the originating system.
FirstName	VARCHAR(100), NOT NULL	Patient’s first name.
LastName	VARCHAR(100), NOT NULL	Patient’s last name.
MiddleName	VARCHAR(100), NULL	Patient’s middle name.
DateOfBirth	DATE, NULL	Patient’s date of birth.
SSN	VARCHAR(25), NULL	Social Security Number (or equivalent national ID).
GenderID	SMALLINT, NOT NULL, FOREIGN KEY to Gender(GenderID)	Patient's gender reference.
DeathIndicator	BIT, NOT NULL, DEFAULT 0	Indicates whether the patient is deceased.
Address1	VARCHAR(255), NULL	Primary street address.
Address2	VARCHAR(255), NULL	Secondary address (e.g., apartment, suite).
City	VARCHAR(100), NULL	City name.
State	VARCHAR(100), NULL	State or province.
ZipCode	VARCHAR(25), NULL	ZIP or postal code.
Country	VARCHAR(100), NULL	Country name.
LanguageID	SMALLINT, NOT NULL, FOREIGN KEY to PrimaryLanguage(LanguageID)	Language spoken by the patient.
MaritalStatusID	SMALLINT, NOT NULL, FOREIGN KEY to MaritalStatus(MaritalStatusID)	Patient's marital status reference.
MobileNumber	VARCHAR(20), NULL	Patient’s mobile phone number.
HomePhoneNumber	VARCHAR(20), NULL	Patient’s home landline number.
EmailId	VARCHAR(100), NULL	Patient’s email address.
RaceCode	VARCHAR(25), NULL	Race code (e.g., as per HL7 or external system).
RaceName	VARCHAR(100), NULL	Human-readable race description.
EthnicityCode	VARCHAR(25), NULL	Ethnicity code (e.g., as per HL7 or external system).
EthnicityName	VARCHAR(100), NULL	Human-readable ethnicity description.
IsReferenceLabPatient	BIT, NOT NULL, DEFAULT 0	Indicates if the patient belongs to a reference lab.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Record creation timestamp.
CreatedByUserID	SMALLINT, NULL	User who created the patient record.
LastModifiedOn	SMALLDATETIME, NULL	Timestamp of the last update.
LastModifiedByUserID	SMALLINT, NULL	User who last modified the patient record.

Table 2: Visits

This table stores patient visit information including encounter metadata, location, and provider details.

Column	Type	Description
LISVisitID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique visit identifier in the LIS.
LISPatientID	INT, NOT NULL, FOREIGN KEY to Patients(LISPatientID)	Reference to the patient associated with the visit.
ExternalSystemID	SMALLINT, NULL, FOREIGN KEY to ExternalSystems(ExternalSystemID)	External system associated with the visit.
ExternalSystemVisitID	VARCHAR(50), NOT NULL, UNIQUE	External system's unique visit identifier.
PatientClass	VARCHAR(50), NOT NULL	Type of patient encounter (e.g., inpatient, outpatient).
AdmitDate	DATETIME, NOT NULL	Date and time of admission.
DischargeDate	DATETIME, NULL	Date and time of discharge, if applicable.
AttendingProviderID	SMALLINT, NOT NULL, FOREIGN KEY to Providers(ProviderID)	ID of the attending provider.
ReferringProviderID	SMALLINT, NOT NULL, FOREIGN KEY to Providers(ProviderID)	ID of the referring provider.
LocationID	VARCHAR(50), NULL	Visit location identifier.
LocationRoom	VARCHAR(50), NULL	Room within the location.
LocationBed	VARCHAR(50), NULL	Bed within the room.
ReasonForVisit	VARCHAR(255), NULL	Reason for the visit or encounter.
VIPIndicator	BIT, NOT NULL, DEFAULT 0	Flags high-profile (VIP) patients.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the visit was created.
CreatedByUserID	SMALLINT, NULL	User who created the record.
LastModifiedOn	SMALLDATETIME, NULL	Timestamp of the last modification.
LastModifiedByUserID	SMALLINT, NULL	User who last modified the record.

Table 3: Insurances

This table stores insurance information linked to a specific patient and visit, including policy and group details.

Column	Type	Description
LISInsuranceID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique internal LIS insurance identifier.
LISPatientID	INT, NOT NULL, FOREIGN KEY to Patients(LISPatientID)	Reference to the patient associated with the insurance.
LISVisitID	INT, NOT NULL, FOREIGN KEY to Visits(LISVisitID)	Reference to the visit during which this insurance is applicable.
SetID	TINYINT, NULL	Internal sequence number if multiple insurances are attached.
InsurancePlanID	VARCHAR(100), NULL	Insurance plan identifier.
InsuranceCompanyID	VARCHAR(100), NULL	External or internal ID for the insurance company.
InsuranceCompanyName	VARCHAR(250), NULL	Full name of the insurance company.
InsuranceGroupNumber	VARCHAR(100), NULL	Group number associated with the insurance plan.
InsuranceGroupName	VARCHAR(250), NULL	Group name associated with the insurance plan.
PlanEffectiveDate	DATE, NULL	Start date of the insurance coverage.
PlanEndDate	DATE, NULL	End date of the insurance coverage.
PolicyNumber	VARCHAR(100), NULL	Policy number assigned to the patient.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Record creation timestamp.
CreatedByUserID	SMALLINT, NULL	ID of the user who created this insurance record.
LastModifiedOn	SMALLDATETIME, NULL	Timestamp of the last modification to this record.
LastModifiedByUserID	SMALLINT, NULL	ID of the user who last modified this record.

Table 4: Allergies

This table stores patient allergy information recorded during a visit, including type, severity, and reactions.

Column	Type	Description
LISAllergyID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique internal LIS allergy identifier.
LISPatientID	INT, NOT NULL, FOREIGN KEY to Patients(LISPatientID)	Reference to the patient associated with the allergy.
LISVisitID	INT, NOT NULL, FOREIGN KEY to Visits(LISVisitID)	Reference to the visit during which the allergy was recorded.
SetID	TINYINT, NULL	Internal sequence number if multiple allergies are recorded.
AllergyType	VARCHAR(50), NULL	Type of allergy (e.g., drug, food, environmental).
AllergyCode	VARCHAR(100), NULL	Code representing the allergen.
AllergyDesc	VARCHAR(250), NULL	Description of the allergen.
AllergySeverity	VARCHAR(100), NULL	Severity of the allergic reaction (e.g., mild, moderate, severe).
AllergyReaction	VARCHAR(100), NULL	Description of the reaction (e.g., rash, anaphylaxis).
IdentificationDate	DATE, NULL	Date when the allergy was identified.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Record creation timestamp.
CreatedByUserID	SMALLINT, NULL	ID of the user who created this allergy record.
LastModifiedOn	SMALLDATETIME, NULL	Timestamp of the last modification to this record.
LastModifiedByUserID	SMALLINT, NULL	ID of the user who last modified this record.

Table 5: DXCodes

This table stores diagnostic (Dx) codes assigned to a patient during a visit, including code type, description, and priority.

Column	Type	Description
LISDxCodeID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique internal LIS diagnosis code identifier.
LISPatientID	INT, NOT NULL, FOREIGN KEY to Patients(LISPatientID)	Reference to the patient associated with the diagnosis.
LISVisitID	INT, NOT NULL, FOREIGN KEY to Visits(LISVisitID)	Reference to the visit during which the diagnosis was made.
DxCode	VARCHAR(100), NULL	Diagnosis code (e.g., ICD-10 code).
DxCodeDesc	VARCHAR(250), NULL	Description of the diagnosis.
DxCodeType	VARCHAR(50), NULL	Type of diagnosis code (e.g., primary, secondary, admitting).
DxCodePriority	TINYINT, NULL	Priority of the diagnosis (lower number = higher priority).
DxDate	DATE, NULL	Date the diagnosis was recorded.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Record creation timestamp.
CreatedByUserID	SMALLINT, NULL	ID of the user who created this record.
LastModifiedOn	SMALLDATETIME, NULL	Timestamp of the last modification.
LastModifiedByUserID	SMALLINT, NULL	ID of the user who last modified the record.

Table 6: LabOrders

This table stores laboratory orders placed for patients, including source system, ordering provider, status, and test catalog references.

Column	Type	Description
LISOrderID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique internal LIS order identifier.
ExternalSystemID	SMALLINT, NULL, FOREIGN KEY to ExternalSystems(ExternalSystemID)	External system from which the order originated.
ExternalSystemOrderID	VARCHAR(50), NOT NULL, UNIQUE	Unique order identifier in the external system.
LISPatientID	INT, NOT NULL, FOREIGN KEY to Patients(LISPatientID)	Reference to the patient for whom the order was placed.
LISVisitID	INT, NOT NULL, FOREIGN KEY to Visits(LISVisitID)	Reference to the visit associated with the order.
SetID	TINYINT, NULL	Sequence number for distinguishing multiple orders in a group.
OrderingProviderID	SMALLINT, NOT NULL, FOREIGN KEY to Providers(ProviderID)	Provider who placed the order.
OrderDate	DATETIME, NOT NULL	Date the order was created or initiated.
OrderTransactionDate	DATETIME, NOT NULL	Timestamp of the order transaction event.
OrderPriority	VARCHAR(25), NOT NULL	Priority of the order (e.g., STAT, Routine).
OrderTestCatalogID	INT, NOT NULL, FOREIGN KEY to TestCatalog(TestCatalogID)	Reference to the test catalog entry associated with the order.
OrderStatus	VARCHAR(50), NOT NULL	Current status of the lab order (e.g., Ordered, Collected, Cancelled).
ResultStatus	VARCHAR(50), NOT NULL	Result status (e.g., Pending, Final, Corrected).
ReasonForStudy	VARCHAR(250), NULL	Clinical reason or justification for ordering the test.
IsDuplicateCheckPerformed	BIT, NOT NULL, DEFAULT 0	Indicates whether duplicate order check was performed.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the order record was created.
CreatedByUserID	SMALLINT, NULL	User ID of the person who created the record.
LastModifiedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp of the last update to the record.
LastModifiedByUserID	SMALLINT, NULL	User ID of the person who last modified the record.

Table 7: OrderDiagnosticCodes

This table stores diagnostic (ICD-10) codes associated with specific lab orders, allowing linkage of medical necessity to ordered tests.

Column	Type	Description
OrderDiagnosticCodeID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the order diagnostic code entry.
LISOrderID	INT, NOT NULL, FOREIGN KEY to LabOrders(LISOrderID)	Reference to the associated lab order.
ICD10Code	VARCHAR(20), NOT NULL	ICD-10 diagnostic code.
Description	VARCHAR(255), NULL	Description of the diagnosis or condition.

Table 8: TestPanels

This table defines lab test panels, including identifiers such as LOINC and CPT codes, status flags, and effective dates.

Column	Type	Description
TestPanelID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the test panel.
PanelName	VARCHAR(100), NOT NULL	Name of the test panel.
PanelLOINCCode	VARCHAR(20), NULL	Optional LOINC code representing the panel.
PanelCPTCode	VARCHAR(20), NULL	Optional CPT code associated with the panel.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the panel is currently active.
EffectiveDate	DATE, NOT NULL	Date the panel becomes effective/available for ordering.
RetiredDate	DATE, NULL	Date the panel is retired or no longer active.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Record creation timestamp.
CreatedByUserID	SMALLINT, NULL	ID of the user who created the record.
LastModifiedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp of the last update to the record.
LastModifiedByUserID	SMALLINT, NULL	ID of the user who last modified the record.

Table 9: TestCatalog

This table stores the master catalog of lab tests, including codes, categorization, sample and container types, and test panel linkage.

Column	Type	Description
TestCatalogID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the test catalog entry.
TestCode	VARCHAR(100), NOT NULL	Unique code representing the test.
TestName	VARCHAR(250), NOT NULL	Human-readable name of the test.
TestCategoryID	SMALLINT, NOT NULL, FOREIGN KEY to TestCategory(TestCategoryID)	Reference to the category this test belongs to.
LOINCCode	VARCHAR(20), NULL	Optional LOINC code assigned to the test.
CPTCode	VARCHAR(20), NULL	Optional CPT billing code.
TestPanelID	INT, NULL, FOREIGN KEY to TestPanels(TestPanelID)	Reference to the panel this test may be part of.
SampleType	VARCHAR(50), NOT NULL	Type of biological sample required (e.g., Blood, Urine).
ContainerType	VARCHAR(50), NULL	Type of container to collect the sample (e.g., Red Top Tube).
PreAnalyticalInstructions	VARCHAR(MAX), NULL	Instructions to follow before sample collection.
Units	VARCHAR(20), NULL	Measurement units for the test result (e.g., mg/dL, mmol/L).
IsResultNumeric	BIT, NOT NULL, DEFAULT 1	Indicates if the result is numeric (1) or non-numeric (0).
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the test is currently active.
EffectiveDate	DATE, NOT NULL	Date from which the test becomes available.
RetiredDate	DATE, NULL	Date on which the test is retired or made inactive.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the test record was created.
CreatedByUserID	SMALLINT, NULL	ID of the user who created this record.
LastModifiedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp of the last modification to the test record.
LastModifiedByUserID	SMALLINT, NULL	ID of the user who last modified the record.

Table 10: TestPanelComponents

This table defines the individual test components that make up a test panel. It maps entries from the test catalog to a panel.

Column	Type	Description
TestPanelComponentID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the test panel component entry.
TestPanelID	INT, NOT NULL, FOREIGN KEY to TestPanels(TestPanelID)	Reference to the parent test panel.
TestCatalogID	INT, NOT NULL, FOREIGN KEY to TestCatalog(TestCatalogID)	Reference to the individual test from the test catalog.
DisplayOrder	TINYINT, NULL	Optional display order for the test within the panel.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the test component is currently active.
EffectiveDate	DATE, NOT NULL	Date when the test component becomes active in the panel.
RetiredDate	DATE, NULL	Date when the component is removed or made inactive from the panel.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the component entry was created.
CreatedByUserID	SMALLINT, NULL	ID of the user who created the component entry.
LastModifiedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp of the last modification to the component entry.
LastModifiedByUserID	SMALLINT, NULL	ID of the user who last modified the component entry.

Table 11: LISRules

This table defines configurable rules applied to lab tests, including logic expressions, rule types, and activation status.

Column	Type	Description
RuleID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the LIS rule.
RuleName	VARCHAR(100), NOT NULL	Name of the rule.
RuleType	VARCHAR(50), NOT NULL	Type/category of the rule (e.g., validation, workflow).
TestCatalogID	INT, NULL, FOREIGN KEY to TestCatalog(TestCatalogID)	Optional reference to the test this rule applies to.
RuleLogic	VARCHAR(MAX), NOT NULL	Logic expression or script defining the rule behavior.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the rule is currently active.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the rule was created.
CreatedByUserID	SMALLINT, NULL	ID of the user who created the rule.
LastModifiedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp of the last update to the rule.
LastModifiedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	ID of the user who last modified the rule.

Table 12: Specimens

This table stores information about biological specimens collected for lab orders, including status, chain of custody, and collection metadata.

Column	Type	Description
SpecimenID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the specimen.
LISOrderID	INT, NOT NULL, FOREIGN KEY to LabOrders(LISOrderID)	Reference to the associated lab order.
BarcodeID	VARCHAR(50), NOT NULL, UNIQUE	Unique barcode assigned to the specimen.
SpecimenType	VARCHAR(50), NOT NULL	Type of specimen collected (e.g., blood, urine).
ContainerType	VARCHAR(50), NULL	Type of container used for the specimen.
CollectionDateTime	DATETIME, NULL	Date and time when the specimen was collected.
CollectedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	ID of the user who collected the specimen.
CollectionSite	VARCHAR(100), NULL	Physical site or location of specimen collection.
ReceivedDateTime	DATETIME, NULL	Date and time when the specimen was received in the lab.
ReceivedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	ID of the user who received the specimen.
CurrentStatus	VARCHAR(50), NOT NULL	Current status of the specimen (e.g., Collected, In Transit, Received).
Priority	VARCHAR(20), NOT NULL	Priority of the specimen (e.g., STAT, Routine).
Volume	DECIMAL(10,2), NULL	Volume of the specimen collected.
IntegrityNotes	VARCHAR(MAX), NULL	Notes regarding specimen integrity (e.g., hemolysis, leakage).
RejectionReasonID	SMALLINT, NULL, FOREIGN KEY to RejectionReasons(ReasonID)	Reason for rejecting the specimen, if applicable.
ChainOfCustody	VARCHAR(MAX), NULL	Record of the chain of custody for the specimen.
CurrentLocation	VARCHAR(100), NULL	Current physical location of the specimen.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the specimen record was created.
CreatedByUserID	SMALLINT, NULL	ID of the user who created the specimen record.
LastModifiedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp of the last update to the specimen record.
LastModifiedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	ID of the user who last modified the specimen record.

Table 13: Aliquots

This table stores information about aliquots, which are sub-samples derived from a parent specimen, including tracking, volume, and storage details.

Column	Type	Description
AliquotID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the aliquot.
ParentSpecimenID	INT, NOT NULL, FOREIGN KEY to Specimens(SpecimenID)	Reference to the parent specimen from which the aliquot is derived.
AliquotBarcode	VARCHAR(50), NOT NULL, UNIQUE	Unique barcode assigned to the aliquot.
AliquotType	VARCHAR(50), NULL	Type or classification of the aliquot (e.g., serum, plasma).
Volume	DECIMAL(10,2), NULL	Volume of the aliquot.
StorageLocationID	SMALLINT, NULL, FOREIGN KEY to StorageLocations(LocationID)	Reference to the storage location of the aliquot.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the aliquot record was created.
CreatedByUserID	SMALLINT, NULL	ID of the user who created the aliquot record.
LastModifiedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp of the last modification to the aliquot record.
LastModifiedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	ID of the user who last modified the aliquot record.

Table 14: RejectionReasons

This table stores predefined reasons for rejecting specimens, including their code, description, and status.

Column	Type	Description
ReasonID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the rejection reason.
ReasonCode	VARCHAR(10), NOT NULL, UNIQUE	Short code representing the rejection reason.
Description	VARCHAR(255), NOT NULL	Detailed description of the reason for rejection.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the reason is currently active.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the rejection reason was created.
CreatedByUserID	SMALLINT, NULL	ID of the user who created this record.
LastModifiedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp of the last modification to this record.
LastModifiedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	ID of the user who last modified the record.

Table 15: TestResults

This table stores lab test results, including patient linkage, result values, reference ranges, verification, and critical value tracking.

Column	Type	Description
ResultID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the test result record.
LISOrderID	INT, NOT NULL, FOREIGN KEY to LabOrders(LISOrderID)	Reference to the lab order associated with the result.
LISPatientID	INT, NOT NULL, FOREIGN KEY to Patients(LISPatientID)	Reference to the patient associated with the result.
LISVisitID	INT, NOT NULL, FOREIGN KEY to Visits(LISVisitID)	Reference to the visit associated with the result.
SpecimenID	INT, NOT NULL, FOREIGN KEY to Specimens(SpecimenID)	Reference to the specimen from which the result was obtained.
TestCatalogID	INT, NOT NULL, FOREIGN KEY to TestCatalog(TestCatalogID)	Reference to the test from the catalog.
ResultValue	VARCHAR(255), NOT NULL	Value of the test result.
Units	VARCHAR(20), NULL	Measurement units for the result (e.g., mg/dL).
ReferenceRangeLow	DECIMAL(10,2), NULL	Lower limit of the reference range.
ReferenceRangeHigh	DECIMAL(10,2), NULL	Upper limit of the reference range.
AbnormalFlag	VARCHAR(20), NULL	Flag indicating abnormality (e.g., High, Low, Critical).
ResultStatus	VARCHAR(20), NOT NULL	Status of the result (e.g., Final, Corrected).
VerificationUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	User who verified the result.
ResultDateTime	DATETIME, NULL	Timestamp when the result was entered.
VerificationDateTime	DATETIME, NULL	Timestamp when the result was verified.
IsAutoverified	BIT, NOT NULL, DEFAULT 0	Indicates if the result was autoverified.
AutoverificationRulesApplied	VARCHAR(MAX), NULL	Rules used for autoverification, if any.
InstrumentID	INT, NULL, FOREIGN KEY to LabInstruments(InstrumentID)	Instrument used to produce the result.
ResultComments	VARCHAR(MAX), NULL	Additional comments about the result.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the result record was created.
CreatedByUserID	SMALLINT, NULL	ID of the user who created the record.
ChangeReason	VARCHAR(MAX), NULL	Reason for any result change.
LastModifiedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp of the last modification.
LastModifiedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	ID of the user who last modified the record.
IsCriticalValue	BIT, NOT NULL, DEFAULT 0	Indicates whether the result is a critical value.
CriticalValueNotificationLogID	INT, NULL, FOREIGN KEY to CriticalValueNotificationLog(LogID)	Reference to the log of critical value notification.
TestAnalysisStart	DATETIME, NULL	Start time of result analysis.
TestAnalysisEnd	DATETIME, NULL	End time of result analysis.

Table 16: TestResultVersions

This table maintains version history for test results, capturing previous result values, statuses, and reasons for changes.

Column	Type	Description
ResultVersionID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the versioned result entry.
ResultID	INT, NOT NULL, FOREIGN KEY to TestResults(ResultID)	Reference to the original test result being versioned.
VersionNumber	INT, NOT NULL	Version number of the result (incremented with each change).
ResultValue	VARCHAR(255), NULL	Value of the test result in this version.
ResultStatus	VARCHAR(20), NULL	Status of the result in this version (e.g., Final, Corrected).
ChangeReason	VARCHAR(MAX), NULL	Reason for the result change.
ChangedByUserID	SMALLINT, NOT NULL, FOREIGN KEY to Users(UserID)	ID of the user who made the change.
ChangeDateTime	DATETIME, NOT NULL	Timestamp of when the change occurred.

Table 17: CriticalValueNotificationLog

This table logs notifications sent when a critical test result is identified. It records provider notification, method, and user actions.

Column	Type	Description
LogID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the critical value notification log entry.
ResultID	INT, NOT NULL, FOREIGN KEY to TestResults(ResultID)	Reference to the critical test result.
NotificationDateTime	SMALLDATETIME, NOT NULL	Date and time the notification was sent.
NotifiedProviderID	SMALLINT, NULL, FOREIGN KEY to Providers(ProviderID)	Provider who was notified, if applicable.
NotifiedName	VARCHAR(100), NULL	Name of the individual who was notified (if not a provider).
NotificationMethod	VARCHAR(50), NULL	Method of notification (e.g., phone, SMS, email).
NotificationStatus	VARCHAR(50), NULL	Status of the notification (e.g., successful, failed, pending).
CallbackNumber	VARCHAR(20), NULL	Contact number for callback or confirmation.
LoggedByUserID	SMALLINT, NOT NULL, FOREIGN KEY to Users(UserID)	User who logged the critical value notification.

Table 18: QualityControls

This table logs quality control (QC) events for lab tests, including measured values, instrument used, user actions, and rule breaches.

Column	Type	Description
QCID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the QC record.
TestCatalogID	INT, NOT NULL, FOREIGN KEY to TestCatalog(TestCatalogID)	Reference to the test for which QC was performed.
InstrumentID	INT, NOT NULL, FOREIGN KEY to LabInstruments(InstrumentID)	Reference to the instrument used for QC.
ControlLotNumber	VARCHAR(50), NOT NULL	Lot number of the control material used.
ControlLevel	VARCHAR(20), NOT NULL	QC level (e.g., Level 1, Normal, High).
QCDateTime	DATETIME, NOT NULL	Timestamp when the QC was performed.
QCValue	DECIMAL(10,2), NOT NULL	Measured value during the QC event.
IsQCValid	BIT, NOT NULL	Indicates whether the QC result was valid.
WestgardRuleBreach	VARCHAR(50), NULL	Breached Westgard rule, if any (e.g., 1:2s, 2:2s).
CorrectiveActions	VARCHAR(MAX), NULL	Description of any corrective actions taken.
RootCauseAnalysis	VARCHAR(MAX), NULL	Root cause investigation summary if QC was invalid.
PerformedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	ID of the user who performed the QC.

Table 19: LabInstruments

This table stores configuration and metadata details about laboratory instruments integrated with the LIS.

Column	Type	Description
InstrumentID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the lab instrument.
InstrumentName	VARCHAR(100), NOT NULL	Name of the instrument.
Vendor	VARCHAR(100), NULL	Vendor or supplier of the instrument.
Manufacturer	VARCHAR(100), NULL	Manufacturer of the instrument.
Model	VARCHAR(100), NULL	Model identifier of the instrument.
SerialNumber	VARCHAR(100), NULL	Serial number of the instrument.
IsLicensed	BIT, NOT NULL, DEFAULT 1	Indicates whether the instrument is licensed for use.
DeviceType	VARCHAR(100), NULL	Type/category of the device (e.g., Hematology, Chemistry).
Location	VARCHAR(100), NULL	Physical location of the instrument within the lab.
SubLocation	VARCHAR(100), NULL	Specific room or bench within the lab location.
InterfaceProtocol	VARCHAR(20), NULL	Communication protocol (e.g., ASTM, HL7).
IPAddress	VARCHAR(15), NULL	Network IP address of the instrument.
Port	INT, NULL	Port used for network communication.
Middleware	VARCHAR(25), NULL	Middleware or LIS integration platform used.
Notes	VARCHAR(250), NULL	Additional notes or comments.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the instrument is actively used in the lab.

Table 20: InstrumentTestMappings

This table maps LIS test catalog entries to the corresponding test codes used by lab instruments, allowing accurate result association and communication.

Column	Type	Description
MappingID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the mapping entry.
InstrumentID	INT, NOT NULL, FOREIGN KEY to LabInstruments(InstrumentID)	Reference to the lab instrument.
TestCatalogID	INT, NOT NULL, FOREIGN KEY to TestCatalog(TestCatalogID)	Reference to the LIS test catalog entry.
InstrumentTestCode	VARCHAR(50), NOT NULL	Test code used by the instrument corresponding to the LIS test.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the mapping is currently active.

Table 21: ExternalSystemOtherMappings

This table stores code mappings between external systems and internal LIS codes for various mapping types (e.g., test codes, location codes).

Column	Type	Description
MappingID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the mapping record.
ExternalSystemID	SMALLINT, NULL, FOREIGN KEY to ExternalSystems(ExternalSystemID)	Reference to the external system.
MappingType	VARCHAR(50), NOT NULL	Type/category of the mapping (e.g., TestCode, Department).
SourceCode	VARCHAR(50), NOT NULL	Code received from the external system.
TargetCode	VARCHAR(50), NOT NULL	Corresponding internal LIS code.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether this mapping is currently active.
LastModifiedOn	DATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp of the last modification.
LastModifiedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	ID of the user who last modified the mapping.

Table 22: Users

This table stores user account information including login credentials, personal details, and audit metadata.

Column	Type	Description
UserID	SMALLINT, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for each user.
Username	VARCHAR(50), NOT NULL, UNIQUE	Unique login name for the user.
PasswordHash	VARCHAR(255), NOT NULL	Secure hash of the user's password.
FirstName	VARCHAR(100), NOT NULL	User’s first name.
LastName	VARCHAR(100), NOT NULL	User’s last name.
Email	VARCHAR(255), NULL, UNIQUE	Email address of the user.
PhoneNumber	VARCHAR(50), NULL	Contact number of the user.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the user account is active.
LastLoginDateTime	DATETIME, NULL	Last recorded login date and time.
Force2FA	BIT, NOT NULL, DEFAULT 1	Indicates whether two-factor authentication is enforced.
CreatedOn	DATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the user account was created.
CreatedByUserID	SMALLINT, NULL, FOREIGN KEY to Users.UserID	Reference to the user who created this account.

Table 23: Roles

This table defines the available roles in the system for access control and authorization purposes.

Column	Type	Description
RoleID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the role.
RoleName	VARCHAR(50), NOT NULL, UNIQUE	Name of the role (e.g., Admin, LabTech).
Description	VARCHAR(255), NULL	Optional description of the role's purpose.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the role is currently active.

Table 24: UserRoles

This table manages role assignments for users, allowing multiple roles per user to support flexible access control.

Column	Type	Description
UserRoleID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the user-role assignment.
UserID	SMALLINT, NOT NULL, FOREIGN KEY to Users(UserID)	Reference to the user assigned to the role.
RoleID	SMALLINT, NOT NULL, FOREIGN KEY to Roles(RoleID)	Reference to the assigned role.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the role assignment is active.

Table 25: Permissions

This table defines individual permissions that can be granted to roles for controlling access to system features.

Column	Type	Description
PermissionID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the permission.
PermissionName	VARCHAR(100), NOT NULL, UNIQUE	Name of the permission (e.g., ViewResults, EditUsers).
Description	VARCHAR(255), NULL	Description of what the permission allows.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the permission is currently active.

Table 26: RolePermissions

This table defines which permissions are granted to each role, supporting fine-grained access control.

Column	Type	Description
RolePermissionID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the role-permission assignment.
RoleID	SMALLINT, NOT NULL, FOREIGN KEY to Roles(RoleID)	Reference to the role.
PermissionID	SMALLINT, NOT NULL, FOREIGN KEY to Permissions(PermissionID)	Reference to the permission assigned to the role.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates whether the permission assignment is active.

Table 27: AuditLogs

This table captures audit trail information for user actions across the system, including before/after values, timestamps, and change reasons.

Column	Type	Description
LogID	BIGINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for each audit log entry.
LogDateTime	SMALLDATETIME, NOT NULL	Timestamp of the logged activity.
UserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	User who performed the activity (if available).
WorkstationIP	VARCHAR(50), NULL	IP address of the workstation used.
ActivityType	VARCHAR(100), NOT NULL	Type of activity (e.g., INSERT, UPDATE, DELETE, LOGIN).
TableName	VARCHAR(100), NULL	Name of the table affected.
RecordID	VARCHAR(100), NULL	Identifier of the affected record (primary key or business key).
OldValue	VARCHAR(MAX), NULL	Previous value(s) before the change.
NewValue	VARCHAR(MAX), NULL	New value(s) after the change.
ReasonForChange	VARCHAR(MAX), NULL	Reason provided for the change, if applicable.
TransactionDetails	VARCHAR(MAX), NULL	Additional details (e.g., transaction ID, batch reference, etc.).

Table 28: Providers

This table stores details about healthcare providers such as physicians or referring doctors. It includes contact information and unique identifiers.

Column	Type	Description
ProviderID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the provider.
ExternalSystemID	SMALLINT, NULL, FOREIGN KEY to ExternalSystems(ExternalSystemID)	Reference to the originating external system.
ProviderType	VARCHAR(50), NULL	Type of provider (e.g., Physician, Referring Doctor).
FirstName	VARCHAR(100), NOT NULL	First name of the provider.
LastName	VARCHAR(100), NOT NULL	Last name of the provider.
MiddleName	VARCHAR(100), NOT NULL	Last name of the provider.
NPI	VARCHAR(10), NULL, UNIQUE	National Provider Identifier (NPI), must be unique if present.
ContactEmail	VARCHAR(255), NULL	Email address of the provider.
ContactPhone	VARCHAR(20), NULL	Phone number of the provider.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates if the provider is active.

Table 29: ArchivedReports

This table stores metadata for archived laboratory reports, including their associated order, patient, visit, storage location, and retrieval eligibility.

Column	Type	Description
ArchiveID	BIGINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the archived report.
LISOrderID	INT, NOT NULL, FOREIGN KEY to LabOrders(LISOrderID)	Reference to the lab order associated with the report.
LISPatientID	INT, NOT NULL, FOREIGN KEY to Patients(LISPatientID)	Reference to the patient associated with the report.
LISVisitID	INT, NOT NULL, FOREIGN KEY to Visits(LISVisitID)	Reference to the patient visit associated with the report.
ReportType	VARCHAR(50), NULL	Type of report (e.g., Final, Preliminary, Amended).
ReportFormat	VARCHAR(20), NULL	Format of the archived report (e.g., PDF, XML).
StorageLocation	VARCHAR(255), NULL	Path or location where the report is physically or digitally stored.
ArchiveDateTime	SMALLDATETIME, NOT NULL	Date and time the report was archived.
RetentionPolicyEndDate	DATE, NULL	The date after which the report is no longer required to be retained.
IsRetrievable	BIT, NOT NULL, DEFAULT 1	Indicates if the report is currently retrievable by users or systems.
IndexedFields	VARCHAR(MAX), NULL	Searchable metadata to assist in fast retrieval of archived reports.

Table 30: StorageLocations

This table tracks physical storage locations used for storing specimens, aliquots, and other lab materials, including environmental and capacity metadata.

Column	Type	Description
LocationID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the storage location.
LocationName	VARCHAR(100), NOT NULL	Name or label of the storage location (e.g., Freezer A, Shelf 3).
LocationType	VARCHAR(50), NOT NULL	Type of storage (e.g., Refrigerator, Freezer, Room Temperature).
Capacity	INT, NULL	Maximum capacity of the location (e.g., number of specimens).
CurrentTemperature	DECIMAL(5,2), NULL	Latest recorded temperature at the location.
LastTempReadingDateTime	DATETIME, NULL	Date and time of the last temperature reading.
TemperatureAlertThreshold	DECIMAL(5,2), NULL	Threshold temperature that triggers alerts.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates if the storage location is currently in use.
CreateDate	DATETIME, NOT NULL	Date the storage location record was created.

Table 31: SpecimenStorageLog

This table logs storage actions (e.g., placement, movement, retrieval) for specimens, including location, responsible user, and stability tracking.

Column	Type	Description
StorageLogID	BIGINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the storage log entry.
SpecimenID	INT, NOT NULL, FOREIGN KEY to Specimens(SpecimenID)	Reference to the specimen being stored or moved.
StorageLocationID	SMALLINT, NOT NULL, FOREIGN KEY to StorageLocations(LocationID)	Reference to the storage location.
PositionInLocation	VARCHAR(50), NULL	Specific position within the storage location (e.g., Rack 3, Box A).
ActionType	VARCHAR(20), NOT NULL	Type of action (e.g., Store, Retrieve, Move).
ActionDateTime	SMALLDATETIME, NOT NULL	Date and time when the action occurred.
ActionByUserID	SMALLINT, NOT NULL, FOREIGN KEY to Users(UserID)	User who performed the storage action.
Justification	VARCHAR(MAX), NULL	Reason for the action, if applicable.
CurrentStabilityStatus	VARCHAR(50), NULL	Status of specimen stability after the action (e.g., Stable, Compromised).
StabilityExpiryDateTime	SMALLDATETIME, NULL	Expected expiry date/time of specimen stability in current conditions.

Table 32: TableMetaData

This table stores metadata descriptions for columns within all other database tables. It enables dynamic documentation or tooltips in user interfaces.

Column	Type	Description
TableMetaDataId	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the metadata entry.
TableName	VARCHAR(100), NOT NULL	Name of the table to which the metadata applies.
ColumnName	VARCHAR(100), NOT NULL	Name of the column being described.
Description	VARCHAR(MAX), NULL	Detailed description or purpose of the column.
CreatedOn	DATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the metadata record was created.
CreatedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	User who created the metadata record.

Table 33: ExternalSystems

This table stores configuration and contact information for external systems integrated with the LIS (e.g., EMRs, middleware, APIs), including HL7 and API settings.

Column	Type	Description
ExternalSystemID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the external system.
SystemName	VARCHAR(255), NOT NULL	Name of the external system.
SystemCode	VARCHAR(50), NOT NULL	Unique code to identify the system.
SystemType	VARCHAR(50), NOT NULL	Type of system (e.g., EMR, Middleware, API).
CommunicationMode	VARCHAR(50), NOT NULL	Communication mode (e.g., HL7, API, SFTP).
Direction	VARCHAR(50), NOT NULL	Data direction: Inbound, Outbound, or Bidirectional.
HL7Version	VARCHAR(20), NULL	HL7 version used for message formatting.
HL7ADTEnabled	BIT, NOT NULL, DEFAULT 0	Whether HL7 ADT (Admission/Discharge/Transfer) is enabled.
HL7ORMEnabled	BIT, NOT NULL, DEFAULT 0	Whether HL7 ORM (Order Entry) is enabled.
HL7ORUEnabled	BIT, NOT NULL, DEFAULT 0	Whether HL7 ORU (Observation Result) is enabled.
HL7EndpointURL	VARCHAR(500), NULL	Endpoint URL for HL7 communication.
HL7Port	INT, NULL	Port number for HL7 communication.
APIEndpointURL	VARCHAR(500), NULL	REST API endpoint URL, if applicable.
APIAuthType	VARCHAR(50), NULL	Type of authentication used for API (e.g., Basic, OAuth2).
ContactPerson	VARCHAR(255), NULL	Contact person responsible for integration support.
ContactEmail	VARCHAR(255), NULL	Email address of the contact person.
ContactPhone	VARCHAR(255), NULL	Phone number of the contact person.
IntegrationStatus	VARCHAR(50), NOT NULL	Status of the system's integration (e.g., Active, Testing, Disabled).
LastUpdated	DATETIME, NULL	Last update timestamp of this record.
Notes	VARCHAR(MAX), NULL	Any additional information or notes.
ReceivingApplication	VARCHAR(50), NULL	HL7 receiving application name, if applicable.
Address1	VARCHAR(255), NULL	Address line 1 of the external system.
Address2	VARCHAR(255), NULL	Address line 2 of the external system.
City	VARCHAR(100), NULL	City of the external system.
State	VARCHAR(100), NULL	State or province.
ZipCode	VARCHAR(25), NULL	Postal or zip code.
Country	VARCHAR(100), NULL	Country of the external system.

Table 34: ExternalSystemTestMappings

This table stores mappings between LIS test catalog entries and corresponding test codes/names in external systems for interoperability and integration.

Column	Type	Description
TestExternalMappingId	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the test mapping record.
TestCatalogID	INT, NOT NULL	Reference to the LIS test (foreign key may exist in TestCatalog table).
ExternalSystemId	SMALLINT, NOT NULL, FOREIGN KEY to ExternalSystems(ExternalSystemID)	Reference to the external system associated with the test.
ExternalSystemTestCode	VARCHAR(100), NOT NULL	Test code as defined by the external system.
ExternalSystemTestName	VARCHAR(100), NOT NULL	Test name as defined by the external system.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates if the mapping is currently active.
EffectiveDate	DATE, NULL	The date when the mapping becomes effective.
RetiredDate	DATE, NULL	The date when the mapping was retired or superseded.

Table 35: Gender

This table defines standardized gender codes used across patient demographic records.

Column	Type	Description
GenderID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the gender.
GenderCode	VARCHAR(50), NOT NULL, UNIQUE	Short code representing gender (e.g., M, F, Other).
GenderDesc	VARCHAR(150), NULL	Full description of the gender code.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the gender record was created.

Table 36: MaritalStatus

This table stores standardized marital status codes used in patient demographic information.

Column	Type	Description
MaritalStatusID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the marital status entry.
MaritalStatusCode	VARCHAR(50), NOT NULL, UNIQUE	Code representing marital status (e.g., S, M, D, W).
MaritalStatusDesc	VARCHAR(150), NULL	Full description of the marital status.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the marital status record was created.

Table 37: PrimaryLanguage

This table defines standardized language codes used for patient communication preferences.

Column	Type	Description
LanguageID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the language.
LanguageCode	VARCHAR(50), NOT NULL, UNIQUE	Code representing the language (e.g., EN, HI, TE).
LanguageDesc	VARCHAR(150), NULL	Full description of the language.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the language entry was created.

Table 38: TestCategory

This table stores high-level classifications or categories for lab tests (e.g., Hematology, Microbiology).

Column	Type	Description
TestCategoryID	SMALLINT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the test category.
TestCategoryCode	VARCHAR(100), NOT NULL, UNIQUE	Unique code representing the test category.
TestCategoryName	VARCHAR(100), NOT NULL	Name of the test category.
TestCategoryDesc	VARCHAR(150), NULL	Optional description of the test category.
IsActive	BIT, NOT NULL, DEFAULT 1	Indicates if the category is active.
CreatedOn	SMALLDATETIME, NOT NULL, DEFAULT GETDATE()	Timestamp when the category was created.
LastModifiedOn	SMALLDATETIME, NULL	Timestamp of the last modification to the category.

Table 39: TestRanges

Defines the reference and panic ranges for a specific test in the catalog, optionally based on age and gender.

Column	Type	Description
TestRangeID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the test range entry.
TestCatalogID	INT, NOT NULL, FOREIGN KEY to TestCatalog(TestCatalogID)	Associated test from the test catalog.
ReferenceRangeLow	DECIMAL(10,2), NULL	Lower bound of the normal reference range.
ReferenceRangeHigh	DECIMAL(10,2), NULL	Upper bound of the normal reference range.
PanicLow	DECIMAL(10,2), NULL	Lower threshold triggering a panic alert.
PanicHigh	DECIMAL(10,2), NULL	Upper threshold triggering a panic alert.
MinAge	SMALLINT, NULL	Minimum patient age this range applies to.
MaxAge	SMALLINT, NULL	Maximum patient age this range applies to.
GenderID	SMALLINT, NOT NULL, FOREIGN KEY to Gender(GenderID)	Gender this range applies to.
IsActive	BIT, NOT NULL, DEFAULT 1	Whether the range is currently active.
EffectiveDate	DATE, NOT NULL	Start date for range applicability.
RetiredDate	DATE, NULL	Optional end date for the range.

Table 40: ProficiencyTests

Tracks participation in external proficiency testing programs to ensure test accuracy and compliance.

Column	Type	Description
PTID	INT, NOT NULL, PRIMARY KEY, IDENTITY(1,1)	Unique identifier for the proficiency test record.
TestCatalogID	INT, NOT NULL, FOREIGN KEY to TestCatalog(TestCatalogID)	Reference to the tested item from the LIS test catalog.
PTProgram	VARCHAR(100), NULL	Name of the proficiency testing program or provider.
PTEventID	VARCHAR(50), NOT NULL	Unique identifier for the PT event/session.
SampleID	VARCHAR(50), NOT NULL	Identifier for the test sample used.
ExpectedResult	VARCHAR(255), NOT NULL	The expected result from the PT provider.
ActualResult	VARCHAR(255), NULL	The result obtained in LIS/lab testing.
IsCorrect	BIT, NULL	Indicates whether the result was correct.
PerformedDate	DATE, NULL	Date the test was performed.
PerformedByUserID	SMALLINT, NULL, FOREIGN KEY to Users(UserID)	User who performed the test.
SubmissionDate	DATE, NULL	Date the results were submitted to the PT provider.
Score	DECIMAL(5,2), NULL	Numeric score (if provided by the PT program).