0

I have a database(mySQL) with a schedule for a bus. I want to be able to display the schedule based on some user inputs: route, day, and time. The bus makes at least 13 runs around the city in per day. The structure is set up as:

-Select Route(2 diff routes)
-Select Day(2 set of day, Sun-Wed & Thur-Sat)
-Select Time(atLeast 13 runs per day) = Show Schedule

My table structure is:
p_id,   route     day    run#    stop     time
  1     routeA    m-w      1     stop1   12:00PM
  1     routeA    m-w      1     stop2   12:10PM

..and so on

I do have a functioning demo, however, it is very inefficient. I query the db for every possible run. I would like to avoid doing this.

Could anyone give me some tips to make this more efficient? OR show me some examples?

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Jebish
  • 25
  • 1
  • 3
  • This data structure is horribly unnormalised but given that it is very small you may be able to work with it. It would help if you add a specific example to your question in the form of - given this data and this user input I need to return this data. How many records in this table and is it likely to grow considerably? – user1191247 Apr 11 '12 at 13:09
  • @nnichols The bus makes 13 trips a day, 7 days a week. The way I have the db set up right now, the number of rows are 200+. So, let say for today, Select Route A, day: wednesday, run#3 = Display the schedule for route A, on Wednesday, for run#3. I do realize my db is setup very poorly, and am willing to change it. – Jebish Apr 11 '12 at 13:22

1 Answers1

0

If you google for "bus timetable schema design" you will find lots of similar questions and many different solutions depending on the specific use case. Here is one similar question asked on here - bus timetable using SQL.

The first thing would be to normalise your data structure. There are many different approaches to this but a starting point would be something like -

routes(route_id, bus_no, route_name)
stops(stop_id, stop_name, lat/long, etc)
schedule(schedule_id, route_id, stop_id, arrive, depart)

You should do some searching and look to see the different use cases supported and how they relate to your specific scenario. The above example is only a crude example. It can be broken down further depending on the data being used. You may only want to store the time between stops in one table and then a start time for the route in another.

Community
  • 1
  • 1
user1191247
  • 10,808
  • 2
  • 22
  • 32
  • Thanks for your help. After doing some searching I was able to write a dynamic query, which made it a lot simpler. – Jebish Apr 11 '12 at 20:26