OpenSAFELY
Reports

OpenSAFELY-TPP database schema

Description
This report provides information about the data schema for tables in the OpenSAFELY-TPP database.
Contact
Get in touch and tell us how you use this report or new features you'd like to see: [email protected]
First published
06 Jan 2021
Last updated
08 Oct 2021
Links

Dataset schema in OpenSAFELY-TPP

This notebook displays the schema of the OpenSAFELY-TPP database. It is part of the technical documentation of the OpenSAFELY platform to help users understand the underlying data and guide analyses.

The schema information is read from the OpenSAFELYSchemaInformation table, which is refreshed at the same time as the core S1 SystemOne tables. There are some non-automated steps required to update the schema information when a new table is added to the database — if you can't see a table that you are expecting to see, speak to TPP.

If you would like to apply to use the OpenSAFELY platform please read our documentation, the principles of the platform, and information about our pilot programme for onboarding external users.

If you want to see the Python code used to create this notebook, you can view it on GitHub.

Data sources

The core SystmOne primary care datasets are held in the S1 tables in the OpenSAFELY-TPP database. Other externally-linked data sources are listed below, with the table name given in brackets:

  • All positive or negative SARS-CoV2 tests, from SGSS (SGSS_AllTests_Positive and SGSS_AllTests_Negative)
  • First-ever positive or negative SARS-CoV2 test, from SGSS (SGSS_Positive and SGSS_Negative)
  • A&E attendances, from SUS Emergency Care data (EC)
  • In-patient hospital admissions, from SUS Admitted Patient Care Spells data (APCS)
  • Out-patient hospital appointments, from SUS (OPA)
  • Covid-related ICU admissions, from ICNARC (ICNARC)
  • Covid-related in-hospital deaths, from CPNS (CPNS)
  • COVID-19 Infection Survey, from ONS (ONS_CIS)
  • All-cause registered deaths, from ONS (ONS_Deaths)
  • High cost drugs (HighCostDrugs)
  • Unique Property Reference Number, used for deriving household variables (UPRN)
  • Master Patient Index (MPI)
  • Health and Social Care Worker identification, collected at the point of vaccination (HealthCareWorker)

Some of these tables are accompanied by additional tables with further data. For instance, OPA contains the core out-patient appointment event data, and is supplemented by the OPA_Cost, OPA_Diag, OPA_Proc tables. See the data schema notebook for more information.

Notebook run date

This notebook was run on 2021-10-08. The information below reflects the state of the OpenSAFELY-TPP as at this date.

Table names by source

The table below lists all the data tables available in the OpenSAFELY-TPP database and where the data originate from.

DataSource TableName
0 ICNARC ICNARC
1 NHSD_HIGH_COST_DRUGS HighCostDrugs
2 NHSE_APCS APCS
3 NHSE_APCS APCS_Cost
4 NHSE_APCS APCS_Der
5 NHSE_CPNS CPNS
6 NHSE_EC EC
7 NHSE_EC EC_AlcoholDrugInvolvement
8 NHSE_EC EC_Comorbidities
9 NHSE_EC EC_Cost
10 NHSE_EC EC_Diagnosis
11 NHSE_EC EC_Investigation
12 NHSE_EC EC_PatientMentalHealth
13 NHSE_EC EC_Treatment
14 NHSE_ECDS ECDS
15 NHSE_ECDS ECDS_EC_Diagnoses
16 NHSE_HEALTH_CARE_WORKER HealthCareWorker
17 NHSE_MPI MPI
18 NHSE_MPI UPRN
19 NHSE_OPA OPA
20 NHSE_OPA OPA_Cost
21 NHSE_OPA OPA_Diag
22 NHSE_OPA OPA_Proc
23 NHSE_SGSS SGSS_AllTests_Negative
24 NHSE_SGSS SGSS_AllTests_Positive
25 NHSE_SGSS SGSS_Negative
26 NHSE_SGSS SGSS_Positive
27 ONS_CIS ONS_CIS
28 ONS_DEATHS ONS_Deaths
29 OS_BUILD BuildInfo
30 OS_BUILD CodeCountIndicator
31 OS_BUILD LatestBuildTime
32 OS_BUILD OpenSAFELYSchemaInformation
33 OS_DERIVED Household
34 OS_DERIVED HouseholdMember
35 OS_DERIVED MSOA_PopulationEstimates_2019
36 OS_DERIVED PotentialCareHomeAddress
37 S1 Appointment
38 S1 CTV3Dictionary
39 S1 CTV3Hierarchy
40 S1 CodedEvent
41 S1 CodedEventRange
42 S1 Consultation
43 S1 DataDictionary
44 S1 ICD10Dictionary
45 S1 MedicationDictionary
46 S1 MedicationIssue
47 S1 MedicationRepeat
48 S1 MedicationSensitivity
49 S1 Organisation
50 S1 Patient
51 S1 PatientAddress
52 S1 QOFClusterReference
53 S1 RegistrationHistory
54 S1 UnitDictionary
55 S1 Vaccination
56 S1 VaccinationReference
57 S1 YCodeToSnomedMapping

Table Schema

The schema for each table contains the following info:

The schema for each table is printed below.

ICNARC

ICNARC

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
ICNARC_ID bigint 8 19 0 True
CalculatedAge int 4 10 0 True
EstimatedAge int 4 10 0 True
Sex varchar 10 0 0 True
OriginalHospitalAdmissionDate datetime 8 23 3 True
HospitalAdmissionDate datetime 8 23 3 True
IcuAdmissionDateTime datetime 8 23 3 True
TransferredIn varchar 10 0 0 True
OriginalIcuAdmissionDate datetime 8 23 3 True
HighestLevelFirst24Hours int 4 10 0 True
Ventilator int 4 10 0 True
AP2score int 4 10 0 True
IMscore int 4 10 0 True
pfratio real 4 24 0 True
BasicDays_RespiratorySupport int 4 10 0 True
AdvancedDays_RespiratorySupport int 4 10 0 True
BasicDays_CardiovascularSupport int 4 10 0 True
AdvancedDays_CardiovascularSupport int 4 10 0 True
SupportDays_Renal int 4 10 0 True
SupportDays_Neurological int 4 10 0 True
SupportDays_Gastrointestinal int 4 10 0 True
SupportDays_Dermatological int 4 10 0 True
SupportDays_Liver int 4 10 0 True
Level3days int 4 10 0 True
Level2days int 4 10 0 True
Level1days int 4 10 0 True
Level0days int 4 10 0 True
HRG varchar 50 0 0 True
yusurv int 4 10 0 True
IcuDischargeDateTime datetime 8 23 3 True
TransferredOut varchar 10 0 0 True
ausurv int 4 10 0 True
UltimateIcuDischargeDate datetime 8 23 3 True
yhsurv int 4 10 0 True
HospitalDischargeDate datetime 8 23 3 True
ahsurv int 4 10 0 True
UltimateHospitalDischargeDate datetime 8 23 3 True
DateOfDeath datetime 8 23 3 True

NHSD_HIGH_COST_DRUGS

HighCostDrugs

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
FinancialMonth varchar 2 0 0 True
FinancialYear varchar 6 0 0 True
PersonAge int 4 10 0 True
PersonGender int 4 10 0 True
ActivityTreatmentFunctionCode varchar 100 0 0 True
TherapeuticIndicationCode varchar 1000 0 0 True
HighCostTariffExcludedDrugCode varchar 100 0 0 True
DrugName varchar 1000 0 0 True
RouteOfAdministration varchar 100 0 0 True
DrugStrength varchar 1000 0 0 True
DrugVolume varchar 1000 0 0 True
DrugPackSize varchar 1000 0 0 True
DrugQuanitityOrWeightProportion varchar 1000 0 0 True
UnitOfMeasurement varchar 100 0 0 True
DispensingRoute varchar 100 0 0 True
HomeDeliveryCharge varchar 100 0 0 True
TotalCost varchar 100 0 0 True
DerivedSNOMEDFromName varchar 1000 0 0 True
DerivedVTM varchar 1000 0 0 True
DerivedVTMName varchar 1000 0 0 True

NHSE_APCS

APCS

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
APCS_Ident bigint 8 19 0 False
Carer_Support_Indicator varchar 1000 0 0 True
Ethnic_Group varchar 2 0 0 True
Administrative_Category varchar 2 0 0 True
Patient_Classification varchar 2 0 0 True
Admission_Method varchar 2 0 0 True
Discharge_Destination varchar 2 0 0 True
Discharge_Method varchar 2 0 0 True
Source_of_Admission varchar 2 0 0 True
Admission_Date date 3 10 0 True
Discharge_Date date 3 10 0 True
Provider_Org_Code_Type varchar 5 0 0 True
Duration_of_Elective_Wait int 4 10 0 True
Spell_Core_HRG_SUS varchar 10 0 0 True
Spell_HRG_Version_No_SUS varchar 20 0 0 True
Hospital_Spell_Duration int 4 10 0 True
Der_Spell_LoS int 4 10 0 True
Der_Diagnosis_Count int 4 10 0 True
Der_Procedure_Count int 4 10 0 True
Der_Diagnosis_All varchar 4000 0 0 True
Der_Procedure_All varchar 4000 0 0 True
Der_Admit_Treatment_Function_Code varchar 3 0 0 True
Der_Dischg_Treatment_Function_Code varchar 3 0 0 True
Der_Pseudo_Patient_Pathway_ID bigint 8 19 0 True
Der_Activity_Month varchar 6 0 0 True
Der_Financial_Year varchar 7 0 0 True

APCS_Cost

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
APCS_Ident bigint 8 19 0 False
Tariff_Initial_Amount real 4 24 0 True
Tariff_Total_Payment real 4 24 0 True
Grand_Total_Payment_MFF real 4 24 0 True

APCS_Der

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
APCS_Ident bigint 8 19 0 False
Spell_Dominant_Procedure varchar 100 0 0 True
Spell_Primary_Diagnosis varchar 5 0 0 True
Spell_Secondary_Diagnosis varchar 5 0 0 True
Spell_Treatment_Function_Code varchar 3 0 0 True
Spell_Main_Specialty_Code varchar 3 0 0 True
Spell_LoS varchar 5 0 0 True
Spell_PbR_CC_Day varchar 4 0 0 True
Spell_PbR_Rehab_Days varchar 4 0 0 True
Spell_RE30_Indicator varchar 1 0 0 True
Spell_RE30_Admit_Type varchar 1 0 0 True

NHSE_CPNS

CPNS

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Id bigint 8 19 0 False
LocationOfDeath varchar 1000 0 0 True
Sex varchar 5 0 0 True
DateOfAdmission date 3 10 0 True
DateOfSwabbed date 3 10 0 True
DateOfResult date 3 10 0 True
RelativesAware varchar 100 0 0 True
TravelHistory varchar 10 0 0 True
RegionCode varchar 10 0 0 True
RegionName varchar 100 0 0 True
OrganisationTypeLot varchar 100 0 0 True
RegionApproved varchar 10 0 0 True
RegionalApprovedDate date 3 10 0 True
NationalApproved varchar 10 0 0 True
NationalApprovedDate date 3 10 0 True
PreExistingCondition varchar 10 0 0 True
Age int 4 10 0 True
DateOfDeath date 3 10 0 True
HadLearningDisability varchar 10 0 0 True
ReceivedTreatmentForMentalHealth varchar 100 0 0 True
Der_Ethnic_Category_Description varchar 100 0 0 True
Der_Latest_SUS_Attendance_Date_For_Ethnicity varchar 100 0 0 True
Der_Source_Dataset_For_Ethnicty varchar 100 0 0 True
snapDate date 3 10 0 True
OnDeathCertificateNotice bit 1 1 0 True
CovidTestResult varchar 100 0 0 True
NHSworker bit 1 1 0 True
PreExistingConditionList varchar 4000 0 0 True
LearningDisabilityType varchar 100 0 0 True
TransferredFromLearningDisabilityAutismSetting bit 1 1 0 True
TransferredFromAMentalHealthSetting bit 1 1 0 True
DetainedUnderMHAct bit 1 1 0 True

NHSE_EC

EC

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
Ethnic_Category varchar 1 0 0 True
EC_Department_Type varchar 2 0 0 True
Arrival_Date date 3 10 0 True
Arrival_Time time 5 16 7 True
EC_Arrival_Mode_SNOMED_CT varchar 20 0 0 True
EC_AttendanceCategory varchar 1 0 0 True
EC_Attendance_Source_SNOMED_CT varchar 20 0 0 True
EC_Decision_To_Admit_Date date 3 10 0 True
Decision_To_Admit_Treatment_Function_Code varchar 3 0 0 True
Discharge_Destination_SNOMED_CT varchar 20 0 0 True
EC_Chief_Complaint_SNOMED_CT varchar 20 0 0 True
EC_Injury_Date date 3 10 0 True
SUS_HRG_Code varchar 5 0 0 True
SUS_Tariff varchar 5 0 0 True
SUS_Final_Price varchar 5 0 0 True
DQ_Chief_Complaint_Expected varchar 5 0 0 True
DQ_Chief_Complaint_Completed varchar 5 0 0 True
DQ_Chief_Complaint_Valid varchar 5 0 0 True
DQ_Primary_Diagnosis_Expected varchar 5 0 0 True
DQ_Primary_Diagnosis_Completed varchar 5 0 0 True
DQ_Primary_Diagnosis_Valid varchar 5 0 0 True
Der_EC_Diagnosis_All varchar 4000 0 0 True
Der_EC_Investigation_All varchar 4000 0 0 True
Der_EC_Treatment_All varchar 4000 0 0 True
Der_Activity_Month varchar 6 0 0 True
Der_Financial_Year varchar 7 0 0 True

EC_AlcoholDrugInvolvement

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
EC_Alcohol_Drug_Involvement_01 varchar 20 0 0 True
Is_Code_Approved_01 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_02 varchar 20 0 0 True
Is_Code_Approved_02 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_03 varchar 20 0 0 True
Is_Code_Approved_03 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_04 varchar 20 0 0 True
Is_Code_Approved_04 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_05 varchar 20 0 0 True
Is_Code_Approved_05 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_06 varchar 20 0 0 True
Is_Code_Approved_06 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_07 varchar 20 0 0 True
Is_Code_Approved_07 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_08 varchar 20 0 0 True
Is_Code_Approved_08 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_09 varchar 20 0 0 True
Is_Code_Approved_09 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_10 varchar 20 0 0 True
Is_Code_Approved_10 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_11 varchar 20 0 0 True
Is_Code_Approved_11 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_12 varchar 20 0 0 True
Is_Code_Approved_12 varchar 5 0 0 True

EC_Comorbidities

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
Comorbidity_01 varchar 20 0 0 True
Comorbidity_02 varchar 20 0 0 True
Comorbidity_03 varchar 20 0 0 True
Comorbidity_04 varchar 20 0 0 True
Comorbidity_05 varchar 20 0 0 True
Comorbidity_06 varchar 20 0 0 True
Comorbidity_07 varchar 20 0 0 True
Comorbidity_08 varchar 20 0 0 True
Comorbidity_09 varchar 20 0 0 True
Comorbidity_10 varchar 20 0 0 True
Comorbidity_11 varchar 20 0 0 True
Comorbidity_12 varchar 20 0 0 True
Comorbidity_13 varchar 20 0 0 True
Comorbidity_14 varchar 20 0 0 True
Comorbidity_15 varchar 20 0 0 True
Comorbidity_16 varchar 20 0 0 True
Comorbidity_17 varchar 20 0 0 True
Comorbidity_18 varchar 20 0 0 True
Comorbidity_19 varchar 20 0 0 True
Comorbidity_20 varchar 20 0 0 True
Comorbidity_21 varchar 20 0 0 True
Comorbidity_22 varchar 20 0 0 True
Comorbidity_23 varchar 20 0 0 True
Comorbidity_24 varchar 20 0 0 True

EC_Cost

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
Tariff_Total_Payment real 4 24 0 True
Grand_Total_Payment_MFF real 4 24 0 True

EC_Diagnosis

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
EC_Chief_Complaint_SNOMED_CT varchar 20 0 0 True
EC_Diagnosis_01 varchar 20 0 0 True
EC_Diagnosis_02 varchar 20 0 0 True
EC_Diagnosis_03 varchar 20 0 0 True
EC_Diagnosis_04 varchar 20 0 0 True
EC_Diagnosis_05 varchar 20 0 0 True
EC_Diagnosis_06 varchar 20 0 0 True
EC_Diagnosis_07 varchar 20 0 0 True
EC_Diagnosis_08 varchar 20 0 0 True
EC_Diagnosis_09 varchar 20 0 0 True
EC_Diagnosis_10 varchar 20 0 0 True
EC_Diagnosis_11 varchar 20 0 0 True
EC_Diagnosis_12 varchar 20 0 0 True
EC_Diagnosis_13 varchar 20 0 0 True
EC_Diagnosis_14 varchar 20 0 0 True
EC_Diagnosis_15 varchar 20 0 0 True
EC_Diagnosis_16 varchar 20 0 0 True
EC_Diagnosis_17 varchar 20 0 0 True
EC_Diagnosis_18 varchar 20 0 0 True
EC_Diagnosis_19 varchar 20 0 0 True
EC_Diagnosis_20 varchar 20 0 0 True
EC_Diagnosis_21 varchar 20 0 0 True
EC_Diagnosis_22 varchar 20 0 0 True
EC_Diagnosis_23 varchar 20 0 0 True
EC_Diagnosis_24 varchar 20 0 0 True
AEA_Diagnosis_01 varchar 100 0 0 True
AEA_Diagnosis_02 varchar 100 0 0 True
AEA_Diagnosis_03 varchar 100 0 0 True
AEA_Diagnosis_04 varchar 100 0 0 True
AEA_Diagnosis_05 varchar 100 0 0 True
AEA_Diagnosis_06 varchar 100 0 0 True
AEA_Diagnosis_07 varchar 100 0 0 True
AEA_Diagnosis_08 varchar 100 0 0 True
AEA_Diagnosis_09 varchar 100 0 0 True
AEA_Diagnosis_10 varchar 100 0 0 True
AEA_Diagnosis_11 varchar 100 0 0 True
AEA_Diagnosis_12 varchar 100 0 0 True
AEA_Diagnosis_13 varchar 100 0 0 True
AEA_Diagnosis_14 varchar 100 0 0 True
AEA_Diagnosis_15 varchar 100 0 0 True
AEA_Diagnosis_16 varchar 100 0 0 True
AEA_Diagnosis_17 varchar 100 0 0 True
AEA_Diagnosis_18 varchar 100 0 0 True
AEA_Diagnosis_19 varchar 100 0 0 True
AEA_Diagnosis_20 varchar 100 0 0 True
AEA_Diagnosis_21 varchar 100 0 0 True
AEA_Diagnosis_22 varchar 100 0 0 True
AEA_Diagnosis_23 varchar 100 0 0 True
AEA_Diagnosis_24 varchar 100 0 0 True

EC_Investigation

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
EC_Investigation_01 varchar 20 0 0 True
EC_Investigation_02 varchar 20 0 0 True
EC_Investigation_03 varchar 20 0 0 True
EC_Investigation_04 varchar 20 0 0 True
EC_Investigation_05 varchar 20 0 0 True
EC_Investigation_06 varchar 20 0 0 True
EC_Investigation_07 varchar 20 0 0 True
EC_Investigation_08 varchar 20 0 0 True
EC_Investigation_09 varchar 20 0 0 True
EC_Investigation_10 varchar 20 0 0 True
EC_Investigation_11 varchar 20 0 0 True
EC_Investigation_12 varchar 20 0 0 True
EC_Investigation_13 varchar 20 0 0 True
EC_Investigation_14 varchar 20 0 0 True
EC_Investigation_15 varchar 20 0 0 True
EC_Investigation_16 varchar 20 0 0 True
EC_Investigation_17 varchar 20 0 0 True
EC_Investigation_18 varchar 20 0 0 True
EC_Investigation_19 varchar 20 0 0 True
EC_Investigation_20 varchar 20 0 0 True
EC_Investigation_21 varchar 20 0 0 True
EC_Investigation_22 varchar 20 0 0 True
EC_Investigation_23 varchar 20 0 0 True
EC_Investigation_24 varchar 20 0 0 True
AEA_Investigation_01 varchar 20 0 0 True
AEA_Investigation_02 varchar 20 0 0 True
AEA_Investigation_03 varchar 20 0 0 True
AEA_Investigation_04 varchar 20 0 0 True
AEA_Investigation_05 varchar 20 0 0 True
AEA_Investigation_06 varchar 20 0 0 True
AEA_Investigation_07 varchar 20 0 0 True
AEA_Investigation_08 varchar 20 0 0 True
AEA_Investigation_09 varchar 20 0 0 True
AEA_Investigation_10 varchar 20 0 0 True
AEA_Investigation_11 varchar 20 0 0 True
AEA_Investigation_12 varchar 20 0 0 True
AEA_Investigation_13 varchar 20 0 0 True
AEA_Investigation_14 varchar 20 0 0 True
AEA_Investigation_15 varchar 20 0 0 True
AEA_Investigation_16 varchar 20 0 0 True
AEA_Investigation_17 varchar 20 0 0 True
AEA_Investigation_18 varchar 20 0 0 True
AEA_Investigation_19 varchar 20 0 0 True
AEA_Investigation_20 varchar 20 0 0 True
AEA_Investigation_21 varchar 20 0 0 True
AEA_Investigation_22 varchar 20 0 0 True
AEA_Investigation_23 varchar 20 0 0 True
AEA_Investigation_24 varchar 20 0 0 True

EC_PatientMentalHealth

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
MH_Classification_01 varchar 20 0 0 True
MH_Start_Date_01 date 3 10 0 True
MH_Expiry_Date_01 date 3 10 0 True
... ... ... ... ... ...
MH_Start_Date_23 date 3 10 0 True
MH_Expiry_Date_23 date 3 10 0 True
MH_Classification_24 varchar 20 0 0 True
MH_Start_Date_24 date 3 10 0 True
MH_Expiry_Date_24 date 3 10 0 True

74 rows × 5 columns

EC_Treatment

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
EC_Treatment_01 varchar 20 0 0 True
EC_Treatment_02 varchar 20 0 0 True
EC_Treatment_03 varchar 20 0 0 True
EC_Treatment_04 varchar 20 0 0 True
EC_Treatment_05 varchar 20 0 0 True
EC_Treatment_06 varchar 20 0 0 True
EC_Treatment_07 varchar 20 0 0 True
EC_Treatment_08 varchar 20 0 0 True
EC_Treatment_09 varchar 20 0 0 True
EC_Treatment_10 varchar 20 0 0 True
EC_Treatment_11 varchar 20 0 0 True
EC_Treatment_12 varchar 20 0 0 True
EC_Treatment_13 varchar 20 0 0 True
EC_Treatment_14 varchar 20 0 0 True
EC_Treatment_15 varchar 20 0 0 True
EC_Treatment_16 varchar 20 0 0 True
EC_Treatment_17 varchar 20 0 0 True
EC_Treatment_18 varchar 20 0 0 True
EC_Treatment_19 varchar 20 0 0 True
EC_Treatment_20 varchar 20 0 0 True
EC_Treatment_21 varchar 20 0 0 True
EC_Treatment_22 varchar 20 0 0 True
EC_Treatment_23 varchar 20 0 0 True
EC_Treatment_24 varchar 20 0 0 True
AEA_Treatment_01 varchar 20 0 0 True
AEA_Treatment_02 varchar 20 0 0 True
AEA_Treatment_03 varchar 20 0 0 True
AEA_Treatment_04 varchar 20 0 0 True
AEA_Treatment_05 varchar 20 0 0 True
AEA_Treatment_06 varchar 20 0 0 True
AEA_Treatment_07 varchar 20 0 0 True
AEA_Treatment_08 varchar 20 0 0 True
AEA_Treatment_09 varchar 20 0 0 True
AEA_Treatment_10 varchar 20 0 0 True
AEA_Treatment_11 varchar 20 0 0 True
AEA_Treatment_12 varchar 20 0 0 True
AEA_Treatment_13 varchar 20 0 0 True
AEA_Treatment_14 varchar 20 0 0 True
AEA_Treatment_15 varchar 20 0 0 True
AEA_Treatment_16 varchar 20 0 0 True
AEA_Treatment_17 varchar 20 0 0 True
AEA_Treatment_18 varchar 20 0 0 True
AEA_Treatment_19 varchar 20 0 0 True
AEA_Treatment_20 varchar 20 0 0 True
AEA_Treatment_21 varchar 20 0 0 True
AEA_Treatment_22 varchar 20 0 0 True
AEA_Treatment_23 varchar 20 0 0 True
AEA_Treatment_24 varchar 20 0 0 True

NHSE_ECDS

ECDS

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 True
EC_PCD_Indicator int 4 10 0 True
CDS_Type varchar 3 0 0 True
CDS_Group_Indicator bigint 8 19 0 True
... ... ... ... ... ...
Der_EC_Departure_Date_Time datetime 8 23 3 True
Der_EC_Duration int 4 10 0 True
Der_Dupe_Flag int 4 10 0 True
Der_Record_Type varchar 4 0 0 True
Der_AEA_Patient_Group varchar 2 0 0 True

143 rows × 5 columns

ECDS_EC_Diagnoses

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 True
Ordinal int 4 10 0 False
DiagnosisCode varchar 50 0 0 True

NHSE_HEALTH_CARE_WORKER

HealthCareWorker

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
HealthCareWorker varchar 10 0 0 False

NHSE_MPI

MPI

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Gender varchar 1 0 0 True
Birth_Month varchar 10 0 0 True
Death_Month varchar 10 0 0 True
RP_of_Death varchar 10 0 0 True
DateFrom date 3 10 0 True
DateTo date 3 10 0 True
Date_Added date 3 10 0 True
Original_Posting_Date date 3 10 0 True
Data_Source varchar 100 0 0 True
Latest_Flag varchar 10 0 0 True
Care_Home_Flag varchar 10 0 0 True
ServiceType varchar 100 0 0 True
Living_Alone_Flag varchar 100 0 0 True
Living_with_young_Flag varchar 100 0 0 True
Living_with_elderly_Flag varchar 100 0 0 True
OS_Property_Classification varchar 100 0 0 True
Rural_Urban_Classification varchar 100 0 0 True
property_type varchar 100 0 0 True
private_outdoor_space varchar 100 0 0 True
private_outdoor_space_area varchar 100 0 0 True
Pseudo_uprn varchar 200 0 0 True
Pseudo_parent_uprn varchar 200 0 0 True

UPRN

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Pseudo_uprn varchar 200 0 0 True
Pseudo_parent_uprn varchar 200 0 0 True
class varchar 100 0 0 True
Total_Pop int 4 10 0 True
_0to4 int 4 10 0 True
_5to9 int 4 10 0 True
_10to14 int 4 10 0 True
_15to19 int 4 10 0 True
_20to24 int 4 10 0 True
_25to29 int 4 10 0 True
_30to34 int 4 10 0 True
_40to44 int 4 10 0 True
_45to49 int 4 10 0 True
_50to54 int 4 10 0 True
_55to59 int 4 10 0 True
_60to64 int 4 10 0 True
_65to69 int 4 10 0 True
_70to74 int 4 10 0 True
_75to79 int 4 10 0 True
_80to84 int 4 10 0 True
_85Plus int 4 10 0 True
Care_Home_Flag varchar 100 0 0 True
ServiceType varchar 100 0 0 True
Rural_Urban_Classification varchar 100 0 0 True
property_type varchar 100 0 0 True
private_outdoor_space varchar 100 0 0 True
private_outdoor_space_area varchar 100 0 0 True

NHSE_OPA

OPA

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
OPA_Ident bigint 8 19 0 False
Ethnic_Category varchar 2 0 0 True
Main_Specialty_Code varchar 3 0 0 True
Treatment_Function_Code varchar 3 0 0 True
MultiProf_Ind_Code varchar 2 0 0 True
Administrative_Category varchar 2 0 0 True
Attendance_Status varchar 2 0 0 True
First_Attendance varchar 2 0 0 True
Medical_Staff_Type_Seeing_Patient varchar 2 0 0 True
Operation_Status varchar 2 0 0 True
Outcome_of_Attendance varchar 2 0 0 True
Appointment_Date date 3 10 0 True
Consultation_Medium_Used varchar 2 0 0 True
Activity_Location_Type_Code varchar 5 0 0 True
Clinic_Code varchar 100 0 0 True
Provider_Code varchar 100 0 0 True
Provider_Code_Type varchar 100 0 0 True
Priority_Type varchar 2 0 0 True
OPA_Referral_Source varchar 2 0 0 True
Referral_Request_Received_Date date 3 10 0 True
HRG_Code varchar 10 0 0 True
HRG_Version_No varchar 10 0 0 True
Der_Activity_Month varchar 100 0 0 True
Der_Financial_Year varchar 100 0 0 True

OPA_Cost

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
OPA_Ident bigint 8 19 0 False
Tariff_OPP real 4 24 0 True
Tariff_Total_Payment real 4 24 0 True
Grand_Total_Payment_MFF real 4 24 0 True

OPA_Diag

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
OPA_Ident bigint 8 19 0 False
Primary_Diagnosis_Code varchar 100 0 0 True
Secondary_Diagnosis_Code_1 varchar 100 0 0 True
Primary_Diagnosis_Code_Read varchar 5 0 0 True
Secondary_Diagnosis_Code_1_Read varchar 5 0 0 True

OPA_Proc

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
OPA_Ident bigint 8 19 0 False
Primary_Procedure_Code varchar 100 0 0 True
Procedure_Code_2 varchar 100 0 0 True
Primary_Procedure_Code_Read varchar 5 0 0 True
Procedure_Code_2_Read varchar 5 0 0 True

NHSE_SGSS

SGSS_AllTests_Negative

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Organism_Species_Name varchar 200 0 0 True
Specimen_Date date 3 10 0 True
Lab_Report_Date date 3 10 0 True
Age_In_Years int 4 10 0 True
Patient_Sex varchar 50 0 0 True
County_Description varchar 50 0 0 True
PostCode_Source varchar 50 0 0 True
Lab_Type varchar 200 0 0 True
Symptomatic varchar 50 0 0 True
Ethnic_Category_Desc varchar 255 0 0 True
Pillar varchar 255 0 0 True
LFT_Flag varchar 255 0 0 True

SGSS_AllTests_Positive

ColumnType MaxLength Precision Scale IsNullable
ColumnName
VariantDetectionMethod varchar 255 0 0 True
Patient_ID bigint 8 19 0 False
Organism_Species_Name varchar 200 0 0 True
Specimen_Date date 3 10 0 True
Lab_Report_Date date 3 10 0 True
Age_In_Years int 4 10 0 True
Patient_Sex varchar 50 0 0 True
County_Description varchar 50 0 0 True
PostCode_Source varchar 50 0 0 True
Lab_Type varchar 200 0 0 True
Symptomatic varchar 50 0 0 True
Ethnic_Category_Desc varchar 255 0 0 True
Pillar varchar 255 0 0 True
LFT_Flag varchar 255 0 0 True
Variant varchar 255 0 0 True

SGSS_Negative

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Organism_Species_Name varchar 200 0 0 True
Earliest_Specimen_Date date 3 10 0 True
Lab_Report_Date date 3 10 0 True
Age_In_Years int 4 10 0 True
Patient_Sex varchar 50 0 0 True
County_Description varchar 50 0 0 True
PostCode_Source varchar 50 0 0 True
Lab_Type varchar 200 0 0 True

SGSS_Positive

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Organism_Species_Name varchar 200 0 0 True
Earliest_Specimen_Date date 3 10 0 True
Lab_Report_Date date 3 10 0 True
Age_In_Years int 4 10 0 True
Patient_Sex varchar 50 0 0 True
County_Description varchar 50 0 0 True
PostCode_Source varchar 50 0 0 True
Lab_Type varchar 200 0 0 True
SGTF varchar 10 0 0 False
CaseCategory varchar 50 0 0 False

ONS_CIS

ONS_CIS

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
visit_id varchar 100 0 0 True
dataset int 4 10 0 True
visit_date date 3 10 0 True
samples_taken_date date 3 10 0 True
... ... ... ... ... ...
patient_facing_clean int 4 10 0 True
work_status_clean int 4 10 0 True
SOC_occupation varchar 100 0 0 True
geography_name varchar 100 0 0 True
geography_code varchar 100 0 0 True

93 rows × 5 columns

ONS_DEATHS

ONS_Deaths

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
sex varchar 10 0 0 True
ageinyrs int 4 10 0 True
dod date 3 10 0 True
FIC10UND varchar 100 0 0 True
icd10u varchar 100 0 0 True
ICD10001 varchar 100 0 0 True
ICD10002 varchar 100 0 0 True
ICD10003 varchar 100 0 0 True
ICD10004 varchar 100 0 0 True
ICD10005 varchar 100 0 0 True
ICD10006 varchar 100 0 0 True
ICD10007 varchar 100 0 0 True
ICD10008 varchar 100 0 0 True
ICD10009 varchar 100 0 0 True
ICD10010 varchar 100 0 0 True
ICD10011 varchar 100 0 0 True
ICD10012 varchar 100 0 0 True
ICD10013 varchar 100 0 0 True
ICD10014 varchar 100 0 0 True
ICD10015 varchar 100 0 0 True
FIC10MEN1 varchar 100 0 0 True
FIC10MEN2 varchar 100 0 0 True
FIC10MEN3 varchar 100 0 0 True
FIC10MEN4 varchar 100 0 0 True
FIC10MEN5 varchar 100 0 0 True
FIC10MEN6 varchar 100 0 0 True
FIC10MEN7 varchar 100 0 0 True
FIC10MEN8 varchar 100 0 0 True
FIC10MEN9 varchar 100 0 0 True
FIC10MEN10 varchar 100 0 0 True
FIC10MEN11 varchar 100 0 0 True
FIC10MEN12 varchar 100 0 0 True
FIC10MEN13 varchar 100 0 0 True
FIC10MEN14 varchar 100 0 0 True
FIC10MEN15 varchar 100 0 0 True

OS_BUILD

BuildInfo

ColumnType MaxLength Precision Scale IsNullable
ColumnName
BuildDesc varchar 100 0 0 False
BuildDate datetime 8 23 3 False
BuildNumber int 4 10 0 False

CodeCountIndicator

ColumnType MaxLength Precision Scale IsNullable
ColumnName
CTV3Code varchar 50 0 0 False
CodeCountIndicator float 8 53 0 True

LatestBuildTime

ColumnType MaxLength Precision Scale IsNullable
ColumnName
DtLatestBuild datetime 8 23 3 False

OpenSAFELYSchemaInformation

ColumnType MaxLength Precision Scale IsNullable
ColumnName
TableName nvarchar 256 0 0 False
TableName sysname 256 0 0 False
ColumnId int 4 10 0 False
ColumnName nvarchar 256 0 0 True
ColumnName sysname 256 0 0 True
ColumnType nvarchar 256 0 0 False
ColumnType sysname 256 0 0 False
MaxLength smallint 2 5 0 False
Precision tinyint 1 3 0 False
Scale tinyint 1 3 0 False
CollationName nvarchar 256 0 0 True
CollationName sysname 256 0 0 True
IsNullable bit 1 1 0 True
DataSource varchar 1000 0 0 True

OS_DERIVED

Household

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Household_ID bigint 8 19 0 True
MSOA varchar 50 0 0 True
NFA_Unknown bit 1 1 0 True
CareHome bit 1 1 0 True
Prison bit 1 1 0 True
HouseholdSize int 4 10 0 True
MatchesUprnCount bit 1 1 0 True
MixedSoftwareHousehold bit 1 1 0 True
TppPercentage int 4 10 0 True

HouseholdMember

ColumnType MaxLength Precision Scale IsNullable
ColumnName
HouseholdMember_ID bigint 8 19 0 False
Patient_ID bigint 8 19 0 False
Household_ID bigint 8 19 0 False

MSOA_PopulationEstimates_2019

ColumnType MaxLength Precision Scale IsNullable
ColumnName
MSOA_Code varchar 50 0 0 False
LA_Code_2019 varchar 50 0 0 False
LA_Code_2020 varchar 50 0 0 False
Age_All int 4 10 0 False
Age_0 int 4 10 0 False
... ... ... ... ... ...
Age_86 int 4 10 0 False
Age_87 int 4 10 0 False
Age_88 int 4 10 0 False
Age_89 int 4 10 0 False
Age_90_Plus int 4 10 0 False

95 rows × 5 columns

PotentialCareHomeAddress

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
PatientAddress_ID bigint 8 19 0 False
LocationRequiresNursing varchar 5 0 0 False
LocationDoesNotRequireNursing varchar 5 0 0 False

S1

Appointment

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Appointment_ID bigint 8 19 0 False
Organisation_ID bigint 8 19 0 False
BookedDate datetime 8 23 3 False
StartDate datetime 8 23 3 False
EndDate datetime 8 23 3 False
ArrivedDate datetime 8 23 3 False
SeenDate datetime 8 23 3 False
FinishedDate datetime 8 23 3 False
Status int 4 10 0 False

CTV3Dictionary

ColumnType MaxLength Precision Scale IsNullable
ColumnName
CTV3Code varchar 50 0 0 False
Description varchar 255 0 0 False

CTV3Hierarchy

ColumnType MaxLength Precision Scale IsNullable
ColumnName
ParentCTV3Code varchar 50 0 0 False
ParentCTV3Description varchar 255 0 0 False
ChildCTV3Code varchar 50 0 0 False
ChildCTV3Description varchar 255 0 0 False
ChildToParentDistance int 4 10 0 False

CodedEvent

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Consultation_ID bigint 8 19 0 False
CodedEvent_ID bigint 8 19 0 False
CTV3Code varchar 50 0 0 False
NumericValue real 4 24 0 False
ConsultationDate datetime 8 23 3 False

CodedEventRange

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID int 4 10 0 False
CodedEvent_ID bigint 8 19 0 False
CodedEventRange_ID bigint 8 19 0 False
Consultation_ID bigint 8 19 0 False
LowerBound real 4 24 0 False
UpperBound real 4 24 0 False
Comparator tinyint 1 3 0 False

Consultation

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Consultation_ID bigint 8 19 0 False
Registration_ID bigint 8 19 0 False
ConsultationDate datetime 8 23 3 False

DataDictionary

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Table varchar 1000 0 0 True
Type varchar 255 0 0 True
Code varchar 255 0 0 True
Description varchar 1000 0 0 True

ICD10Dictionary

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Code varchar 4 0 0 False
CodeDescription varchar 500 0 0 False
ParentCode char 3 0 0 False
ParentCodeDescription varchar 500 0 0 False

MedicationDictionary

ColumnType MaxLength Precision Scale IsNullable
ColumnName
MultilexDrug_ID varchar 767 0 0 True
ProductId bigint 8 19 0 False
FullName varchar 1000 0 0 True
RootName varchar 100 0 0 True
PackDescription varchar 50 0 0 True
Form varchar 50 0 0 True
Strength varchar 500 0 0 True
CompanyName varchar 200 0 0 True
DMD_ID varchar 50 0 0 True

MedicationIssue

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Consultation_ID bigint 8 19 0 False
MedicationIssue_ID bigint 8 19 0 False
RepeatMedication_ID bigint 8 19 0 False
MultilexDrug_ID varchar 255 0 0 False
Dose varchar 255 0 0 False
Quantity varchar 255 0 0 False
StartDate datetime 8 23 3 False
EndDate datetime 8 23 3 False
MedicationStatus int 4 10 0 False
ConsultationDate datetime 8 23 3 False

MedicationRepeat

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Consultation_ID bigint 8 19 0 False
MedicationRepeat_ID bigint 8 19 0 False
MultilexDrug_ID varchar 255 0 0 False
Dose varchar 255 0 0 False
Quantity varchar 255 0 0 False
StartDate datetime 8 23 3 False
EndDate datetime 8 23 3 False
MedicationStatus int 4 10 0 False
ConsultationDate datetime 8 23 3 False

MedicationSensitivity

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID int 4 10 0 False
Consultation_ID bigint 8 19 0 False
MedicationSensitivity_ID int 4 10 0 False
MultilexDrug_ID varchar 100 0 0 False
StartDate datetime 8 23 3 False
FormulationSpecific bit 1 1 0 False
Ended bit 1 1 0 True
ConsultationDate datetime 8 23 3 False

Organisation

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Organisation_ID bigint 8 19 0 False
GoLiveDate datetime 8 23 3 False
STPCode varchar 50 0 0 False
MSOACode varchar 150 0 0 False
Region varchar 255 0 0 False

Patient

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
DateOfBirth date 3 10 0 True
DateOfDeath date 3 10 0 True
Sex char 1 0 0 False

PatientAddress

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
PatientAddress_ID bigint 8 19 0 False
StartDate datetime 8 23 3 False
EndDate datetime 8 23 3 False
AddressType int 4 10 0 False
RuralUrbanClassificationCode int 4 10 0 False
ImdRankRounded int 4 10 0 False
MSOACode varchar 150 0 0 False

QOFClusterReference

ColumnType MaxLength Precision Scale IsNullable
ColumnName
ClusterType varchar 50 0 0 False
ClusterName varchar 100 0 0 False
CTV3Code varchar 50 0 0 False
Description varchar 255 0 0 False

RegistrationHistory

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Registration_ID bigint 8 19 0 False
Organisation_ID bigint 8 19 0 False
Patient_ID bigint 8 19 0 False
StartDate datetime 8 23 3 False
EndDate datetime 8 23 3 False

UnitDictionary

ColumnType MaxLength Precision Scale IsNullable
ColumnName
UnitDictionary_ID int 4 10 0 False
CTV3Code varchar 50 0 0 False
Units varchar 50 0 0 False
Minimum real 4 24 0 False
Maximum real 4 24 0 False
LowerNormalBound real 4 24 0 False
UpperNormalBound real 4 24 0 False
DecimalPlaces int 4 10 0 False

Vaccination

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Vaccination_ID bigint 8 19 0 False
VaccinationDate datetime 8 23 3 False
VaccinationName varchar 100 0 0 False
VaccinationName_ID bigint 8 19 0 False
VaccinationSchedulePart int 4 10 0 False

VaccinationReference

ColumnType MaxLength Precision Scale IsNullable
ColumnName
VaccinationName_ID int 4 10 0 False
VaccinationName varchar 100 0 0 False
VaccinationContent varchar 50 0 0 False

YCodeToSnomedMapping

ColumnType MaxLength Precision Scale IsNullable
ColumnName
YCode varchar 5 0 0 False
SctConceptId bigint 8 19 0 True

OpenSAFELY-TPP database schema

Description
This report provides information about the data schema for tables in the OpenSAFELY-TPP database.
Contact
Get in touch and tell us how you use this report or new features you'd like to see: [email protected]
First published
06 Jan 2021
Last updated
08 Oct 2021
Links

Dataset schema in OpenSAFELY-TPP

This notebook displays the schema of the OpenSAFELY-TPP database. It is part of the technical documentation of the OpenSAFELY platform to help users understand the underlying data and guide analyses.

The schema information is read from the OpenSAFELYSchemaInformation table, which is refreshed at the same time as the core S1 SystemOne tables. There are some non-automated steps required to update the schema information when a new table is added to the database — if you can't see a table that you are expecting to see, speak to TPP.

If you would like to apply to use the OpenSAFELY platform please read our documentation, the principles of the platform, and information about our pilot programme for onboarding external users.

If you want to see the Python code used to create this notebook, you can view it on GitHub.

Data sources

The core SystmOne primary care datasets are held in the S1 tables in the OpenSAFELY-TPP database. Other externally-linked data sources are listed below, with the table name given in brackets:

  • All positive or negative SARS-CoV2 tests, from SGSS (SGSS_AllTests_Positive and SGSS_AllTests_Negative)
  • First-ever positive or negative SARS-CoV2 test, from SGSS (SGSS_Positive and SGSS_Negative)
  • A&E attendances, from SUS Emergency Care data (EC)
  • In-patient hospital admissions, from SUS Admitted Patient Care Spells data (APCS)
  • Out-patient hospital appointments, from SUS (OPA)
  • Covid-related ICU admissions, from ICNARC (ICNARC)
  • Covid-related in-hospital deaths, from CPNS (CPNS)
  • COVID-19 Infection Survey, from ONS (ONS_CIS)
  • All-cause registered deaths, from ONS (ONS_Deaths)
  • High cost drugs (HighCostDrugs)
  • Unique Property Reference Number, used for deriving household variables (UPRN)
  • Master Patient Index (MPI)
  • Health and Social Care Worker identification, collected at the point of vaccination (HealthCareWorker)

Some of these tables are accompanied by additional tables with further data. For instance, OPA contains the core out-patient appointment event data, and is supplemented by the OPA_Cost, OPA_Diag, OPA_Proc tables. See the data schema notebook for more information.

Notebook run date

This notebook was run on 2021-10-08. The information below reflects the state of the OpenSAFELY-TPP as at this date.

Table names by source

The table below lists all the data tables available in the OpenSAFELY-TPP database and where the data originate from.

DataSource TableName
0 ICNARC ICNARC
1 NHSD_HIGH_COST_DRUGS HighCostDrugs
2 NHSE_APCS APCS
3 NHSE_APCS APCS_Cost
4 NHSE_APCS APCS_Der
5 NHSE_CPNS CPNS
6 NHSE_EC EC
7 NHSE_EC EC_AlcoholDrugInvolvement
8 NHSE_EC EC_Comorbidities
9 NHSE_EC EC_Cost
10 NHSE_EC EC_Diagnosis
11 NHSE_EC EC_Investigation
12 NHSE_EC EC_PatientMentalHealth
13 NHSE_EC EC_Treatment
14 NHSE_ECDS ECDS
15 NHSE_ECDS ECDS_EC_Diagnoses
16 NHSE_HEALTH_CARE_WORKER HealthCareWorker
17 NHSE_MPI MPI
18 NHSE_MPI UPRN
19 NHSE_OPA OPA
20 NHSE_OPA OPA_Cost
21 NHSE_OPA OPA_Diag
22 NHSE_OPA OPA_Proc
23 NHSE_SGSS SGSS_AllTests_Negative
24 NHSE_SGSS SGSS_AllTests_Positive
25 NHSE_SGSS SGSS_Negative
26 NHSE_SGSS SGSS_Positive
27 ONS_CIS ONS_CIS
28 ONS_DEATHS ONS_Deaths
29 OS_BUILD BuildInfo
30 OS_BUILD CodeCountIndicator
31 OS_BUILD LatestBuildTime
32 OS_BUILD OpenSAFELYSchemaInformation
33 OS_DERIVED Household
34 OS_DERIVED HouseholdMember
35 OS_DERIVED MSOA_PopulationEstimates_2019
36 OS_DERIVED PotentialCareHomeAddress
37 S1 Appointment
38 S1 CTV3Dictionary
39 S1 CTV3Hierarchy
40 S1 CodedEvent
41 S1 CodedEventRange
42 S1 Consultation
43 S1 DataDictionary
44 S1 ICD10Dictionary
45 S1 MedicationDictionary
46 S1 MedicationIssue
47 S1 MedicationRepeat
48 S1 MedicationSensitivity
49 S1 Organisation
50 S1 Patient
51 S1 PatientAddress
52 S1 QOFClusterReference
53 S1 RegistrationHistory
54 S1 UnitDictionary
55 S1 Vaccination
56 S1 VaccinationReference
57 S1 YCodeToSnomedMapping

Table Schema

The schema for each table contains the following info:

The schema for each table is printed below.

ICNARC

ICNARC

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
ICNARC_ID bigint 8 19 0 True
CalculatedAge int 4 10 0 True
EstimatedAge int 4 10 0 True
Sex varchar 10 0 0 True
OriginalHospitalAdmissionDate datetime 8 23 3 True
HospitalAdmissionDate datetime 8 23 3 True
IcuAdmissionDateTime datetime 8 23 3 True
TransferredIn varchar 10 0 0 True
OriginalIcuAdmissionDate datetime 8 23 3 True
HighestLevelFirst24Hours int 4 10 0 True
Ventilator int 4 10 0 True
AP2score int 4 10 0 True
IMscore int 4 10 0 True
pfratio real 4 24 0 True
BasicDays_RespiratorySupport int 4 10 0 True
AdvancedDays_RespiratorySupport int 4 10 0 True
BasicDays_CardiovascularSupport int 4 10 0 True
AdvancedDays_CardiovascularSupport int 4 10 0 True
SupportDays_Renal int 4 10 0 True
SupportDays_Neurological int 4 10 0 True
SupportDays_Gastrointestinal int 4 10 0 True
SupportDays_Dermatological int 4 10 0 True
SupportDays_Liver int 4 10 0 True
Level3days int 4 10 0 True
Level2days int 4 10 0 True
Level1days int 4 10 0 True
Level0days int 4 10 0 True
HRG varchar 50 0 0 True
yusurv int 4 10 0 True
IcuDischargeDateTime datetime 8 23 3 True
TransferredOut varchar 10 0 0 True
ausurv int 4 10 0 True
UltimateIcuDischargeDate datetime 8 23 3 True
yhsurv int 4 10 0 True
HospitalDischargeDate datetime 8 23 3 True
ahsurv int 4 10 0 True
UltimateHospitalDischargeDate datetime 8 23 3 True
DateOfDeath datetime 8 23 3 True

NHSD_HIGH_COST_DRUGS

HighCostDrugs

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
FinancialMonth varchar 2 0 0 True
FinancialYear varchar 6 0 0 True
PersonAge int 4 10 0 True
PersonGender int 4 10 0 True
ActivityTreatmentFunctionCode varchar 100 0 0 True
TherapeuticIndicationCode varchar 1000 0 0 True
HighCostTariffExcludedDrugCode varchar 100 0 0 True
DrugName varchar 1000 0 0 True
RouteOfAdministration varchar 100 0 0 True
DrugStrength varchar 1000 0 0 True
DrugVolume varchar 1000 0 0 True
DrugPackSize varchar 1000 0 0 True
DrugQuanitityOrWeightProportion varchar 1000 0 0 True
UnitOfMeasurement varchar 100 0 0 True
DispensingRoute varchar 100 0 0 True
HomeDeliveryCharge varchar 100 0 0 True
TotalCost varchar 100 0 0 True
DerivedSNOMEDFromName varchar 1000 0 0 True
DerivedVTM varchar 1000 0 0 True
DerivedVTMName varchar 1000 0 0 True

NHSE_APCS

APCS

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
APCS_Ident bigint 8 19 0 False
Carer_Support_Indicator varchar 1000 0 0 True
Ethnic_Group varchar 2 0 0 True
Administrative_Category varchar 2 0 0 True
Patient_Classification varchar 2 0 0 True
Admission_Method varchar 2 0 0 True
Discharge_Destination varchar 2 0 0 True
Discharge_Method varchar 2 0 0 True
Source_of_Admission varchar 2 0 0 True
Admission_Date date 3 10 0 True
Discharge_Date date 3 10 0 True
Provider_Org_Code_Type varchar 5 0 0 True
Duration_of_Elective_Wait int 4 10 0 True
Spell_Core_HRG_SUS varchar 10 0 0 True
Spell_HRG_Version_No_SUS varchar 20 0 0 True
Hospital_Spell_Duration int 4 10 0 True
Der_Spell_LoS int 4 10 0 True
Der_Diagnosis_Count int 4 10 0 True
Der_Procedure_Count int 4 10 0 True
Der_Diagnosis_All varchar 4000 0 0 True
Der_Procedure_All varchar 4000 0 0 True
Der_Admit_Treatment_Function_Code varchar 3 0 0 True
Der_Dischg_Treatment_Function_Code varchar 3 0 0 True
Der_Pseudo_Patient_Pathway_ID bigint 8 19 0 True
Der_Activity_Month varchar 6 0 0 True
Der_Financial_Year varchar 7 0 0 True

APCS_Cost

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
APCS_Ident bigint 8 19 0 False
Tariff_Initial_Amount real 4 24 0 True
Tariff_Total_Payment real 4 24 0 True
Grand_Total_Payment_MFF real 4 24 0 True

APCS_Der

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
APCS_Ident bigint 8 19 0 False
Spell_Dominant_Procedure varchar 100 0 0 True
Spell_Primary_Diagnosis varchar 5 0 0 True
Spell_Secondary_Diagnosis varchar 5 0 0 True
Spell_Treatment_Function_Code varchar 3 0 0 True
Spell_Main_Specialty_Code varchar 3 0 0 True
Spell_LoS varchar 5 0 0 True
Spell_PbR_CC_Day varchar 4 0 0 True
Spell_PbR_Rehab_Days varchar 4 0 0 True
Spell_RE30_Indicator varchar 1 0 0 True
Spell_RE30_Admit_Type varchar 1 0 0 True

NHSE_CPNS

CPNS

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Id bigint 8 19 0 False
LocationOfDeath varchar 1000 0 0 True
Sex varchar 5 0 0 True
DateOfAdmission date 3 10 0 True
DateOfSwabbed date 3 10 0 True
DateOfResult date 3 10 0 True
RelativesAware varchar 100 0 0 True
TravelHistory varchar 10 0 0 True
RegionCode varchar 10 0 0 True
RegionName varchar 100 0 0 True
OrganisationTypeLot varchar 100 0 0 True
RegionApproved varchar 10 0 0 True
RegionalApprovedDate date 3 10 0 True
NationalApproved varchar 10 0 0 True
NationalApprovedDate date 3 10 0 True
PreExistingCondition varchar 10 0 0 True
Age int 4 10 0 True
DateOfDeath date 3 10 0 True
HadLearningDisability varchar 10 0 0 True
ReceivedTreatmentForMentalHealth varchar 100 0 0 True
Der_Ethnic_Category_Description varchar 100 0 0 True
Der_Latest_SUS_Attendance_Date_For_Ethnicity varchar 100 0 0 True
Der_Source_Dataset_For_Ethnicty varchar 100 0 0 True
snapDate date 3 10 0 True
OnDeathCertificateNotice bit 1 1 0 True
CovidTestResult varchar 100 0 0 True
NHSworker bit 1 1 0 True
PreExistingConditionList varchar 4000 0 0 True
LearningDisabilityType varchar 100 0 0 True
TransferredFromLearningDisabilityAutismSetting bit 1 1 0 True
TransferredFromAMentalHealthSetting bit 1 1 0 True
DetainedUnderMHAct bit 1 1 0 True

NHSE_EC

EC

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
Ethnic_Category varchar 1 0 0 True
EC_Department_Type varchar 2 0 0 True
Arrival_Date date 3 10 0 True
Arrival_Time time 5 16 7 True
EC_Arrival_Mode_SNOMED_CT varchar 20 0 0 True
EC_AttendanceCategory varchar 1 0 0 True
EC_Attendance_Source_SNOMED_CT varchar 20 0 0 True
EC_Decision_To_Admit_Date date 3 10 0 True
Decision_To_Admit_Treatment_Function_Code varchar 3 0 0 True
Discharge_Destination_SNOMED_CT varchar 20 0 0 True
EC_Chief_Complaint_SNOMED_CT varchar 20 0 0 True
EC_Injury_Date date 3 10 0 True
SUS_HRG_Code varchar 5 0 0 True
SUS_Tariff varchar 5 0 0 True
SUS_Final_Price varchar 5 0 0 True
DQ_Chief_Complaint_Expected varchar 5 0 0 True
DQ_Chief_Complaint_Completed varchar 5 0 0 True
DQ_Chief_Complaint_Valid varchar 5 0 0 True
DQ_Primary_Diagnosis_Expected varchar 5 0 0 True
DQ_Primary_Diagnosis_Completed varchar 5 0 0 True
DQ_Primary_Diagnosis_Valid varchar 5 0 0 True
Der_EC_Diagnosis_All varchar 4000 0 0 True
Der_EC_Investigation_All varchar 4000 0 0 True
Der_EC_Treatment_All varchar 4000 0 0 True
Der_Activity_Month varchar 6 0 0 True
Der_Financial_Year varchar 7 0 0 True

EC_AlcoholDrugInvolvement

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
EC_Alcohol_Drug_Involvement_01 varchar 20 0 0 True
Is_Code_Approved_01 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_02 varchar 20 0 0 True
Is_Code_Approved_02 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_03 varchar 20 0 0 True
Is_Code_Approved_03 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_04 varchar 20 0 0 True
Is_Code_Approved_04 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_05 varchar 20 0 0 True
Is_Code_Approved_05 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_06 varchar 20 0 0 True
Is_Code_Approved_06 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_07 varchar 20 0 0 True
Is_Code_Approved_07 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_08 varchar 20 0 0 True
Is_Code_Approved_08 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_09 varchar 20 0 0 True
Is_Code_Approved_09 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_10 varchar 20 0 0 True
Is_Code_Approved_10 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_11 varchar 20 0 0 True
Is_Code_Approved_11 varchar 5 0 0 True
EC_Alcohol_Drug_Involvement_12 varchar 20 0 0 True
Is_Code_Approved_12 varchar 5 0 0 True

EC_Comorbidities

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
Comorbidity_01 varchar 20 0 0 True
Comorbidity_02 varchar 20 0 0 True
Comorbidity_03 varchar 20 0 0 True
Comorbidity_04 varchar 20 0 0 True
Comorbidity_05 varchar 20 0 0 True
Comorbidity_06 varchar 20 0 0 True
Comorbidity_07 varchar 20 0 0 True
Comorbidity_08 varchar 20 0 0 True
Comorbidity_09 varchar 20 0 0 True
Comorbidity_10 varchar 20 0 0 True
Comorbidity_11 varchar 20 0 0 True
Comorbidity_12 varchar 20 0 0 True
Comorbidity_13 varchar 20 0 0 True
Comorbidity_14 varchar 20 0 0 True
Comorbidity_15 varchar 20 0 0 True
Comorbidity_16 varchar 20 0 0 True
Comorbidity_17 varchar 20 0 0 True
Comorbidity_18 varchar 20 0 0 True
Comorbidity_19 varchar 20 0 0 True
Comorbidity_20 varchar 20 0 0 True
Comorbidity_21 varchar 20 0 0 True
Comorbidity_22 varchar 20 0 0 True
Comorbidity_23 varchar 20 0 0 True
Comorbidity_24 varchar 20 0 0 True

EC_Cost

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
Tariff_Total_Payment real 4 24 0 True
Grand_Total_Payment_MFF real 4 24 0 True

EC_Diagnosis

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
EC_Chief_Complaint_SNOMED_CT varchar 20 0 0 True
EC_Diagnosis_01 varchar 20 0 0 True
EC_Diagnosis_02 varchar 20 0 0 True
EC_Diagnosis_03 varchar 20 0 0 True
EC_Diagnosis_04 varchar 20 0 0 True
EC_Diagnosis_05 varchar 20 0 0 True
EC_Diagnosis_06 varchar 20 0 0 True
EC_Diagnosis_07 varchar 20 0 0 True
EC_Diagnosis_08 varchar 20 0 0 True
EC_Diagnosis_09 varchar 20 0 0 True
EC_Diagnosis_10 varchar 20 0 0 True
EC_Diagnosis_11 varchar 20 0 0 True
EC_Diagnosis_12 varchar 20 0 0 True
EC_Diagnosis_13 varchar 20 0 0 True
EC_Diagnosis_14 varchar 20 0 0 True
EC_Diagnosis_15 varchar 20 0 0 True
EC_Diagnosis_16 varchar 20 0 0 True
EC_Diagnosis_17 varchar 20 0 0 True
EC_Diagnosis_18 varchar 20 0 0 True
EC_Diagnosis_19 varchar 20 0 0 True
EC_Diagnosis_20 varchar 20 0 0 True
EC_Diagnosis_21 varchar 20 0 0 True
EC_Diagnosis_22 varchar 20 0 0 True
EC_Diagnosis_23 varchar 20 0 0 True
EC_Diagnosis_24 varchar 20 0 0 True
AEA_Diagnosis_01 varchar 100 0 0 True
AEA_Diagnosis_02 varchar 100 0 0 True
AEA_Diagnosis_03 varchar 100 0 0 True
AEA_Diagnosis_04 varchar 100 0 0 True
AEA_Diagnosis_05 varchar 100 0 0 True
AEA_Diagnosis_06 varchar 100 0 0 True
AEA_Diagnosis_07 varchar 100 0 0 True
AEA_Diagnosis_08 varchar 100 0 0 True
AEA_Diagnosis_09 varchar 100 0 0 True
AEA_Diagnosis_10 varchar 100 0 0 True
AEA_Diagnosis_11 varchar 100 0 0 True
AEA_Diagnosis_12 varchar 100 0 0 True
AEA_Diagnosis_13 varchar 100 0 0 True
AEA_Diagnosis_14 varchar 100 0 0 True
AEA_Diagnosis_15 varchar 100 0 0 True
AEA_Diagnosis_16 varchar 100 0 0 True
AEA_Diagnosis_17 varchar 100 0 0 True
AEA_Diagnosis_18 varchar 100 0 0 True
AEA_Diagnosis_19 varchar 100 0 0 True
AEA_Diagnosis_20 varchar 100 0 0 True
AEA_Diagnosis_21 varchar 100 0 0 True
AEA_Diagnosis_22 varchar 100 0 0 True
AEA_Diagnosis_23 varchar 100 0 0 True
AEA_Diagnosis_24 varchar 100 0 0 True

EC_Investigation

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
EC_Investigation_01 varchar 20 0 0 True
EC_Investigation_02 varchar 20 0 0 True
EC_Investigation_03 varchar 20 0 0 True
EC_Investigation_04 varchar 20 0 0 True
EC_Investigation_05 varchar 20 0 0 True
EC_Investigation_06 varchar 20 0 0 True
EC_Investigation_07 varchar 20 0 0 True
EC_Investigation_08 varchar 20 0 0 True
EC_Investigation_09 varchar 20 0 0 True
EC_Investigation_10 varchar 20 0 0 True
EC_Investigation_11 varchar 20 0 0 True
EC_Investigation_12 varchar 20 0 0 True
EC_Investigation_13 varchar 20 0 0 True
EC_Investigation_14 varchar 20 0 0 True
EC_Investigation_15 varchar 20 0 0 True
EC_Investigation_16 varchar 20 0 0 True
EC_Investigation_17 varchar 20 0 0 True
EC_Investigation_18 varchar 20 0 0 True
EC_Investigation_19 varchar 20 0 0 True
EC_Investigation_20 varchar 20 0 0 True
EC_Investigation_21 varchar 20 0 0 True
EC_Investigation_22 varchar 20 0 0 True
EC_Investigation_23 varchar 20 0 0 True
EC_Investigation_24 varchar 20 0 0 True
AEA_Investigation_01 varchar 20 0 0 True
AEA_Investigation_02 varchar 20 0 0 True
AEA_Investigation_03 varchar 20 0 0 True
AEA_Investigation_04 varchar 20 0 0 True
AEA_Investigation_05 varchar 20 0 0 True
AEA_Investigation_06 varchar 20 0 0 True
AEA_Investigation_07 varchar 20 0 0 True
AEA_Investigation_08 varchar 20 0 0 True
AEA_Investigation_09 varchar 20 0 0 True
AEA_Investigation_10 varchar 20 0 0 True
AEA_Investigation_11 varchar 20 0 0 True
AEA_Investigation_12 varchar 20 0 0 True
AEA_Investigation_13 varchar 20 0 0 True
AEA_Investigation_14 varchar 20 0 0 True
AEA_Investigation_15 varchar 20 0 0 True
AEA_Investigation_16 varchar 20 0 0 True
AEA_Investigation_17 varchar 20 0 0 True
AEA_Investigation_18 varchar 20 0 0 True
AEA_Investigation_19 varchar 20 0 0 True
AEA_Investigation_20 varchar 20 0 0 True
AEA_Investigation_21 varchar 20 0 0 True
AEA_Investigation_22 varchar 20 0 0 True
AEA_Investigation_23 varchar 20 0 0 True
AEA_Investigation_24 varchar 20 0 0 True

EC_PatientMentalHealth

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
MH_Classification_01 varchar 20 0 0 True
MH_Start_Date_01 date 3 10 0 True
MH_Expiry_Date_01 date 3 10 0 True
... ... ... ... ... ...
MH_Start_Date_23 date 3 10 0 True
MH_Expiry_Date_23 date 3 10 0 True
MH_Classification_24 varchar 20 0 0 True
MH_Start_Date_24 date 3 10 0 True
MH_Expiry_Date_24 date 3 10 0 True

74 rows × 5 columns

EC_Treatment

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 False
EC_Treatment_01 varchar 20 0 0 True
EC_Treatment_02 varchar 20 0 0 True
EC_Treatment_03 varchar 20 0 0 True
EC_Treatment_04 varchar 20 0 0 True
EC_Treatment_05 varchar 20 0 0 True
EC_Treatment_06 varchar 20 0 0 True
EC_Treatment_07 varchar 20 0 0 True
EC_Treatment_08 varchar 20 0 0 True
EC_Treatment_09 varchar 20 0 0 True
EC_Treatment_10 varchar 20 0 0 True
EC_Treatment_11 varchar 20 0 0 True
EC_Treatment_12 varchar 20 0 0 True
EC_Treatment_13 varchar 20 0 0 True
EC_Treatment_14 varchar 20 0 0 True
EC_Treatment_15 varchar 20 0 0 True
EC_Treatment_16 varchar 20 0 0 True
EC_Treatment_17 varchar 20 0 0 True
EC_Treatment_18 varchar 20 0 0 True
EC_Treatment_19 varchar 20 0 0 True
EC_Treatment_20 varchar 20 0 0 True
EC_Treatment_21 varchar 20 0 0 True
EC_Treatment_22 varchar 20 0 0 True
EC_Treatment_23 varchar 20 0 0 True
EC_Treatment_24 varchar 20 0 0 True
AEA_Treatment_01 varchar 20 0 0 True
AEA_Treatment_02 varchar 20 0 0 True
AEA_Treatment_03 varchar 20 0 0 True
AEA_Treatment_04 varchar 20 0 0 True
AEA_Treatment_05 varchar 20 0 0 True
AEA_Treatment_06 varchar 20 0 0 True
AEA_Treatment_07 varchar 20 0 0 True
AEA_Treatment_08 varchar 20 0 0 True
AEA_Treatment_09 varchar 20 0 0 True
AEA_Treatment_10 varchar 20 0 0 True
AEA_Treatment_11 varchar 20 0 0 True
AEA_Treatment_12 varchar 20 0 0 True
AEA_Treatment_13 varchar 20 0 0 True
AEA_Treatment_14 varchar 20 0 0 True
AEA_Treatment_15 varchar 20 0 0 True
AEA_Treatment_16 varchar 20 0 0 True
AEA_Treatment_17 varchar 20 0 0 True
AEA_Treatment_18 varchar 20 0 0 True
AEA_Treatment_19 varchar 20 0 0 True
AEA_Treatment_20 varchar 20 0 0 True
AEA_Treatment_21 varchar 20 0 0 True
AEA_Treatment_22 varchar 20 0 0 True
AEA_Treatment_23 varchar 20 0 0 True
AEA_Treatment_24 varchar 20 0 0 True

NHSE_ECDS

ECDS

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 True
EC_PCD_Indicator int 4 10 0 True
CDS_Type varchar 3 0 0 True
CDS_Group_Indicator bigint 8 19 0 True
... ... ... ... ... ...
Der_EC_Departure_Date_Time datetime 8 23 3 True
Der_EC_Duration int 4 10 0 True
Der_Dupe_Flag int 4 10 0 True
Der_Record_Type varchar 4 0 0 True
Der_AEA_Patient_Group varchar 2 0 0 True

143 rows × 5 columns

ECDS_EC_Diagnoses

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
EC_Ident bigint 8 19 0 True
Ordinal int 4 10 0 False
DiagnosisCode varchar 50 0 0 True

NHSE_HEALTH_CARE_WORKER

HealthCareWorker

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
HealthCareWorker varchar 10 0 0 False

NHSE_MPI

MPI

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Gender varchar 1 0 0 True
Birth_Month varchar 10 0 0 True
Death_Month varchar 10 0 0 True
RP_of_Death varchar 10 0 0 True
DateFrom date 3 10 0 True
DateTo date 3 10 0 True
Date_Added date 3 10 0 True
Original_Posting_Date date 3 10 0 True
Data_Source varchar 100 0 0 True
Latest_Flag varchar 10 0 0 True
Care_Home_Flag varchar 10 0 0 True
ServiceType varchar 100 0 0 True
Living_Alone_Flag varchar 100 0 0 True
Living_with_young_Flag varchar 100 0 0 True
Living_with_elderly_Flag varchar 100 0 0 True
OS_Property_Classification varchar 100 0 0 True
Rural_Urban_Classification varchar 100 0 0 True
property_type varchar 100 0 0 True
private_outdoor_space varchar 100 0 0 True
private_outdoor_space_area varchar 100 0 0 True
Pseudo_uprn varchar 200 0 0 True
Pseudo_parent_uprn varchar 200 0 0 True

UPRN

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Pseudo_uprn varchar 200 0 0 True
Pseudo_parent_uprn varchar 200 0 0 True
class varchar 100 0 0 True
Total_Pop int 4 10 0 True
_0to4 int 4 10 0 True
_5to9 int 4 10 0 True
_10to14 int 4 10 0 True
_15to19 int 4 10 0 True
_20to24 int 4 10 0 True
_25to29 int 4 10 0 True
_30to34 int 4 10 0 True
_40to44 int 4 10 0 True
_45to49 int 4 10 0 True
_50to54 int 4 10 0 True
_55to59 int 4 10 0 True
_60to64 int 4 10 0 True
_65to69 int 4 10 0 True
_70to74 int 4 10 0 True
_75to79 int 4 10 0 True
_80to84 int 4 10 0 True
_85Plus int 4 10 0 True
Care_Home_Flag varchar 100 0 0 True
ServiceType varchar 100 0 0 True
Rural_Urban_Classification varchar 100 0 0 True
property_type varchar 100 0 0 True
private_outdoor_space varchar 100 0 0 True
private_outdoor_space_area varchar 100 0 0 True

NHSE_OPA

OPA

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
OPA_Ident bigint 8 19 0 False
Ethnic_Category varchar 2 0 0 True
Main_Specialty_Code varchar 3 0 0 True
Treatment_Function_Code varchar 3 0 0 True
MultiProf_Ind_Code varchar 2 0 0 True
Administrative_Category varchar 2 0 0 True
Attendance_Status varchar 2 0 0 True
First_Attendance varchar 2 0 0 True
Medical_Staff_Type_Seeing_Patient varchar 2 0 0 True
Operation_Status varchar 2 0 0 True
Outcome_of_Attendance varchar 2 0 0 True
Appointment_Date date 3 10 0 True
Consultation_Medium_Used varchar 2 0 0 True
Activity_Location_Type_Code varchar 5 0 0 True
Clinic_Code varchar 100 0 0 True
Provider_Code varchar 100 0 0 True
Provider_Code_Type varchar 100 0 0 True
Priority_Type varchar 2 0 0 True
OPA_Referral_Source varchar 2 0 0 True
Referral_Request_Received_Date date 3 10 0 True
HRG_Code varchar 10 0 0 True
HRG_Version_No varchar 10 0 0 True
Der_Activity_Month varchar 100 0 0 True
Der_Financial_Year varchar 100 0 0 True

OPA_Cost

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
OPA_Ident bigint 8 19 0 False
Tariff_OPP real 4 24 0 True
Tariff_Total_Payment real 4 24 0 True
Grand_Total_Payment_MFF real 4 24 0 True

OPA_Diag

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
OPA_Ident bigint 8 19 0 False
Primary_Diagnosis_Code varchar 100 0 0 True
Secondary_Diagnosis_Code_1 varchar 100 0 0 True
Primary_Diagnosis_Code_Read varchar 5 0 0 True
Secondary_Diagnosis_Code_1_Read varchar 5 0 0 True

OPA_Proc

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
OPA_Ident bigint 8 19 0 False
Primary_Procedure_Code varchar 100 0 0 True
Procedure_Code_2 varchar 100 0 0 True
Primary_Procedure_Code_Read varchar 5 0 0 True
Procedure_Code_2_Read varchar 5 0 0 True

NHSE_SGSS

SGSS_AllTests_Negative

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Organism_Species_Name varchar 200 0 0 True
Specimen_Date date 3 10 0 True
Lab_Report_Date date 3 10 0 True
Age_In_Years int 4 10 0 True
Patient_Sex varchar 50 0 0 True
County_Description varchar 50 0 0 True
PostCode_Source varchar 50 0 0 True
Lab_Type varchar 200 0 0 True
Symptomatic varchar 50 0 0 True
Ethnic_Category_Desc varchar 255 0 0 True
Pillar varchar 255 0 0 True
LFT_Flag varchar 255 0 0 True

SGSS_AllTests_Positive

ColumnType MaxLength Precision Scale IsNullable
ColumnName
VariantDetectionMethod varchar 255 0 0 True
Patient_ID bigint 8 19 0 False
Organism_Species_Name varchar 200 0 0 True
Specimen_Date date 3 10 0 True
Lab_Report_Date date 3 10 0 True
Age_In_Years int 4 10 0 True
Patient_Sex varchar 50 0 0 True
County_Description varchar 50 0 0 True
PostCode_Source varchar 50 0 0 True
Lab_Type varchar 200 0 0 True
Symptomatic varchar 50 0 0 True
Ethnic_Category_Desc varchar 255 0 0 True
Pillar varchar 255 0 0 True
LFT_Flag varchar 255 0 0 True
Variant varchar 255 0 0 True

SGSS_Negative

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Organism_Species_Name varchar 200 0 0 True
Earliest_Specimen_Date date 3 10 0 True
Lab_Report_Date date 3 10 0 True
Age_In_Years int 4 10 0 True
Patient_Sex varchar 50 0 0 True
County_Description varchar 50 0 0 True
PostCode_Source varchar 50 0 0 True
Lab_Type varchar 200 0 0 True

SGSS_Positive

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Organism_Species_Name varchar 200 0 0 True
Earliest_Specimen_Date date 3 10 0 True
Lab_Report_Date date 3 10 0 True
Age_In_Years int 4 10 0 True
Patient_Sex varchar 50 0 0 True
County_Description varchar 50 0 0 True
PostCode_Source varchar 50 0 0 True
Lab_Type varchar 200 0 0 True
SGTF varchar 10 0 0 False
CaseCategory varchar 50 0 0 False

ONS_CIS

ONS_CIS

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
visit_id varchar 100 0 0 True
dataset int 4 10 0 True
visit_date date 3 10 0 True
samples_taken_date date 3 10 0 True
... ... ... ... ... ...
patient_facing_clean int 4 10 0 True
work_status_clean int 4 10 0 True
SOC_occupation varchar 100 0 0 True
geography_name varchar 100 0 0 True
geography_code varchar 100 0 0 True

93 rows × 5 columns

ONS_DEATHS

ONS_Deaths

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
sex varchar 10 0 0 True
ageinyrs int 4 10 0 True
dod date 3 10 0 True
FIC10UND varchar 100 0 0 True
icd10u varchar 100 0 0 True
ICD10001 varchar 100 0 0 True
ICD10002 varchar 100 0 0 True
ICD10003 varchar 100 0 0 True
ICD10004 varchar 100 0 0 True
ICD10005 varchar 100 0 0 True
ICD10006 varchar 100 0 0 True
ICD10007 varchar 100 0 0 True
ICD10008 varchar 100 0 0 True
ICD10009 varchar 100 0 0 True
ICD10010 varchar 100 0 0 True
ICD10011 varchar 100 0 0 True
ICD10012 varchar 100 0 0 True
ICD10013 varchar 100 0 0 True
ICD10014 varchar 100 0 0 True
ICD10015 varchar 100 0 0 True
FIC10MEN1 varchar 100 0 0 True
FIC10MEN2 varchar 100 0 0 True
FIC10MEN3 varchar 100 0 0 True
FIC10MEN4 varchar 100 0 0 True
FIC10MEN5 varchar 100 0 0 True
FIC10MEN6 varchar 100 0 0 True
FIC10MEN7 varchar 100 0 0 True
FIC10MEN8 varchar 100 0 0 True
FIC10MEN9 varchar 100 0 0 True
FIC10MEN10 varchar 100 0 0 True
FIC10MEN11 varchar 100 0 0 True
FIC10MEN12 varchar 100 0 0 True
FIC10MEN13 varchar 100 0 0 True
FIC10MEN14 varchar 100 0 0 True
FIC10MEN15 varchar 100 0 0 True

OS_BUILD

BuildInfo

ColumnType MaxLength Precision Scale IsNullable
ColumnName
BuildDesc varchar 100 0 0 False
BuildDate datetime 8 23 3 False
BuildNumber int 4 10 0 False

CodeCountIndicator

ColumnType MaxLength Precision Scale IsNullable
ColumnName
CTV3Code varchar 50 0 0 False
CodeCountIndicator float 8 53 0 True

LatestBuildTime

ColumnType MaxLength Precision Scale IsNullable
ColumnName
DtLatestBuild datetime 8 23 3 False

OpenSAFELYSchemaInformation

ColumnType MaxLength Precision Scale IsNullable
ColumnName
TableName nvarchar 256 0 0 False
TableName sysname 256 0 0 False
ColumnId int 4 10 0 False
ColumnName nvarchar 256 0 0 True
ColumnName sysname 256 0 0 True
ColumnType nvarchar 256 0 0 False
ColumnType sysname 256 0 0 False
MaxLength smallint 2 5 0 False
Precision tinyint 1 3 0 False
Scale tinyint 1 3 0 False
CollationName nvarchar 256 0 0 True
CollationName sysname 256 0 0 True
IsNullable bit 1 1 0 True
DataSource varchar 1000 0 0 True

OS_DERIVED

Household

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Household_ID bigint 8 19 0 True
MSOA varchar 50 0 0 True
NFA_Unknown bit 1 1 0 True
CareHome bit 1 1 0 True
Prison bit 1 1 0 True
HouseholdSize int 4 10 0 True
MatchesUprnCount bit 1 1 0 True
MixedSoftwareHousehold bit 1 1 0 True
TppPercentage int 4 10 0 True

HouseholdMember

ColumnType MaxLength Precision Scale IsNullable
ColumnName
HouseholdMember_ID bigint 8 19 0 False
Patient_ID bigint 8 19 0 False
Household_ID bigint 8 19 0 False

MSOA_PopulationEstimates_2019

ColumnType MaxLength Precision Scale IsNullable
ColumnName
MSOA_Code varchar 50 0 0 False
LA_Code_2019 varchar 50 0 0 False
LA_Code_2020 varchar 50 0 0 False
Age_All int 4 10 0 False
Age_0 int 4 10 0 False
... ... ... ... ... ...
Age_86 int 4 10 0 False
Age_87 int 4 10 0 False
Age_88 int 4 10 0 False
Age_89 int 4 10 0 False
Age_90_Plus int 4 10 0 False

95 rows × 5 columns

PotentialCareHomeAddress

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
PatientAddress_ID bigint 8 19 0 False
LocationRequiresNursing varchar 5 0 0 False
LocationDoesNotRequireNursing varchar 5 0 0 False

S1

Appointment

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Appointment_ID bigint 8 19 0 False
Organisation_ID bigint 8 19 0 False
BookedDate datetime 8 23 3 False
StartDate datetime 8 23 3 False
EndDate datetime 8 23 3 False
ArrivedDate datetime 8 23 3 False
SeenDate datetime 8 23 3 False
FinishedDate datetime 8 23 3 False
Status int 4 10 0 False

CTV3Dictionary

ColumnType MaxLength Precision Scale IsNullable
ColumnName
CTV3Code varchar 50 0 0 False
Description varchar 255 0 0 False

CTV3Hierarchy

ColumnType MaxLength Precision Scale IsNullable
ColumnName
ParentCTV3Code varchar 50 0 0 False
ParentCTV3Description varchar 255 0 0 False
ChildCTV3Code varchar 50 0 0 False
ChildCTV3Description varchar 255 0 0 False
ChildToParentDistance int 4 10 0 False

CodedEvent

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Consultation_ID bigint 8 19 0 False
CodedEvent_ID bigint 8 19 0 False
CTV3Code varchar 50 0 0 False
NumericValue real 4 24 0 False
ConsultationDate datetime 8 23 3 False

CodedEventRange

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID int 4 10 0 False
CodedEvent_ID bigint 8 19 0 False
CodedEventRange_ID bigint 8 19 0 False
Consultation_ID bigint 8 19 0 False
LowerBound real 4 24 0 False
UpperBound real 4 24 0 False
Comparator tinyint 1 3 0 False

Consultation

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Consultation_ID bigint 8 19 0 False
Registration_ID bigint 8 19 0 False
ConsultationDate datetime 8 23 3 False

DataDictionary

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Table varchar 1000 0 0 True
Type varchar 255 0 0 True
Code varchar 255 0 0 True
Description varchar 1000 0 0 True

ICD10Dictionary

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Code varchar 4 0 0 False
CodeDescription varchar 500 0 0 False
ParentCode char 3 0 0 False
ParentCodeDescription varchar 500 0 0 False

MedicationDictionary

ColumnType MaxLength Precision Scale IsNullable
ColumnName
MultilexDrug_ID varchar 767 0 0 True
ProductId bigint 8 19 0 False
FullName varchar 1000 0 0 True
RootName varchar 100 0 0 True
PackDescription varchar 50 0 0 True
Form varchar 50 0 0 True
Strength varchar 500 0 0 True
CompanyName varchar 200 0 0 True
DMD_ID varchar 50 0 0 True

MedicationIssue

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Consultation_ID bigint 8 19 0 False
MedicationIssue_ID bigint 8 19 0 False
RepeatMedication_ID bigint 8 19 0 False
MultilexDrug_ID varchar 255 0 0 False
Dose varchar 255 0 0 False
Quantity varchar 255 0 0 False
StartDate datetime 8 23 3 False
EndDate datetime 8 23 3 False
MedicationStatus int 4 10 0 False
ConsultationDate datetime 8 23 3 False

MedicationRepeat

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Consultation_ID bigint 8 19 0 False
MedicationRepeat_ID bigint 8 19 0 False
MultilexDrug_ID varchar 255 0 0 False
Dose varchar 255 0 0 False
Quantity varchar 255 0 0 False
StartDate datetime 8 23 3 False
EndDate datetime 8 23 3 False
MedicationStatus int 4 10 0 False
ConsultationDate datetime 8 23 3 False

MedicationSensitivity

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID int 4 10 0 False
Consultation_ID bigint 8 19 0 False
MedicationSensitivity_ID int 4 10 0 False
MultilexDrug_ID varchar 100 0 0 False
StartDate datetime 8 23 3 False
FormulationSpecific bit 1 1 0 False
Ended bit 1 1 0 True
ConsultationDate datetime 8 23 3 False

Organisation

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Organisation_ID bigint 8 19 0 False
GoLiveDate datetime 8 23 3 False
STPCode varchar 50 0 0 False
MSOACode varchar 150 0 0 False
Region varchar 255 0 0 False

Patient

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
DateOfBirth date 3 10 0 True
DateOfDeath date 3 10 0 True
Sex char 1 0 0 False

PatientAddress

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
PatientAddress_ID bigint 8 19 0 False
StartDate datetime 8 23 3 False
EndDate datetime 8 23 3 False
AddressType int 4 10 0 False
RuralUrbanClassificationCode int 4 10 0 False
ImdRankRounded int 4 10 0 False
MSOACode varchar 150 0 0 False

QOFClusterReference

ColumnType MaxLength Precision Scale IsNullable
ColumnName
ClusterType varchar 50 0 0 False
ClusterName varchar 100 0 0 False
CTV3Code varchar 50 0 0 False
Description varchar 255 0 0 False

RegistrationHistory

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Registration_ID bigint 8 19 0 False
Organisation_ID bigint 8 19 0 False
Patient_ID bigint 8 19 0 False
StartDate datetime 8 23 3 False
EndDate datetime 8 23 3 False

UnitDictionary

ColumnType MaxLength Precision Scale IsNullable
ColumnName
UnitDictionary_ID int 4 10 0 False
CTV3Code varchar 50 0 0 False
Units varchar 50 0 0 False
Minimum real 4 24 0 False
Maximum real 4 24 0 False
LowerNormalBound real 4 24 0 False
UpperNormalBound real 4 24 0 False
DecimalPlaces int 4 10 0 False

Vaccination

ColumnType MaxLength Precision Scale IsNullable
ColumnName
Patient_ID bigint 8 19 0 False
Vaccination_ID bigint 8 19 0 False
VaccinationDate datetime 8 23 3 False
VaccinationName varchar 100 0 0 False
VaccinationName_ID bigint 8 19 0 False
VaccinationSchedulePart int 4 10 0 False

VaccinationReference

ColumnType MaxLength Precision Scale IsNullable
ColumnName
VaccinationName_ID int 4 10 0 False
VaccinationName varchar 100 0 0 False
VaccinationContent varchar 50 0 0 False

YCodeToSnomedMapping

ColumnType MaxLength Precision Scale IsNullable
ColumnName
YCode varchar 5 0 0 False
SctConceptId bigint 8 19 0 True