This question relates to data structures, performance and approach to a problem. It is best explained with example so bear with me. For the record, I will be using a combination of PHP, MySQL and Apache server on a dedicated Linux server.
The problem is how we would model the world within a database and use our data structure to efficiently execute queries on this world structure. For instance, take a chunk of the USA part of our model of the world:
The world - North America - - USA - - - California - - - - Los Angeles - - - - San Diego - - - - San Francisco - - - - ... - - - Florida - - - - Orlando - - - Nevada - - - - Las Vegas - - - etc. - - Canada - South America
The way I initially thought of doing this would be to have a kind of parent child structure in a MySQL table. Each place would have it's own record with an ID, name, any other stuff, and a parent ID. For example say the USA record had an ID of 81, California's parent ID would be 81 and say California's ID is 101, Los Angeles would have a parent ID of 101 and so on. I also thought that a "type" field would come in handy if I wanted to store different data and represent each different type of place differently, i.e. country, state, city etc.
Now the issue comes when say I land on a page at a low level, like LA for instance. I want to be able to say.. grab everything within this place's country and print them out on the page. This would likely involve a process similar to the following:
- Execute a query to select LA's parent which is California
- Execute a query to select California's parent which is USA (And is the country we are looking for)
- Execute a query to select all of USA's children i.e. all records that have parent ID equal to USA's ID, which are California, Florida and Nevada
- Execute multiple queries (Or potentially a single query with a fair few arguments depending on the number of children USA has) to grab the children of California, Florida and Nevada
- Continue this process all the way down the structure until we no longer get anything else back
This would result in quite a lot of processing (Some kind of caching would probably come into play to save having to repeat it) to do something which you would think would be rather simple. You can imagine in the example of the LA page you would end up with a hell of a lot of data considering there are 52 states, and each state containing a lot of cities and you could even go as far as to include counties, districts (I'm not American so I'm not sure what exactly comes after cities hah) etc. You can imagine the number of queries this would require.
Can anyone think of a more efficient way to model and play around with this data? Perhaps there is a more efficient way to grab all the data I want from this structure or perhaps there's a better structure I could use?
Thanks for your input.