A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How Do I Normalize This?



 
 
Thread Tools Display Modes
  #1  
Old August 19th, 2007, 06:06 AM posted to microsoft.public.access.tablesdbdesign
[email protected]
external usenet poster
 
Posts: 7
Default How Do I Normalize This?

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 he 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

  #2  
Old August 20th, 2007, 01:44 PM posted to microsoft.public.access.tablesdbdesign
scubadiver
external usenet poster
 
Posts: 1,673
Default How Do I Normalize This?


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



--

The 11th day of every month:

http://truthaction.org/forum/index.php


" wrote:

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 he 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


  #3  
Old August 20th, 2007, 06:19 PM posted to microsoft.public.access.tablesdbdesign
Roger Carlson
external usenet poster
 
Posts: 824
Default How Do I Normalize This?

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/...UBED1=ACCESS-L

wrote in message
ups.com...
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 he 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



  #4  
Old August 21st, 2007, 01:37 AM posted to microsoft.public.access.tablesdbdesign
Michael Gramelspacher
external usenet poster
 
Posts: 482
Default How Do I Normalize This?

In article . com,
says...
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 he
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:21 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.