Say I have an Items table. Each item has a location associated to it. This location can either be a city, a state or a country. I believe this could normally be solved with a column Type in the Location table.
However, a city is part of a state and a state is part of a country. So when I add a new item and associate it to a city, for example, I also want it to be associated to that city's state and that state's country.
- What are my options in creating this location table and how do I relate it to the Items table?
PS: My approach so far was to keep using a type column but also create a foreign key in the location table that references that same table. This key indicates which record is the next up in the hierarchy (city->state->country). My conclusion is that this might work, but I'll have some relativelly complex selects (which could in turn slow everything down) and the application will have to deal with some of the work.