Query to work out possible destinations on a set of routes using SQL Server 2008

Question

I'm writing an application that models train routes, which are stored in the database table [TrainStop] as follows:

TrainServiceId StationCode StopIndex IsEnabled

So a given train follows a route and consists of several rows with the StopIndex indicating the order. The problem I am trying to solve is to say which stations a user can get to from a given starting station based on the train services that are available. This would be relatively straightforward BUT it is also possible to disable stops which means that a user cannot get to any destinations after that stop for that train service. It is also possible that multiple trains on different routes can share stations e.g.:

Train Service 1: A, B, C, D, E

Train Service 2: P, Q, B, C, D, R

So if a user is at B they can go to C, D, E and R but if station D is disabled on either service they can get to C only. If station E is disabled on service 1 the user can get to C, D and R.

Solving this problem is fairly straightforward within C# but I am wondering whether it can be solved elegantly and efficiently within SQL? I'm struggling to find a way, for each route, to rule out stations past a row that is not enabled.

Answer 1

I think I might set it up this way. Three tables:

ROUTE
List of routes

STATION
List of stations

ROUTEPATH (or route plan?)
Many-to-many table between route and station, including the StopIndex attribute

Whether a station is enabled or disabled is both an attribute of an individual station (station is closed) AND an attribute of the route path (station before "this" station is closed, so "this" is also closed). That means that IsEnabled should appear in bot the STATION and ROUTEPATH tables.

When a station is to be disabled:

• Flag it as disabled in STATION
• Flag it as disabled in ROUTEPATH
• For each route containing that station, flag all stations with StopIndex greater than that station's as disabled (these last two can be done as one update query)

When a station is to be enabled, reverse the process.

There will of course be problems when more than one station becomes disabled--or rather, when some but not all disabled stations become enabled again. I think it would be better to make ROUTEPATH's attribute "IsDisabled", and make it an integer:

• Zero means not disabled
• Non-zero means disabled
• Increment it by one for each "station disabled" action
• Decrement it by one for each "station enabled" action

A system like this should lend itself to relatively simple queries.