Transportation Database Designs

[DBUserNew]

Hello Everyone,

I am not an expert in Access, but I have basic knowledge about Access and normalization. I always read articles to improve data modelling. But no luck. I am currently preparing a db for our transportation system.

Business: Transport (Individuals, Company employees, School trips or special events)
Expected Outcome:
1). To see upcoming/completed schedules (There are various trips Morning/Evening/Nights).
2). Track vehicle details (includes maintenance, oil change & other service dues etc...)
3). Track customers payments & history (Pending/completed). Which months they have pending/completed.
4). Track drivers salary (includes advance payments if any), their vacations/off days. So we can see on which dates they are available.
5). Then of course overall financial conditions.

Here is the table I plan:

tblCustomer
CustomerID
LastName (I use Company as last name if Type is company)
FirstName
ContactNumber
CustType (Individual, Company, Student or Special events etc...)
Status (active/Inactive)

Then I plan to design trips as orders; tblOrder & tblOrderDetails

tblOrder
OrderID
CustomerID
OrderDate
Status

tblOrderDetails
OrderID
VehicleID
Shift
TripDate
etc...

I don't know I totally lost here. Because some trips we have to complete daily, some not required on weekends/holidays, some only need on certain dates (special trip), some need on certain weekday (ie: Tuesdays, Sundays etc...). Some individuals send us their schedules for the upcoming weeks (morning/evening/nights) so how should we record this and get their schedule?

Vehicle table; Some vehicles are rented/leased not owned by us. So how to track their availability.

Trips for Individual, company or school have a fixed charge for a month. Other special trip will charge for that trip.

Any help would be greatly appreciated.
Raj

 

[jdraw]

There are a number of Transportation related data models from Barry Williams' former site DatabaseAnswers.org located here on wayback machine. They may help with part of your model. You might want to break your project into parts.

Here are some potential entities based on your post:
Vehicles
Drivers
Salaries
Shifts
Customers/Clients
Trips
Routes
Schedules
VehicleMaintenance
Invoices
Payments

 

[DBUserNew]

Thank you jdraw for your reply. Let me go through his website and your suggested tables. I will be back if I stuck or need assistance .
What I was trouble to design is that, the trip frequency. Some on daily in different shifts, some on weekends or No weekends, some on certain weekday etc...

 

[pbaldy]

What I was trouble to design is that, the trip frequency. Some on daily in different shifts, some on weekends or No weekends, some on certain weekday etc...

I handled that with a "recurring" table in addition to the actual reservations table (a record for each trip). On a set basis, say daily, I would query the recurring table and record reservations from that table into the reservations table for the next day. The recurring table had yes/no fields for each day of the week and date fields for the effective dates.

 

[Pat Hartman]

tblOrder needs a few more columns and you need a schedule table which your code will use to generate "next week's" schedule.
1.ScheduleType (Fixed, Recurring)
2.ScheduleStartDate (Date on which a Recurring schedule starts.
3.StatusDate (lets you put a future end data on a schedule)

Fixed schedules can be a single trip or multiple trips as specified by the customer. Recurring trips are calculated periodically and tblOrderDetails rows are added based on the details of tblOrderRecurringSchedule as constrained by the start and end dates in tblOrder. If there is a certain vehicle requirement for a specific trip, it goes in both tblOrderRecurringSchedule and tblOrderDetails. Rather than a specific vehicle, you should use a vehicle type so you are not tied to a specific vehicleID. Then as your code is generating the next "schedule", you choose the next available instance of the type.

You need reports that show planned trips by day for a specific period.

 

[DBUserNew]

Thank you all for your valuable inputs. Let me put what I have learned from yours & see we are on same page.

Consider we are generating schedule on Fri, Sat or Sun for the next week.

tblTrip (as Order table)
TripID
CustomerID
ScheduleType (Fixed, Recurring)
StartDate
EndDate (Leave this column blank if no end date. So use SELECT query with NZ([EndDate],dteSunday+7)<=dteSunday+7 to get next week schedule. Put end date when customer leaves?)
StatusID (Open, Completed, Cancelled)
PayFrequencyID (Per Trip, Monthly, Weekly etc...)
Price (charge for PayFrequencyID)
LocationIDFrom
LocationIDTo (Same tblLocation relationship)
VehicleTypeID (Bus, SUV, Car etc...)

tblReservations (Next week schedule)
ReservationID
TripID
TimeID (tblTime with TimeID, TimeValue - 06:00 AM, 07:00 PM etc... DisplayAs - Morning, Evening, Night etc...)
StatusID (Completed, Scheduled)
VehicleID
TripDate

tblTripSchedule (Copy of tblReservations to generate next week schedule)
TripScheduleID
TripID
TimeID
StatusID (can be hide default as Scheduled)
VehicleID (Select vehicle based on VehicleTypeID)
DayID (tblDay with 1-Mon...7-Sat)
RunNextWeek (1-Yes, 0-No)
TripDate (So we can put the date of current week's Sunday (dteSunday) in a form, then use SELECT query to get all 'Yes' values for the next week. An UPDATE query to add as dteSunday+DayID to get corresponding dates. Finally INSERT query to add Yes values to tblReservations).

Does this make sense? Is this a normalized layout?

Here come another issue to track payments on each months. Should I add a new TripID for each recurring trip on each months? eg:

1). CustA has daily schedule without end date. So close their TripID with current month's end date as EndDate. At the same time insert a new TripID with their details first day of the next month as StartDate.

2). CustB wouldn't like to continue next month onwards, then close their TripID as above and skip the insertion for the next month.

What is the best and practical way to track their payments?. So we can see which months they have paid/pending. We can generate their trip history if someone ask for invoice.

Sorry for the long or confusing post. I really appreciate your time and advice.

Raj

 

[Pat Hartman]

DayID (tblDay with 1-Mon...7-Sat)
RunNextWeek (1-Yes, 0-No)

Day can be calculated from date so there is no need to store it.
RunNextWeek isn't necessary. Either the schedule is specific or it is recurring. For the recurring schedules, I'm not sure whether it is better to cancel them after the fact or to first generate a list of customers and deselect from that list. In any event, this isn't the table where the flag would go since this represents the generated schedule. You should never generate more than a week ahead of time because you don't want to end up with a lot of records to delete. AND, you won't know how many to generate anyway.

A record is generated in tblTripSchedule for each trip. Use that table for billing.

1/2. I would not generate a new trip record for each month. When the transportation is no longer needed, just add an end date. That way, the query that generates the recurring records that get added to tblTripSchedule every week will not be selected. Selection is a positive action rather than a negative one.

You use the tblTripSchedule for all your historical reporting and invoicing. Keep in mind that there could be future dates in here. A customer might have an outing scheduled for October 15. The record is created when the order is entered so it might sit there for a few weeks before it ends up on "next" week's schedule.

When you are estimating future usage for planning purposes, you look at the Trip table and calculate usage based on the recurring schedule. and then look in the past at the one-off's in the tblTripSchedule. You can calculate an average by month and use that for your projection.