Can some one please tell me, how to design a database that can store
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With