tblMAIN_SURGERY


Columns

Column Type Size Nulls Auto Default Children Parents Comments
SURGERY_ID int identity 4 √ null
lnkUpperGIPostoperativeComplications.SurgeryID FK_lnkUpperGIPostoperativeComplications_tblMAIN_SURGERYR
tblHNProcedureNeeded.SurgeryID FK_tblHNProcedureNeeded_tblMAIN_SURGERY_SurgeryIDR
tblPOSSUM_SCORE.SURGERY_ID FK_tblPOSSUM_SCORE_tblMAIN_SURGERYC
tblSURGERY_BRAIN.SURGERY_ID FK_tblSURGERY_BRAIN_tblMAIN_SURGERYC
tblSURGERY_BREAST.SURGERY_ID FK_tblSURGERY_BREAST_tblMAIN_SURGERYC
tblSURGERY_COLORECTAL.SURGERY_ID FK_tblSURGERY_COLORECTAL_tblMAIN_SURGERYC
tblSURGERY_DAHNO_GMC_CODES.SURGERY_ID FK_tblSURGERY_DAHNO_GMC_CODES_tblMAIN_SURGERYC
tblSURGERY_DERMATOLOGY.SURGERY_ID FK_tblSURGERY_DERMATOLOGY_tblMAIN_SURGERYC
tblSURGERY_GYNAECOLOGY.SURGERY_ID FK_tblSURGERY_GYNAECOLOGY_tblMAIN_SURGERYC
tblSURGERY_HAEMATOLOGY.SURGERY_ID FK_tblSURGERY_HAEMATOLOGY_tblMAIN_SURGERYC
tblSURGERY_HEAD_NECK.SURGERY_ID FK_tblSURGERY_HEAD_NECK_tblMAIN_SURGERYC
tblSURGERY_LUNG.SURGERY_ID FK_tblSURGERY_LUNG_tblMAIN_SURGERYC
tblSURGERY_PAEDIATRICS.SURGERY_ID FK_tblSURGERY_PAEDIATRICS_tblMAIN_SURGERYC
tblSURGERY_SARCOMA.SURGERY_ID FK_tblSURGERY_SARCOMA_tblMAIN_SURGERYC
tblSURGERY_UPPER_GI.SURGERY_ID FK_tblSURGERY_UPPER_GI_tblMAIN_SURGERYC
tblSURGERY_UROLOGY.SURGERY_ID FK_tblSURGERY_UROLOGY_tblMAIN_SURGERYC
tblUNPLANNED_RETURN_TO_THEATRE.SURGERY_ID FK_tblUNPLANNED_RETURN_TO_THEATRE_tblMAIN_SURGERYR

Unique Record ID (Auto)
CARE_ID int 4 null
tblMAIN_REFERRALS.CARE_ID FK_tblMAIN_SURGERY_tblMAIN_REFERRALSC

Links record to Referrals
TEMP_ID varchar 255 √ null
ltblTEMP_NHS_NUMBERS.TEMP_ID Implied ConstraintR

System ID
N7_1_SITE_CODE varchar 5 √ null

Organisation
N_SITE_CODE_DTT varchar 5 √ null
N7_2_CONSULTANT varchar 8 √ null

Consultant
N7_3_SPECIALTY int 4 √ null

Specialty - Derived
N7_4_TREATMENT_INTENT int 4 √ null
ltblTreatmentIntent.IntentID FK_tblMAIN_SURGERY_ltblTreatmentIntentR

Treatment Intent
N7_5_DECISION_DATE smalldatetime 16 √ null

Date of Decision to Operate
N7_8_ADMISSION_DATE smalldatetime 16 √ null

Date of Admission
N7_9_SURGERY_DATE smalldatetime 16 √ null

Date of Surgery
L_PROCEDURE_SITE int 4 √ null

Main Procedure (Local Codes)
L_PROCEDURE_TYPE int 4 √ null

Main Procedure (Local Codes)
L_PROCEDURE int 4 √ null

Main Procedure (Local Codes)
L_PROCEDURE_SITE2 int 4 √ null

Sub-Procedure (Local Codes)
L_PROCEDURE_TYPE2 int 4 √ null

Sub-Procedure (Local Codes)
L_PROCEDURE2 int 4 √ null

Sub-Procedure (Local Codes)
L_PROCEDURE_SITE3 int 4 √ null

Sub-Procedure (Local Codes)
L_PROCEDURE_TYPE3 int 4 √ null

Sub-Procedure (Local Codes)
L_PROCEDURE3 int 4 √ null

Sub-Procedure (Local Codes)
L_PROCEDURE_SITE4 int 4 √ null

Sub-Procedure (Local Codes)
L_PROCEDURE_TYPE4 int 4 √ null

Sub-Procedure (Local Codes)
L_PROCEDURE4 int 4 √ null

Sub-Procedure (Local Codes)
N7_10_PROCEDURE_1 varchar 5 √ null

Main Procedure (OPCS)
N7_11_PROCEDURE_2 varchar 5 √ null

Sub-Procedure (OPCS)
N7_11_PROCEDURE_3 varchar 5 √ null

Sub-Procedure (OPCS)
N7_11_PROCEDURE_4 varchar 5 √ null

Sub-Procedure (OPCS)
N7_12_DISCHARGE_DATE smalldatetime 16 √ null

Date of Discharge
N7_13_DISCHARGE_DESTINATION int 4 √ null

Discharge Destination
N_TREATMENT_EVENT varchar 2 √ null
N_TREATMENT_SETTING varchar 2 √ null
L_TRIAL int 4 √ null
L_SURGEON varchar 8 √ null

Surgeon
L_SURGEON_NAME varchar 50 √ null

Surgeon Name
L_GRADE varchar 50 √ null

Grade of Surgeon
L_ASSISTANT_1 varchar 8 √ null

Second Surgeon/Assistant
L_ASSISTANT_NAME_1 varchar 50 √ null

Second Surgeon/Assistant Name
L_GRADE_1 varchar 50 √ null

Grade of Second Surgeon/Assistant
L_ASSISTANT_2 varchar 8 √ null

Assistant
L_ASSISTANT_NAME_2 varchar 50 √ null

Assistant Name
L_GRADE_2 varchar 50 √ null

Grade of Assistant
L_ANAESTHETIST_NAME varchar 50 √ null

Name of Anaesthetist
L_ANAESTHETIST varchar 50 √ null

Grade of Anaesthetist
L_ANAESTHETIST_NAME_2 varchar 50 √ null

Name of Second Anaesthetist
L_ANAESTHETIST_2 varchar 50 √ null

Grade of Second Anaesthetist
L_URGENCY varchar 50 √ null

Urgency of Surgery
L_ANASTOMOSIS varchar 50 √ null

Anastomosis - not used
L_BLOOD_LOSS real 4 √ null

Blood Loss
L_SUPPORT varchar 50 √ null

Support - not used
L_COMPLICATIONS int 4 √ null

Complications
L_COMPLICATIONS2 int 4 √ null

Complications
L_COMPLICATIONS3 int 4 √ null

Complications
L_OTHER_COMP varchar 255 √ null

Other Complications
L_POST_COMP varchar 255 √ null

Post-op Complications
L_TRANSFER varchar 20 √ null

Transferred To
L_DAYS int 4 √ null

No. of Days
L_RETURN_ITU varchar 50 √ null

Patient Return to ITU
L_REASON_RETURN varchar 255 √ null

Reason for Return
L_DAYS_BACK int 4 √ null

No. of Days back in ITU
DEFINITIVE_TREATMENT int 4 √ null

Definitive Treatment Flag (System)
CWT_PROFORMA int 4 √ null

CWT Proforma Entry Flag (System)
L_DISCHARGE_COMMENTS text 2147483647 √ null

Transfer Comments
STEM_CELL_INFUSION_SOURCE_ID int 4 √ null
STEM_CELL_INFUSION_SOURCE_ID2 int 4 √ null
STEM_CELL_INFUSION_SOURCE_ID3 int 4 √ null
STEM_CELL_INFUSION_DONOR_ID int 4 √ null
STEM_CELL_INFUSION_DONOR_ID2 int 4 √ null
STEM_CELL_INFUSION_DONOR_ID3 int 4 √ null
SURGEON_3 varchar 8 √ null
SURGEON_3_GRADE varchar 50 √ null
SURGEON_3_NAME varchar 50 √ null
SURGEON_4 varchar 8 √ null
SURGEON_4_GRADE varchar 50 √ null
SURGEON_4_NAME varchar 50 √ null
ROOT_ADMISSION_DATE_COMMENTS text 2147483647 √ null
ROOT_PERFORMED_DATE_COMMENTS text 2147483647 √ null
GMC_CODE1 varchar 8 √ null
GMC_CODE2 varchar 8 √ null
GMC_CODE3 varchar 8 √ null
GMC_CODE4 varchar 8 √ null
GMC_CODE5 varchar 8 √ null
GMC_CODE6 varchar 8 √ null
PLANNED_ITU_ADMISSION int 4 √ null
ACTION_ID int 4 √ null
PLANNED_PROCEDURE_DATE datetime 16,3 √ null
DATE_DISCREPANCY_REASON int 4 √ null
ROOT_DECISION_DATE_COMMENTS text 2147483647 √ null
SurgicalAccessID int 4 √ null
ltblSURGICAL_ACCESS.ID FK_tblMAIN_SURGERY_ltblSURGICAL_ACCESSR
CCLGGuidelineName varchar 100 √ null
TreatedToCCLGGuidelines int 4 √ null
ConditioningRegimenPrimary int 4 √ null
ConditioningRegimenSub1 int 4 √ null
ConditioningRegimenSub2 int 4 √ null
ResectionStatus int 4 √ null
ASAGrade int 4 √ null
AdjunctiveTherapyID int 4 √ null
ltblAdjunctiveTherapy.AdjunctiveTherapyID FK_tblMAIN_SURGERY_ltblAdjunctiveTherapyR
OtherTreatmentIntent varchar 255 √ null
TherapyType int 4 √ null
SNOMEDProcedure int 4 √ null
SNOMEDCTConcepts.ID FK_tblMAIN_SURGERY_SNOMEDProcedure_SNOMEDCTConceptsR
SNOMEDProcedure2 int 4 √ null
SNOMEDCTConcepts.ID FK_tblMAIN_SURGERY_SNOMEDProcedure2_SNOMEDCTConceptsR
SNOMEDProcedure3 int 4 √ null
SNOMEDCTConcepts.ID FK_tblMAIN_SURGERY_SNOMEDProcedure3_SNOMEDCTConceptsR
TertiaryReferralKey uniqueidentifier 16 √ null
StartTime char 5 √ null
EndTime char 5 √ null
RegionalAnaestheticTechnique int 4 √ null
SNOMEDRTT int 4 √ null
SNOMEDProcedure4 int 4 √ null
ROOT_PRIM_ADJUSTMENT_COMMENTS text 2147483647 √ null
ROOT_SUBPRIM_ADJUSTMENT_COMMENTS text 2147483647 √ null

Indexes

Constraint Name Type Sort Column(s)
PK_tblMAIN_SURGERY Primary key Asc SURGERY_ID
IX_tblMAIN_SURGERY Performance Asc CARE_ID
IX_tblMAIN_SURGERY_CARE_ID Performance Asc CARE_ID
UQ_tblMAIN_SURGERY_CareID_TertiaryReferralKey Must be unique Asc/Asc CARE_ID + TertiaryReferralKey

Relationships

Close relationships within degrees of separation