Hi. I have been fighting with myself over this design question, Is a single field table valid and of good design?
The data I'm working with spans over 130 years, things change. Kitchener, ON. was once Berlin ON., Brant county didn't exist, Crysler Farm battlefield is underwater, political ridings boundaries (in ON., three levels), census data boundaries, etc., all change. We (mankind) also have many different systems labeling the same ground with different boundaries (that change).
I'm trying to create a database which records these things (locations) though time. So for a "place" it will could have
a name (one or more with To/From dates)
a population (zero or more census with dates)
a location (Geocoded maybe, an address maybe with To/From dates)
be located in many different regions with To/From dates
may have a control location which could change (Capital) with To/From dates
Now I'm also thinking that there is no difference between Point, District, City, Zone, County, Region, State, and Country. They are all man defined, have a title (name), have a position in the hierarchy, and can change over time. Doing a bunch of tables that are nearly identical feels wrong, especially when some (most) need to be skipped. Combining them into one with a couple of lookup tables defining their roles, feels better but causes recursive calls.
Whenever I place fields in the Places table, the temporal time part or the number of nulls pulls it out. So in my mind I see a Places table as a single field table using a auto number, creating a unique PK. It would have relationships (one to many or many to many) to these tables which would have the PlaceID as a FK, a To/From date fields plus the other stuff.
So does this sound right??? Is there a better (another) way that I'm missing? I could be totally wrong (I have been before), but this is why I'm asking.
Thanks for looking
Western_Neil