I am designing a contact management system and have come across an interesting issue regarding modeling geographic locations in a consistent way. I would like to be able to record locations associated with a particular person (mailing address(es) for work, school, home, etc.) My thought is to create a table of locales such as the following:
Locales (ID, LocationName, ParentID) where autonomous locations (such as countries, e.g. USA) are parents of themselves. This way I can have an arbitrarily deep nesting of 'political units' (COUNTRY > STATE > CITY or COUNTRY > STATE > CITY > UNIVERSITY). Some queries will necessarily involve recursion.
I would appreciate any other recommendations or perhaps advice regarding predictable issues that I am likely to encounter with such a scheme.
You should definitely use a null value for the parent (root) level items rather than making them parents of themselves. Try to avoid putting in data unless it actually has meaning.
Hello, I have a similar question and I really like what you're saying. I was wondering if you could take a look or maybe get in touch with me to give me some advice if possible. my nickname at gmail com. thanks so much.