dwh_patient_ipphist
Description¶
This table stores the history of identifier for a patient (IPP). A patient may have multiple identifiers because they were created in duplicate and then merged in the source or in the warehouse. A column specifies which identifier should be displayed to the user.
CNIL compliant warehouse¶
This data model was published before the CNIL referential for health data warehouse.
This table is not used in the warehouse as it contains only directly identifying data.
Nevertheless, it can be used in the identifying part of the warehouse to store patient identifier history.
Uniqueness¶
An identifier should be unique for one center and one software. We have a uniqueness constraint for the combination of the fields hospital_patient_id, instance_ipp_id, ipp_origin_code.
Columns¶
| Field | User Guide | ETL Conventions | Datatype | Required | Primary Key | Foreign Key | FK Table |
|---|---|---|---|---|---|---|---|
| ipphist_num | Unique identifier | bigint(64) | Yes | Yes | No | ||
| patient_num | bigint(64) | Yes | No | Yes | dwh_patient | ||
| hospital_patient_id | Patient identifier in source data (IPP) | varchar(100) | Yes | No | No | ||
| origin_patient_id | Use to separate real identifier from technical identifier (for instance pk in data source with no medical meaning) | Set to SIH for real identifier | varchar(40) | No | No | No | |
| master_patient_id | Indicate which identifier should be display to users. Each patient should have one and only one master identifier. | true if this identifier is the master | boolean | Yes | No | No | |
| instance_ipp_id | Code of the healthcare center, see hospital_instance for more informations | Names accross all instance_id fields in other tables should match | varchar(40) | No | No | No | |
| ipp_origin_code | Indicate source software for this record. | varchar(300) | No | No | No | ||
| upload_id | Identifier of the pipeline integration run, used to differentiate each batch of integrated data. | Defined at the start of the pipeline as datetime.now().strftime("%Y%m%d%H%M%S"). For example a batch integrated on 15/09/2025 at 00:00:00 has upload_id = 20250915000000. |
bigint(64) | No | No | No | |
| update_date | Date and time of the record’s last update. | timestamptz | No | No | No |