Documentation
Common Data Model (CDM)
Convert Database to CDM (ETL)
Tool Specific Documentation
Sidebar
documentation:next_cdm:location_history
Location History
- Requester: Robert Miller
- Discussion: here.
Proposal
The location_id in the Person table is only capable of storing a single location address for an individual. We want to know where a person's location was for a given date-time. This maybe solved using a location_history table, which acts as a relational entity between Person and Location.
There are several things to consider here:
- An entity (person, care site, device) may change locations over a period of time
- A location may have multiple entities (person, device, care site) over a period of time
- An entity may have more than one locations at the same time (e.g. vacation home for a person, or mailing address vs residing address vs billing address, beneficiaries address vs. subscribers address)
The first two issues maybe solved easily. The third, if we are concerned about it, may be resolved by adding an location type field e.g (“primary residence”).
Note in the table below: both person and location IDs do not need to be unique in the residency table.
| location_history_id | person_id | location_id | start_date | start_time | end_date | end_time |
| res1 | John Doe | location1 | May 1985 | 24:00 | - | - |
| res2 | Jane Doe | location1 | May 1985 | - | - | - |
| res3 | Bob Smith | location2 | July 2011 | - | June 2012 | - |
| res4 | Bob Smith | location3 | July 2012 | 14:55 | August 2012 | 21:00 |
It was agreed that the location_id in person table would represent the current location of the person.
It was discussed that location_history may need to be generalized to include device, care site, provider etc. How do we do that?
| location_history_id | entity_domain_id | entity_id | location_id | start_date | start_time | end_date | end_time |
| res1 | person | person_id_1 | location1 | May 1985 | 24:00 | - | - |
| res2 | provider | provider_id_1 | location1 | May 1985 | - | - | - |
| res3 | care_site | care_site_id_1 | location2 | July 2011 | - | June 2012 | - |
| res4 | person | person_id_2 | location3 | July 2012 | 14:55 | August 2012 | 21:00 |
Use Cases
- What zip code did person x live in when they were diagnosed with y?
- Are people who live closer to x more likely to y?
- Is there a cluster of people with certain conditions, when did that cluster first occur, and has it changed in location over time
- How do care sites compare in terms of average travel time? Does it matter?
- Are there hot spots for specific conditions?
documentation/next_cdm/location_history.txt · Last modified: 2016/10/14 18:15 by gowtham_rao
