Representing a Graph in an RDB...again...(BUT DIFFERENT!!)

I have browsed this group for about 2hours, and have seen many posts on the topic of graphs in RDBs, but i havent yet found one that answers my problem.

Basically, I have a set of towns. Each town is connected to another town with a cost of travel. This seems simple enough right? Just a connected graph. One problem is that for every node A to C, one can also go C to A, so its bidirectional always. One can also start on any node.

I was going to simply represent this as {node_id, from_node, to_node, to_node_weight}

Just having one weight per row makes it possible to do the calculation of shortest path, which i need.

However, there is a spanner in the works - Certain links can only happen at certain times on certain days....eg A to C can only happen on thursdays and tuesdays at 2pm until 3pm. So one has to check an inputted time and then when calculated shortest path, ensure that one does not use edges that are unavaible at that time!!

This is giving me a major headache. I am a programmer, not a DB architect. Our DB architect just left, and I am really struggling to solve this.

To give some background of the problem as a whole - the system is a parcel tracking system which allows a client to specify parcel weight and starting location and end location. Due to real world issues, all routes are not available at all times of the day (hence the time and day issue), and I am really struggling to represents this as an RDB schema.

I am not even sure if I am representing the "simple form" of the problem (ie no times and days) correctly as simply having only the "to_node" weight, and not having both the from_node AND the to_node weight in the same row!

Any help at all is greatly appreciated!

Thanks. Received on Sat Nov 06 2004 - 20:57:02 CET