Remote Subscriber Database (RSD) Schema (Compass 2) - Revision history

JoshuA at 08:51, 18 May 2021

2021-05-18T08:51:21Z

Show changes

JoshuA: /* Table: patient_address_match */

2021-05-18T08:37:54Z

Table: patient_address_match

Show changes

JoshuA: /* Table: patient_contact */

2021-05-17T14:43:32Z

Table: patient_contact

← Older revision		Revision as of 14:43, 17 May 2021
Line 2,063:		Line 2,063:
	\|-		\|-
	\|start_date		\|start_date
	\|~~Date~~		\|date
	\|NOT NULL		\|NOT NULL
	\|The start date of this address being relevant		\|The start date of this address being relevant
Line 2,069:		Line 2,069:
	\|-		\|-
	\|end_date		\|end_date
	\|~~Date~~		\|date
	\|DEFAULT NULL		\|DEFAULT NULL
	\|The end date of this address being relevant		\|The end date of this address being relevant
Line 2,324:		Line 2,324:
	\|		\|
	\|-		\|-
	\|start_date ~~date~~		\|start_date
	\|date		\|date
	\|DEFAULT NULL		\|DEFAULT NULL
Line 2,330:		Line 2,330:
	\|		\|
	\|-		\|-
	\|end_date ~~date~~		\|end_date
	\|date		\|date
	\|DEFAULT NULL		\|DEFAULT NULL
Line 2,421:		Line 2,421:
	\|-		\|-
	\|is_nhs_number_valid		\|is_nhs_number_valid
	\|~~tinyint(1)~~		\|boolean
	\|NOT NULL		\|NOT NULL
	\|Whether the nhs number is valid		\|Whether the nhs number is valid
Line 2,427:		Line 2,427:
	\|-		\|-
	\|is_nhs_number_verified_by_publisher		\|is_nhs_number_verified_by_publisher
	\|~~tinyint(1)~~		\|boolean
	\|NOT NULL		\|NOT NULL
	\|Whether the nhs number has been verified by the publisher		\|Whether the nhs number has been verified by the publisher
Line 2,433:		Line 2,433:
	\|-		\|-
	\| colspan="5" \|CONSTRAINT PRIMARY KEY (organization_id)		\| colspan="5" \|CONSTRAINT PRIMARY KEY (organization_id)
			\|-
			\| colspan="5" \|CONSTRAINT fk_patient_pseudo_id FOREIGN KEY (patient_id, organization_id)
			\|-
			\| colspan="5" \|REFERENCES patient (id, organization_id)
	\|}		\|}

JoshuA: /* Table: patient_uprn */

2021-05-17T14:38:48Z

Table: patient_uprn

← Older revision		Revision as of 14:38, 17 May 2021
Line 2,487:		Line 2,487:
	\|-		\|-
	\|no_address		\|no_address
	\|~~tinyint(1)~~		\|boolean
	\|DEFAULT NULL		\|DEFAULT NULL
	\|		\|
Line 2,493:		Line 2,493:
	\|-		\|-
	\|invalid_address		\|invalid_address
	\|~~tinyint(1)~~		\|boolean
	\|DEFAULT NULL		\|DEFAULT NULL
	\|		\|
Line 2,499:		Line 2,499:
	\|-		\|-
	\|missing_postcode		\|missing_postcode
	\|~~tinyint(1)~~		\|boolean
	\|DEFAULT NULL		\|DEFAULT NULL
	\|		\|
Line 2,505:		Line 2,505:
	\|-		\|-
	\|invalid_postcode		\|invalid_postcode
	\|~~tinyint(1)~~		\|boolean
	\|DEFAULT NULL		\|DEFAULT NULL
	\|		\|

JoshuA: /* Table: organization */

2021-05-17T14:32:28Z

Table: organization

@@ Line 1,735: / Line 1,735: @@
 |-
 | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
 |}

JoshuA: /* Table: observation */

2021-05-17T13:50:32Z

Table: observation

← Older revision		Revision as of 13:50, 17 May 2021
Line 1,621:		Line 1,621:
	\|-		\|-
	\|date_precision_concept_id		\|date_precision_concept_id
	\|~~smallint(6)~~		\|int
	\|DEFAULT NULL		\|DEFAULT NULL
	\|Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)		\|Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
Line 1,627:		Line 1,627:
	\|-		\|-
	\|result_value		\|result_value
	\|~~double~~		\|rea
	\|DEFAULT NULL		\|DEFAULT NULL
	\|The value of the result of the observation		\|The value of the result of the observation
Line 1,651:		Line 1,651:
	\|-		\|-
	\|result_concept_id		\|result_concept_id
	\|~~bigint(20)~~		\|int
	\|DEFAULT NULL		\|DEFAULT NULL
	\|Reference to the clinical coding of the result		\|Reference to the clinical coding of the result
Line 1,657:		Line 1,657:
	\|-		\|-
	\|is_problem		\|is_problem
	\|~~tinyint(1)~~		\|boolean
	\|NOT NULL		\|NOT NULL
	\|Whether the observation is marked as a problem		\|Whether the observation is marked as a problem
Line 1,663:		Line 1,663:
	\|-		\|-
	\|is_review		\|is_review
	\|~~tinyint(1)~~		\|boolean
	\|NOT NULL		\|NOT NULL
	\|Whether the observation is a review of an existing problem		\|Whether the observation is a review of an existing problem

JoshuA: /* Table: medication_statement */

2021-05-17T13:38:41Z

Table: medication_statement

← Older revision		Revision as of 13:38, 17 May 2021
Line 1,199:		Line 1,199:
	\|-		\|-
	\|date_precision_concept_id		\|date_precision_concept_id
	\|~~smallint(6)~~		\|int
	\|DEFAULT NULL		\|DEFAULT NULL
	\|Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)		\|Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
Line 1,205:		Line 1,205:
	\|-		\|-
	\|is_active		\|is_active
	\|~~tinyint(1)~~		\|boolean
	\|NOT NULL		\|NOT NULL
	\|Whether the flag is active or not		\|Whether the flag is active or not
Line 1,477:		Line 1,477:
	\|-		\|-
	\|date_precision_concept_id		\|date_precision_concept_id
	\|~~smallint(6)~~		\|int
	\|DEFAULT NULL		\|DEFAULT NULL
	\|Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)		\|Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
Line 1,483:		Line 1,483:
	\|-		\|-
	\|is_active		\|is_active
	\|~~tinyint(4)~~		\|boolean
	\|DEFAULT NULL		\|DEFAULT NULL
	\|Whether the medication is active or not		\|Whether the medication is active or not
Line 1,501:		Line 1,501:
	\|-		\|-
	\|quantity_value		\|quantity_value
	\|~~double~~		\|real
	\|DEFAULT NULL		\|DEFAULT NULL
	\|The value of the medication that was prescribed eg 50		\|The value of the medication that was prescribed eg 50

JoshuA: /* Table: encounter_additional */

2021-05-17T13:33:39Z

Table: encounter_additional

← Older revision		Revision as of 13:33, 17 May 2021
Line 45:		Line 45:
	\|-		\|-
	\|clinical_effective_date		\|clinical_effective_date
	\|~~Date~~		\|date
	\|DEFAULT NULL		\|DEFAULT NULL
	\|The date the clinical code is recorded for		\|The date the clinical code is recorded for
Line 836:		Line 836:
	\|-		\|-
	\|property_id		\|property_id
	\|~~bigint~~		\|int
	\|NOT NULL		\|NOT NULL
	\|IM reference (i.e. Admission method)		\|IM reference (i.e. Admission method)
Line 842:		Line 842:
	\|-		\|-
	\|value_id		\|value_id
	\|~~bigint~~		\|int
	\|NOT NULL		\|NOT NULL
	\|IM reference (i.e. Emergency admission)		\|IM reference (i.e. Emergency admission)
			\|
			\|-
			\|json_value
			\|JSON
			\|NULL
			\|where there is no mapped value_id, just raw JSON (i.e. birth delivery details)
			\|
			\|-
			\|text_value
			\|varchar(255)
			\|NULL
			\|where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)
	\|		\|
	\|-		\|-

JoshuA: /* Table: episode_of_care */

2021-05-17T13:27:54Z

Table: episode_of_care

← Older revision		Revision as of 13:27, 17 May 2021
Line 123:		Line 123:
	\|-		\|-
	\|organization_id		\|organization_id
	\|~~varchar(36)~~		\|bigint
	\|NOT NULL		\|NOT NULL
	\|Owning organisation (i.e. publisher)		\|Owning organisation (i.e. publisher)
Line 129:		Line 129:
	\|-		\|-
	\|patient_id		\|patient_id
	\|~~varchar(36)~~		\|bigint
	\|NOT NULL		\|NOT NULL
	\|The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times		\|The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times
Line 714:		Line 714:
	\|-		\|-
	\|		\|


	date_precision_concept_id		date_precision_concept_id
	\|int		\|int
Line 786:		Line 784:
	\|DEFAULT NULL		\|DEFAULT NULL
	\|The date the encounter was recorded		\|The date the encounter was recorded
	\|
	\|-
	~~\|service_provider_organization_id~~
	~~\|bigint(20)~~
	~~\|DEFAULT NULL~~
	~~\|Reference to the service provider organisation~~
	\|		\|
	\|-		\|-
	\| colspan="5" \|		\| colspan="5" \|


	CONSTRAINT pk_encounter_id PRIMARY KEY (organization_id,person_id,id),		CONSTRAINT pk_encounter_id PRIMARY KEY (organization_id,person_id,id),
	\|-		\|-
Line 803:		Line 793:
	\| colspan="5" \|REFERENCES appointment (id) MATCH SIMPLE		\| colspan="5" \|REFERENCES appointment (id) MATCH SIMPLE
	\|-		\|-
	\| colspan="5" \|ON UPDATE NO ACTION ON DELETE NO ACTION~~,*/~~		\| colspan="5" \|ON UPDATE NO ACTION ON DELETE NO ACTION
	\|-		\|-
	\| colspan="5" \|CONSTRAINT fk_encounter_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)		\| colspan="5" \|CONSTRAINT fk_encounter_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
Line 1,067:		Line 1,057:
	\|		\|
	\|-		\|-
	\|date_registered ~~date~~		\|date_registered
	\|date		\|date
	\|DEFAULT NULL		\|DEFAULT NULL
Line 1,073:		Line 1,063:
	\|		\|
	\|-		\|-
	\|date_registered_end ~~date~~		\|date_registered_end
	\|date		\|date
	\|DEFAULT NULL		\|DEFAULT NULL
Line 3,100:		Line 3,090:
	\|		\|
	\|-		\|-
	\| colspan="5" \|~~CONSTRAINT~~ PRIMARY KEY (organization_id,id,patient_id,person_id)		\| colspan="5" \|PRIMARY KEY (organization_id,id,patient_id,person_id)
			\|-
			\| colspan="5" \|CONSTRAINT fk_registration_status_history_episode_id FOREIGN KEY (episode_of_care_id)
	\|}		\|}
	== Table: schedule ==		== Table: schedule ==

JoshuA: /* Table: patient */

2021-05-17T13:18:36Z

Table: patient

@@ Line 1,817: / Line 1,817: @@
 |-
 |title
-|varchar(50)
+|varchar(255)
+|
 |The title of the patient
@@ Line 1,874: / Line 1,874: @@
+|
 |Reference to the organisation  the patient is registered at
+|
 |-
@@ Line 1,881: / Line 1,899: @@
 |-
 | colspan="5" |REFERENCES organization (id) MATCH SIMPLE
 |}