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.

 

[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