Date - Location Data Structure

[Western_Neil]

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

[ArviLaanemets]

I think you need
Table of geographical locations, like
tblLocations, LocationID, Longitude, Latitude (This table determines geographical locations only! And probably you have to define an unique index based on Longitude and Latitude too, to avoid the possibility the same location is registered twice!)
Then you need a table, where you can register one or several different objects, placed in certain location, like
tblObjects: ObjectID, LocationID, ObjectType, ObjectRegisteredAt, ObjectUnregisteredAt, [ObjectAddressID]...;
In case, you want to register the placement of objects in current postal addressing system, you need a table like
tblObjectCurrentAddress: ObjectCurrentAddressID, ObjectID, ... (There will be several address fields, and it depends on object how much of them are filled. E.g. when object is United States of America, only country field is filled, when tho object is some building, the full postal address info is registered for it. Etc.)
Objects can have different names at different time periods, so you need
tblObjectNames: ObjectNameID, ObjectID, ObjectName, ValidAt (You don't need the end date for name here, as ValidAt date for next name determines it instead. But you have to design a function, which returns the end date of object's name validity at any date. For case the name at asked date is currently valid, this function probably may return todays or tomorrows date);
From your post follows, that some of objects can be a part of other (parent) object. And I assume, this relationship can change over time. So you need
tblObjectParent: ObjectParentID, ObjectID, ParentObjectID, ValidAt, ValidTo

[Western_Neil]

Filled Out Tables for Location-Date Structure


ArviLaanemets thank you for your reply. I have been working on it the last couple of days and have got to here:
A GeoCode with 6 digit accuracy is into 10th's of a meter. I guess most dups will be ball park ones (.120000) and are not a problem (that I see).


Rules:
1) Place has one Type, Type has many Places, (Dup GeoCode allowed) Can a Place change Type over time? or new Place.ID?
2) Place.Building (Type) has many address, Address is one Place
Other types are Metro, City, Town, Camp POI, and Area.
3) Place has many Names, Name is one place (Dup Name allowed) eg. Springfield
4) Place is in many GeoParts, GeoPart has many Places
5) GeoPart has many Parents, Parent is in many GeoParts (Recursive)


Giving the following Tables:

TblPlace:
Auto	Single	Single
ID	Latitude	Latitude
1	51.046258	-114.088878
2	51.048615	-114.070847
TblPlaceType:
Auto	Size 15	Size 63
ID	Tag	Note
2	Town	>1,000,000 pop Max
7	Building	has address
8	Area	General Location
tblPlaceAddress:
Auto	LongInt	Date	Size 15	Size 1
ID	PlaceFK	Opened	Country	Note
1	1	1918-09-24	Canada
TbPlacelName (Not for Type Buildomg)
Auto	LongIng	Date
ID	PlaceFK	VaildFr
1	2	1884-11-07
TblGeoPart:
Auto	Date	Date
ID	VaildFr	VaildTo
1	1882-05-08	1905-09-01
2	1882-05-08
TblGeoPartPlace:
Auto	LongInt	LongInt
ID	PlaceFK	GeoPartFK
1	2	1
TblGeoPartX:
Auto	LongInt	LongInt
ID	GeoPartSubFK	GeoPartPntFK
1	1	2
TblGeoCapital:
Auto	LongInt	LongInt
ID	GeoPartFK	PlaceFK
1	1	2

Have I missed anything or broken rules?
I think I have worked in Time in, does it seem to work?




Thanks for looking and Thinking about this;
Neil

[Western_Neil]

A secondary question on dates would be; all the same or use From/To and Open ended?

I'm thinking of putting the dates into a table called TblChange allowing for a Source field (Act, Listing, Book, etc)

Neil