I am attempting to logically structure my location data. Say for example I have cities/states/countries. I have objects that reference a location, but at any level. An object may reference a city or it may reference a state, depending on how granular we know its location to be. If the city is known for an object, the state and country can be inferred, so the object need not point to all levels in fact that would be redundant and require checking consistency. Ideally, the object would have a foreign key reference to a generic "location" table (which would have child tables: cities, states, countries). The cities, states and countries could then have foreign keys pointing to each other (cities point to states, which point to countries).
Does anyone know a workaround for my problem? Initially I was thinking a single locations table with a location "type" and a parent location id (references itself) although that's not ideal because I then have to do checks to ensure location type country does not end up being "contained" in a city. But perhaps I can write checks that ensure that never happens.
