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:
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.
|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?
|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|