Storing route between two locations in Database

Can some one please tell me, how to design a database that can store

• Starting location ( Name of city)
• Places between ( Name of cities b/w Starting location and Ending location , db should be able to handle any number of places )
• Ending location ( Name of city )

Thanks !

Edit:

For example I have to Save The places between Chicago and New York. I am not sure how to design a db that can Hold

• The starting Place ( Chicago )
• places in between ( Iam mainly confused about dat开发者_如何转开发atype here )
• Ending place ( New York)

---

I'd have three tables: locations, routes and stops

locations:
    location_id | name

routes:
    route_id | description

stops:
    stop_id | location_id (fk) | route_id (fk) | stop_number

The stops-table is the crosslink table. On inserting locations, have the application set the correct stopnumber. You can then get your route with a simple

select * from routes join stops join location
where route_id = ...    
order by stop_number

If you ever need to insert a location to a route, use two queries:

update stops set stop_number = stop_number + 1 
where route_id = ... 
   and stop_number > 'inserted_stop_number';

insert into stops (route_id, location_id, stop_number) values // etc

In theory a linked list (where you store a reference to the next stop on the route) is conceptually closer to reality, but in the standard Mysql-options it is very difficult to get a list from data that is stored that way, so I would advise against it and keep it simple for yourself.

If you really need more power on creating and using linked nodes in a route, you could consider learning about: http://openquery.com/graph/doc but for simple start-to-finish routes that don't have to be recalculated all the time, the above will most likely suffice.

---

You should have another table you can reference to, which will have a one-to-many relationship with all the start, transit, and finish records, so you can "bind" the whole "collection" of records together.

trips

trip_id | ...

locations

location_id | trip_id | location_type | location

In the location_type column you can have some distinctive flags which will tell you if a record of location denotes starting, transit, or finish. For example, start could be a flag of 0, 1 could be a flag of transit, and 2 could be a flag of finish.

---

Untested

CREATE TABLE Location (
    ID INT UNSIGNED AUTO_INCREMENT,
    Name VARCHAR(50),
    PRIMARY KEY (ID)
) ENGINE=INNODB CHARACTER SET utf8

CREATE TABLE Trip (
    ID INT UNSIGNED AUTO_INCREMENT,
    Name VARCHAR(50),
    StartLocation INT UNSIGNED,
    EndLocation INT UNSIGNED,
    PRIMARY KEY (ID),
    CONSTRAINT Constr_Trip_StartLocation_fk
        FOREIGN KEY Trip_StartLocation_fk (StartLocation)
        REFERENCES Location (ID),
    CONSTRAINT Constr_Trip_EndLocation_fk
        FOREIGN KEY Trip_EndLocation_fk (EndLocation)
        REFERENCES Location (ID)
) ENGINE=INNODB CHARACTER SET utf8

CREATE TABLE TripStopoff (
    Trip INT UNSIGNED,
    StopoffNumber INT UNSIGNED,
    Location INT UNSIGNED,
    PRIMARY KEY (Trip, StopoffNumber),
    CONSTRAINT Constr_TripStopoff_Trip_fk
        FOREIGN KEY TripStopoff_Trip_fk (Trip)
        REFERENCES Trip (ID),
    CONSTRAINT Constr_TripStopoff_Location_fk
        FOREIGN KEY TripStopoff_Location_fk (Location)
        REFERENCES Location (ID)
) ENGINE=INNODB CHARACTER SET ascii

INSERT INTO
    Location
    (ID, Name)
VALUES
    (1, 'Chicago'),
    (2, 'Manoning'),
    (3, 'Jersey City'),
    (4, 'Newark'),
    (5, 'New York')

INSERT INTO
    Trip
    (ID, Name, StartLocation, EndLocation)
VALUES
    (1, 'My trip', 1, 5)

INSERT INTO
    TripStopoff
    (Trip, StopoffNumber, Location)
VALUES
    (1, 1, 2),
    (1, 2, 3),
    (1, 3, 4)