I am relatively new to database design, and I’m trying to design a structure to handle location addresses over a century(+) of time. A place will always have a geocode (Longitude, Latitude) that never changes, but the geopolitical address can change. Example Fort Gibraltar was founded in Rupert’s Land, a British trading territory in 1809, but is now called Winnipeg, Manitoba, Canada. Displaying and reporting should reflect the time period of the report, using the appropriate political labels of the period. “Canada” has grown and changed formal titles at least 4 times, Manitoba the same at different times, and Fort Gary is a subdivision(?) of Winnipeg. The information I’m using doesn’t track these changes but does use the period titles, meaning the change date will be a range until confirmed by research (1809 is what I have now for a date but at some time it could become more accurate).
So from this I see a:
Location Table with ID, Geocode code, “Location Type”
LocationXGeopolictal Table ID, Location.ID, StartDate, EndDate, Geopolitical.ID
Geopolitical Table with ID, Title, ParentGeoplitical.ID
LocationXPlace Table with ID, Location.ID, StartDate, EndDate, Place.ID.
Place Table with ID, “+Current Physical Address”
Some problems I see are:
The many entries that would change (both dates and location when Upper Canada changed to Ontario, Canada).
The recursion with the Geopolitical Table (for State, Region, Country, Area)
Missing a Table of Change Dates?
I’m looking for a structure that will work (and understand) to move this project forward.
see also https://www.accessforums.net/showthread.php?t=79107
Thanks for looking and thinking about this
Neil