How can I determine the best data structure/implementation for my dataset?

Question

Preface: I'm a self-taught coder, so a lot of my knowledge is limited to my research. I'm hoping to have other opinions as I want to build things right the first time. I need help with determining an appropriate solution and how to implement the solution.

---

I'm looking to build a least cost alternative model (essentially a shortest path) for delivering between locations (nodes), based on different modes of transportation (vehicles) and the different roads taken (paths). Another consideration is the product price (value) to determine the least cost path.

Here are my important data items:

• nodes: cities where the product will travel to and from.
• paths: roads have different costs, depending on the road.
• vehicles: varying vehicles have differing rental costs when transporting (motorbike, car, truck). Note that the cost of a vehicle is not constant, it is highly dependent on the to/from nodes. For example, using a car to go from city A to city B will have a different cost than using a car to go from city B to A or city A to city C.
• value: Product value. Again, a product's value is highly dependent on its destination node. The same product can have a different value at City A, B or C.

---

Problem Statement

How to setup data structure to best determine where the least cost path would be to get a product from one location to every other location.

Possible Solutions

From my research, I believe a weighted graph data structure would be most suitable for my situation in combination with dijkstra's algorithm. I believe breaking the problem down simpler would be essential, to first create a simple weighted graph of only nodes and paths.

From there, adding the vehicle cost and the product value considerations afterwards. Perhaps just adding the two values as a cost to "visit" a node? (aka incorporate it into the path cost?)

Thoughts on my current solution? Other considerations I overlooked? Perhaps a better solution?

Implementation

I'd love to be able to build this within Excel VBA (as that is how I learned how to code) and Excel is what I use for my tools. Would VBA be too limited in this task? How else can I incorporate my analysis with Excel with another language?

Answer 1

Try the book Practical Management Science by Winston & Albright and check out the chapter on Operations Management - lots of models explained in there from the simple onwards. Available online as a pdf : http://ingenieria-industrial.net/downloads/practicalmanagementscience.pdf

Answer 2

VBA is more a scripting language than a full-fledged one, though one may contend that the underlying framework is .NET. Why don't you give a shot at C++ or Java? If you intuitively understand the data structure and the algorithm, then it'll be a breeze coding in these. Chapter 4 of Algorithms by Sedgewick and Wayne has a beautiful explanation of Shortest Paths. You may also consider studying Bellman-Ford algorithm if you foresee any negative weight cycles on a vertex.