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.

StackExchange Database Administrators Q#16561, answer score: 3