City Distance Map Database Design Well I am just a db beginner, I want to design something like this


I want to keep simple as possible, well I will enter all values[Cities, Distance etc.,] manually, because my cities are limited.  
And also I want to Query like 'oneCity' Between 'AnotherCity', will yield the cities between them.


Any Advice, solution, concepts, technique or tutorials are appreciated. I googled a lot, but no solutions are appropriate :( 

And also I don't know this is the right place to post, if this is a wrong place please ignore my mistake.

Thanks.
I've never tried this but here's some ideas I have:

You'll need to keep track of all cities, so you will need a City table.
You also need to know the distance between two cities that are directly connected to each other, so you will need a cities_distances table like this:


cities_distances
----------------
  From_City_ID
  To_City_ID
  Distance


This will let you keep track of how much distance it is from one city to another. Note that this is directional - The distance from A to B might not be the same as from B to A if the highway between them has a lane for each direction, and one lane has a detour around some natural feature (like a small lake, or difficult parts of terrain). Within a city, this is also important because you'll have one-way streets. 

If you don't care about the direction of the distance between the two cities, you can just rename those two columsn CityA_ID and CityB_ID.

As for writing a query that gets the distance between any two cities, it might be easier to do that in code using Dijkstra's algorithm (because this is really just a graphing problem) than in query, but it might be possible using recursive queries. Or, you could generate a pre-calculated table that stores all this information and just look the data up in that.