How to design this "bus stations" database?

Question

I want to design a database about bus stations. There're about 60 buses in the city, each of them contains these informations:

• BusID
• BusName
• Lists of stations in the way (forward and back)

This database must be efficient in searching, for example, when user want to list buses which are go through A and B stations, it must run quickly.

In my first thought, I want to put stations in a seperate table, includes StationId and Station, and then list of stations will contains those StationIds. I guest it may work, but not sure that it's efficient.

How can I design this database?

Thank you very much.

Answer 1

Have you looked at Database Answers to see if there is a schema that fits your requirements?

Answer 2

(This sounds like homework, so I won't give a full answer.)

It seems like you just need a many-to-many relationship between buses and stops using 3 tables. A query with two inner joins will give you the buses that stop at two specific stops.

Answer 3

hi, what you have thought is good, in some cases it may or may not be efficient. I think that yo u should create tables as table1(BusID, BusName) table 2(Station List, Bus Id). I think this would would help. And try to use joins between these two tables to get the result. One more thing if possible try to normalize the tables that would help you.

Answer 4

I'd go for 3 tables :

bus stations bus_stations

"bus" for what the name stands for, "stations" for the station id's and names, and "bus_stations" to connnect those other 2 tables, wich would have bus_id, station_id_from station_id_to

This is probably more complex that you really need, but if, in the furure, you need to know the full trajectory of a bus, and also, from witch station one bus comes when it goes to "B station", will be usefull.

60 buses will not make that much impact in performance though.

Answer 5

I'd hack it.

bus_id          int
path            varchar(max)

If a bus goes through the following stations (in this order):

01
03
09
17
28

Then I'd put in a record where path was set to

'-01-03-09-17-28-'

When someone wants to find a bus to get from station 03 to 28, then my select statement is

select * from buses where path like '%-03-%-28-%'

Not scalable, not elegant, but dead simple and won't churn through tables like mad when trying to find a route. Of course, it only works if there's a single bus that goes through the two stations in question.

Answer 6

Some random thoughts based on travel on London buses In My Youth, because this could be quite complex I think.

You might need entities for the following:

1. Bus -- the physical entity, with a particular model (ie. particular seating capacity and disabled access, and dimensions etc) and VIN.
2. Bus stop -- the location at which a bus stops. Usually bus stops come in pairs, one for each side of the road, but sometimes they are on a one-way road.
3. Route -- a sequence of bus stops and the route between them (multiple possible roads exist). Sometimes buses do not run the entire route, or skip stops (fast service). Is a route just one direction, or is it both? Maybe a route is actually a loop, not a there-and-back.
4. Service -- a bus following a certain route
5. Scheduled Run -- an event when a bus on a particular service follows a particular route. It starts at some part of the route, ends at another part, and maybe skips certain stops (see 3).
6. Actual Run -- a particular bus followed a particular scheduled run. What time did it start, what time did it get to particular stops, how many people got on and off, what kind of ticket did they have?

Answer 7

I had to solve this problem and I used this :

Line - number - name

Station - name - latitude - longitude - is_terminal

Holiday - date - description

Route - line_id - from_terminal : station_id - to_terminal : station_id

Route schedule - route_id - is_holiday_schedule - starting_at

Route stop - route_id - station_id - enlapsed_time_from_start : in minutes

Does it looks good for you ?