How Do I Normalize This?

[pepandmax]

Hi everyone,

I'm learning Access by creating a schedule database for a public
transit agency. I have a big ol' spreadsheet of trip data. To see
the sample, go here: http://pepandmax.googlepages.com/Sample.xls

So, what do all of the fields mean?
--TripID is a unique identifier for each trip.
--Work Run is an assignment for a driver (the driver doesn't stay on
the same route all day).
--WROrder is used to sort the work run chronologically and other stuff
(not related to database).
--Day is the days that the trip occurs (weekdays, weekends, Friday
only, etc.)
--Block # is the bus that makes the trip. Sometime there is more than
one block for a work run, for example, if the driver comes back to the
garage for lunch and then leaves on a different bus.
--Route # is self explanatory!
--Trip # is related to the route. 1 is the first trip made on that
route, etc. This has to be manually entered, because sometimes a trip
with a lower number happens after a trip with a higher number (don't
ask, it's complicated).
--Stop01Time, Stop02Time, etc. are the scheduled stop times. There is
another table that shows the stop name for Stop01, Stop02, etc. for
each route.

What is the best way to normalize this table into multiple tables? I
hate the Stop01, Stop02, etc. set up, because it's so limited... like
someone wise once said, "Columns are expensive; records are cheap."

Currently I have normalized the tables like this:

tblStopTimes:
StopTimeID (PK)
TripID
StopSeq (the order of the stops, i.e. 01, 02, 03, etc)
StopID (related to a table with stop names mentioned above)
StopTime

tblTrips:
TripID (PK)
Route#
WorkRun#
Block#
DaysID

I'm having difficulty making forms and reports with the table
normalized like this, but I'm not sure what my other options are.

Thanks in advance for reading and for any help you might be able to
offer.

Nicole

 

[scubadiver]

I had a look earlier on and this is quite a challenge! This is what I think.

tables:

tble_Bus
tble_Route
tble_Driver
tble_Day
tble_Stop

Each bus can have many routes
Each route can have many buses
Each bus can have many drivers
Each driver can have many buses
Each route can be on many days
Each Day can have many routes
Each route can have many stops
Each stop can be on many routes

 

[Roger Carlson]

I'm always hesitant to propose a database structure for people based on what
they post because there my be other business rules they're not including
because they haven't even considered them.

I suggest you go to my website
http://www.rogersaccesslibrary.com/TutorialsDesign.html and download some of
the normalization tutorials and work through them. They'll give you a feel
for the *process* of creating a good database design that you can hopefully
generalize to your own database.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Michael Gramelspacher]

Hi everyone,

I'm learning Access by creating a schedule database for a public
transit agency. I have a big ol' spreadsheet of trip data. To see
the sample, go here: http://pepandmax.googlepages.com/Sample.xls

So, what do all of the fields mean?
--TripID is a unique identifier for each trip.
--Work Run is an assignment for a driver (the driver doesn't stay on
the same route all day).
--WROrder is used to sort the work run chronologically and other stuff
(not related to database).
--Day is the days that the trip occurs (weekdays, weekends, Friday
only, etc.)
--Block # is the bus that makes the trip. Sometime there is more than
one block for a work run, for example, if the driver comes back to the
garage for lunch and then leaves on a different bus.
--Route # is self explanatory!
--Trip # is related to the route. 1 is the first trip made on that
route, etc. This has to be manually entered, because sometimes a trip
with a lower number happens after a trip with a higher number (don't
ask, it's complicated).
--Stop01Time, Stop02Time, etc. are the scheduled stop times. There is
another table that shows the stop name for Stop01, Stop02, etc. for
each route.

What is the best way to normalize this table into multiple tables? I
hate the Stop01, Stop02, etc. set up, because it's so limited... like
someone wise once said, "Columns are expensive; records are cheap."

Currently I have normalized the tables like this:

tblStopTimes:
StopTimeID (PK)
TripID
StopSeq (the order of the stops, i.e. 01, 02, 03, etc)
StopID (related to a table with stop names mentioned above)
StopTime

tblTrips:
TripID (PK)
Route#
WorkRun#
Block#
DaysID

I'm having difficulty making forms and reports with the table
normalized like this, but I'm not sure what my other options are.

Thanks in advance for reading and for any help you might be able to

I am just a hobbist, but I will take a stab at the problem.

Create a new database.
Create a new module.
copy this into the module and save it.
In the Immediate Window type
call CreateTables and press enter at the end of that line.
The tables should be there. Just arrange them in the
Relationship Window.

Sub CreateTables()

With CurrentProject.Connection

.Execute _
"CREATE TABLE Drivers" & _
" (driver_nbr COUNTER NOT NULL" & _
", driver_name VARCHAR (30) NOT NULL" & _
", license_nbr VARCHAR (15) NOT NULL" & _
", PRIMARY KEY (driver_nbr));"

.Execute _
"CREATE TABLE Busses" & _
" (bus_nbr INTEGER NOT NULL" & _
", bus_capacity INTEGER NOT NULL" & _
", PRIMARY KEY (bus_nbr));"

.Execute _
"CREATE TABLE BusDrivers" & _
" (bus_nbr INTEGER NOT NULL" & _
" REFERENCES Busses (bus_nbr)" & _
", driver_nbr INTEGER NOT NULL" & _
" REFERENCES Drivers (driver_nbr)" & _
", datetime_out DATETIME NOT NULL" & _
", datetime_in DATETIME NULL" & _
", PRIMARY KEY (bus_nbr, driver_nbr, datetime_out));"

.Execute _
"CREATE TABLE Routes" & _
" (route_nbr INTEGER NOT NULL" & _
", route_name VARCHAR (30) NOT NULL" & _
", PRIMARY KEY (route_nbr));"

.Execute _
"CREATE TABLE Stops" & _
" (stop_nbr INTEGER NOT NULL" & _
", stop_name VARCHAR (30) NOT NULL" & _
", stop_location VARCHAR (100) NOT NULL" & _
", PRIMARY KEY (stop_nbr));"

.Execute _
"CREATE TABLE RouteStops" & _
" (route_nbr INTEGER NOT NULL" & _
" REFERENCES Routes (route_nbr)" & _
", stop_nbr INTEGER NOT NULL" & _
" REFERENCES Stops (stop_nbr)" & _
", PRIMARY KEY (route_nbr, stop_nbr));"

.Execute _
"CREATE TABLE BusDriverRoutes" & _
" (bus_nbr INTEGER NOT NULL" & _
", driver_nbr INTEGER NOT NULL" & _
", datetime_out DATETIME NOT NULL" & _
", CONSTRAINT fk_bus_drivers" & _
" FOREIGN KEY (bus_nbr,driver_nbr, datetime_out)" & _
" REFERENCES BusDrivers (bus_nbr,driver_nbr" & _
", datetime_out)" & _
", route_nbr INTEGER NOT NULL" & _
" REFERENCES Routes (route_nbr)" & _
", begin_stop_nbr INTEGER NOT NULL" & _
", route_direction_begin INTEGER NOT NULL" & _
", end_stop_nbr INTEGER NULL" & _
", route_direction_end INTEGER NULL" & _
", PRIMARY KEY (bus_nbr, driver_nbr" & _
", datetime_out, route_nbr));"

.Execute _
"CREATE TABLE RouteSchedules" & _
" (route_nbr INTEGER NOT NULL" & _
", stop_nbr INTEGER NOT NULL" & _
", CONSTRAINT fk_route_schedules_route_stop" & _
" FOREIGN KEY (route_nbr, stop_nbr)" & _
" REFERENCES RouteStops (route_nbr, stop_nbr)" & _
", route_direction INTEGER NOT NULL" & _
", stop_sequence INTEGER NOT NULL" & _
", weekday_nbr INTEGER NOT NULL" & _
", arrive_time DATETIME NOT NULL" & _
", depart_time DATETIME NOT NULL" & _
", UNIQUE (route_nbr,route_direction,stop_sequence)" & _
", PRIMARY KEY (route_nbr, stop_nbr" & _
", route_direction, stop_sequence, arrive_time));"

End With

End Sub