Novice / Intermediate user seeks help in structuring data / tables

[Jeff Sabarese]

Data Type: TimeTable for Bus Route
Current Number of Tables per Route: 2

Current MySQL Table Structure:

(TableA): Route_TimeTable: Several fields named by "Bus-Stop ID" - each field containing several rows indicating at which time of day a bus makes a stop at that destination

(TableB): Route_StopsTable: 10 Fields of Unique data for each "Bus-Stop ID". One of these fields contains, in as many rows, the field names of the former table, Route_TimeTable -- the BusStop-ID, which is TableB's UniqueIdentifier Field

I wish to create a relational link between my "StopsTable" and my "TimeTable" by way of the Bus-Stop-ID.

Because the Bus-Stop-ID's are the Field Names of the TimeTable, while they reside in field-cells of the StopsTable-- where StopsTable.BusStopID's several rows contain identical data to those several Field-Names in TimeTable-- i've confused myself on how I should handle the field names of (TableA) TimeTable, vs the cell contents of (TableB) RouteTable.

i realize this might be a lot to digest. i hope you are able to understand what i've described.

Considering the structure of the two tables, if I want to locate TimeTable (TableA) row info (eg. the time of day Bus_RouteA arrives at BusStopID._123)
based on a StopsTable (TableB) query result (eg. query appropriate field of StopsTable for text "Super Grocery" and "Main Street" for planning a bus-ride), how should I:
a.) Re-structure my TimeTable to make it query-friendly
b.) code the mysql_query / php script to pull this info based on the FieldNames (the BusStopID) of the TimeTable?


(phew... that was hard work!)

-- -- -- --
Use CSS [ content:" ?? " ] for Unicode + Control Chars (e.g. newline, etc)... Gain understanding of Codepage, Charset, Encoding, Keymapping... Generate CSS and JavaScript on-the-fly with PHP... Tales from the Communications Dept @ NoviceNotes.Net



Edited 3 time(s). Last edit at 12/07/2005 05:07PM by Jeff Sabarese.