I am developing a contact management system and am facing an interesting problem related to sequential modeling of geographical locations. I would like to be able to record locations associated with a specific person (mailing address (es) for work, school, home, etc.). My thought is to create a locale table such as:
Locales (ID, LocationName, ParentID) , where autonomous locations (for example, countries, for example, USA) are parents of themselves. Thus, I can have arbitrarily deep nesting of “political units” (COUNTRY> STATE> CITY or COUNTRY> STATE> CITY> UNIVERSITY). Some queries will necessarily have recursion.
I would be grateful for any other recommendations, or perhaps for advice on the predictable problems that I would probably encounter with such a scheme.
source
share