OpenSAFELY-TPP Database Schema¶

This report displays the schema of the OpenSAFELY-TPP database. It is part of the OpenSAFELY platform's technical documentation and is published at https://reports.opensafely.org/.

The schema is read from the OpenSAFELYSchemaInformation table, which is refreshed at the same time as the core SystmOne primary care datasets. 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, then please speak to TPP.

If you would like to use the OpenSAFELY platform, then you should read our documentation, our principles, and our process for onboarding new users. If you would like to see the code we used to create this report, then you can view it on GitHub.

Data Sources¶

The core SystmOne primary care datasets are stored in the S1 tables in the database. The table below lists other, externally-linked datasets:

Dataset	Source	Tables
All positive or negative SARS-CoV2 tests	SGSS	SGSS_AllTests_Positive, SGSS_AllTests_Negative
First-ever positive or negative SARS-CoV2 test	SGSS	SGSS_Positive, SGSS_Negative
A&E attendances	SUS Emergency Care	EC
In-patient hospital admissions	SUS Admitted Patient Care Spells	APCS
Out-patient hospital appointments	SUS	OPA
Covid-related ICU admissions	ICNARC	ICNARC
Covid-related in-hospital deaths	CPNS	CPNS
COVID-19 Infection Survey	ONS	ONS_CIS
All-cause registered deaths	ONS	ONS_Deaths
High cost drugs		HighCostDrugs
Unique Property Reference Number1		UPRN
Master Patient Index		MPI
Health and Social Care Worker identification2		HealthCareWorker

¹Used for deriving household variables
²Collected at the point of vaccination

Some of these tables are accompanied by additional tables. For example, the OPA table contains the core out-patient appointment event data, and is accompanied by the OPA_Cost, OPA_Diag, and OPA_Proc tables.

Report Run Date¶

Table Names by Data Source¶

Table Schemas¶

The table schemas contain the following information:

• ColumnName, the column name
• ColumnType, the column type, such as integer, numeric, or date. For more information, see SQL Server's data types documentation
• Precision, Scale and MaxLength. For more information, see SQL Server's precision, scale, and length documentation
• IsNullable, whether NULL values are accepted

Use the links in the list below to jump to a table schema.