The solution to this would be to have each tour route tied to an ordered list of locations. For instance, assume a simple tour table:
CREATE TABLE tours (
tour_id SMALLINT UNSIGNED NOT NULL
, name VARCHAR(50) NOT NULL
, PRIMARY KEY (tour_id)
);
then a "key map" table to tie the locations with the tour table:
CREATE TABLE tour_routes (
tour SMALLINT UNSIGNED NOT NULL
, location CHAR(1) NOT NULL
, order_in_route SMALLINT UNSIGNED NOT NULL
, PRIMARY KEY (tour, location)
);
you would then determine the order of each tour via the key map table:
SELECT l.location, l.notes
FROM locations l
INNER JOIN tour_routes tr
ON tr.tour = 123
AND l.location = tr.location
ORDER BY tr.order_in_route;
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com