Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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: enter image description here

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 datatype here )
  • Ending place ( New York)
like image 487
Vamsi Krishna B Avatar asked Oct 19 '25 06:10

Vamsi Krishna B


1 Answers

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.

like image 79
Inca Avatar answered Oct 21 '25 21:10

Inca



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!