1. No Profile Picture
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    The Emerald City
    Rep Power

    Question Database table structure recommendation

    I would like to get some ideas on how to structure the tables of a database i'm creating. I am storing time schedules for bus routes. Here is a typical bus schedule for a single bus:
    To DOWNTOWN (Weekday):
         NE 136th     NE 125th      NE 80th      NE 65th    15th Av NE
             &            &            &            &            &
        17th Av NE   15th Av NE   15th Av NE   15th Av NE     NE 45th
         4:58am       5:05am       5:12am       5:15am       5:20am
         5:25am       5:33am       5:41am       5:44am       5:49am
         5:55am       6:03am       6:11am       6:14am       6:19am
     E   6:22am       6:31am       6:39am       6:43am       6:48am
     E    ---          ---          ---         7:05am       7:10am
     E   6:51am       7:00am       7:09am       7:13am       7:18am
     E    ---          ---          ---         7:35amB      7:40am
     E   7:19am       7:28am       7:39am       7:43am       7:48am
    There would also be a corresponding 'From DOWNTOWN (Weekday)'. For some buses there is an 'EXPRESS to DOWNTOWN' where the stop locations may or may not overlap (express busses are sometimes routed differently) and the times do not necessarily overlap either. There are also '(Saturday)' and '(Sunday)' schedules for most (but not all) buses. Stop locations can overlap across buses (in bus transfer/hub areas)

    The queries would be as follows:
    - given a bus, retrieve the 'directions' of the bus (to downtown, from downtown, express)
    - given a bus and a direction retrieve all the stop names.
    - given bus, direction, and a stop retrieve all times for that stop (or retrieve all times later than right now, or later than a given time)

    This is my first attempt at creating a database of any considerable complexity but here's what I came up with so far

    a Buses table with Busid and Busnum fields
    a stopname table with stopnameid and stopname (the street intersection)
    a direction table with directionid, direction name
    a stopchedule table with stopid, stoptime
    a busstop table with stopid, busid, directionid, and stopnameid

    something like that. I would appreciate any suggestions on this.

    Last edited by justice41; February 15th, 2003 at 01:58 PM.

IMN logo majestic logo threadwatch logo seochat tools logo