Documentation
Common Data Model (CDM)
Convert Database to CDM (ETL)
Tool Specific Documentation
Common Data Model (CDM)
Convert Database to CDM (ETL)
Tool Specific Documentation
This is an old revision of the document!
In the context of adding GIS capabilities to the CDM:
The location_id in the Person table is only capable of storing a single residence for an individual. We want to be able to know where a person's residence was for a given date. In another system, this problem is solved by introducing a Residency table, which acts as a relational entity between Person and Location.
There are several things to consider here:
The first two issues I believe a Residency table could solve. The third, if we are concerned about it, could be resolved by adding a “primary residence” boolean to the table. 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 suggested that, since a large portion of implementations would only have a single location per person, we leave the location_id in the person table and have it refer to the most current residence.
The other necessary component for GIS capabilities would be to add latitude and longitude coordinates to the location table, though that may need to be discussed elsewhere.