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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|