touring database design ...

[John@home]

I'm setting up a database for motorcycle-touring

It consist of nodes, routes and tours.

I currently have the following tables:

Nodes (nodeID(pk), description, ... node-data)

Route (routeID(pk), startnode, endnode, description, ... routedata)
..... There can be more then 1 route between 2 nodes ...

tour (TourID(pk), description, ... tourdata)

I want to design/implement route-sequence (= a tour).
How do I implement this sequence of routes in a relational database?
Ofcourse the order, of the routes, is important.

 

[Fred]

For your mission as you described it, nodes are a part of the definition of a
route, but are otherwise irrelevant. A tour is simple a sequence of routes.
Here's and idea:

A tour table:

Tournum (PK)
Tourname

A tour leg table

Tournum
Lugnum (which leg of the tour it is)
RouteNum

A route table (assuming routes are directional)
RouteNum (PK)
Route details
StartNodeNum
EndNodeNum

A Node table
Nodenum (PK)
NodeDescription

 

[John@home]

On Wed, 19 Mar 2008 06:04:03 -0700, =?Utf-8?B?RnJlZA==?=

Ah yes, I see/understand it now.

I need the tourleg-table as a link-table between the tour-table and
the route-table. (m:n)
Where the legnumber defines the 'place' in the route-sequence.

Many thanks for putting me 'on-route' again ...

Regards,
Jos

 

[John@home]

For your mission as you described it, nodes are a part of the definition of a
route, but are otherwise irrelevant. A tour is simple a sequence of routes.
Here's and idea:

A tour table:

Tournum (PK)
Tourname

A tour leg table

Tournum
Lugnum (which leg of the tour it is)
RouteNum

A route table (assuming routes are directional)
RouteNum (PK)
Route details
StartNodeNum
EndNodeNum

A Node table
Nodenum (PK)
NodeDescription

 

[Fred]

Happy travelin'

On Wed, 19 Mar 2008 06:04:03 -0700, =?Utf-8?B?RnJlZA==?=

Ah yes, I see/understand it now.

I need the tourleg-table as a link-table between the tour-table and
the route-table. (m:n)
Where the legnumber defines the 'place' in the route-sequence.

Many thanks for putting me 'on-route' again ...

Regards,
Jos