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.

  • 1
    Are you attempting to do this for the entire world, or just Americaland? – Philᵀᴹ Feb 20 '15 at 20:25
  • @Phil at first, just for my country (which technically is in America, but the southern one). But I'd like to do it in such a way that I don't have to struggle too much in the case of expanding it. – Gust M. Feb 20 '15 at 21:39
  • Do you have a City, State or Country table already? – Thomas Kejser Feb 21 '15 at 19:21
  • @ThomasKejser No. I'm experimenting with the setup I mentioned earlier. – Gust M. Feb 23 '15 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.