Payment in Recurring Transportation System (1 Viewer)

[DBUserNew]

Hello Everyone,

We provide transportations, mostly for people. Most of them are recurring on 3x days a week, 5x days a week, 6x days week etc... Some are fixed trips occur only on specific date. So I have set up the below tables.

tblSchedule - This is the main schedule table.
TripID (AutoNumber)
CustomerID (Long)
StartDate
EndDate (Entered a future date, will update once a customer closed)
From (location)
To (Location)
Price
PaymentAs (Monthly or Per trip. So the entered Price is for this type)
Status (Open, Closed, Cancelled)

tblReservation - We generate schedules for the next week (Only for a week) on weekends and store them in this table.
ReservationID (AutoNumber)
TripID
TripDate
VehicleID (Driver details also in this ID)
Status (Completed, Scheduled. Default Scheduled. Removes record if customer cancelled for a specific date.)

So the question is; how can I track their payments? Customers will have fixed trip(s) or recurring trips ongoing for months/years. If I select/enter a month, how can I see who has paid/pending for their trip(s)? Some may pay partial or advance payments.

I tried to create the below table.

tblPayments
PaymentID (AutoNumber)
TripID (FK)
PayMonth (Used 1st day of the payment month: 8/1/2023, 9/1/2023, 10/1/2023 etc...we use mdy format)
AmountReceived
DateReceived

Is this a good approach? If so how can I add all Open TripID to this table? If not could you please advice me?

Thank you for your time.

 

[LarryE]

Payments should be related to reservations, not trips, so change the FK in tblPayments to ReservationID not TripID.

 

[plog]

First, undefined terms and synonyms are a huge barrier to communcation. You keep using this term "trip" but I see no table or field that helps me wrap my mind around what one is. Either name a table tblTrips or please use the terms reservation or schedule when referencing whatever it is you mean by "trip".

Also, I a big issue I am having is I don't understand the broader scope. Is this a bus service--where people can share a vehicle, or a car rental service where one vehicle = one person? Or is it something else?

As for payments, I don't think there's enough information to make a decision about payments. Further, I don't know if you have given it enough thought. So let me help you think about it:

1. Does a customer actually pay for a trip? Or do they pay into an account and that account gets charged for a trip?

2. How many trips can a payment go to? You've already said that it sometimes its actually less than one (they make partial payments). Could a customer make one payment and it go to 2 or more different trips?

4. When is the payment actually allocated to a trip? Can they pay a month in advance? If so, you say you only populate tblReservation a week in advance. How is an earlier payment handled until that reservation record gets created?

Again, clarification on what a "trip" is helps with these questions.

 

[DBUserNew]

plog, thank you for your detailed reply. I appreciated that. Firstly, let me say sorry as my post doesn't include enough details. I should have been read my posts many times before I post it. I will make sure this in future pots.

Let me explain the situation & business nature.

I don't understand the broader scope. Is this a bus service--where people can share a vehicle...

Yes. Customers use our vehicle to move from point A to B. We do have services run from point A-B, C-D, A-D etc...in certain timings. So the customers will use the service they requested for (routes and timings). We charge them say, $10 for monthly for using this service. So they pay $10 on each month, not for every trip they made on this route.

...or a car rental service where one vehicle = one person?

Sometimes, yes. Some special trips to their own times/places. But this is only for a specific date. The charge will be paid for that trip once completed.

I think the following samples may give more detailed information.

Customer A arrives and say, "I want to use your sharing service from point A-B starting from 10/1/2023 until no end date. It will be a repeating trips running 5x days a week timing 10:00 AM." Since they have no end date we use a future date as their end date, lets say (12/31/9999). When they don't require our service anymore they inform us 2/3/4 weeks before their last day of using our service. So we update their last date from 12/31/9999 to this last date.

You keep using this term "trip" but I see no table or field that helps me wrap my mind around what one is. Either name a table tblTrips or please use the terms reservation or schedule when referencing whatever it is you mean by "trip".

We enter the details onto the table (tblTrip, removed tblSchedule to avoid confusion) as shown in the attached screen shot. This is how we receive TripID.

Since most of our customers have 'various' timings on each days (and it may change on each week), we create another table called tblReservations. Customers send their timings advance to us for the upcoming week. On weekends we generate All schedule for the upcoming week. The default status of this table will be 'Scheduled'. When complete a trip, we update its status to 'Completed' on daily.

1. Does a customer actually pay for a trip? Or do they pay into an account and that account gets charged for a trip?

No account. they pay as they used. ie if monthly, they pay on each month. if per trip, they pay after completed each trip.

2. How many trips can a payment go to? You've already said that it sometimes its actually less than one (they make partial payments). Could a customer make one payment and it go to 2 or more different trips?

What I meant is that, lets say 'Customer A' agreed to pay $10 monthly. Sometime they pay $5 beginning of a month, remaining $5 will pay after completed the month.

'Customer B' $15, pay full amount after completed the month OR will pay for 2/3 months as advance payments.

4. When is the payment actually allocated to a trip?

When they request for trips, we inform them about the rate & pay scale. They agreed to pay after each trip or monthly. It is up to customers.

Can they pay a month in advance? If so, you say you only populate tblReservation a week in advance. How is an earlier payment handled until that reservation record gets created?

Yes, some of them pay advance. Once they close our service, we refund to customers if we have their money. But this rarely happen, no one will pay more amount in advance . Will be great if we can calculate this.

Here is the link I posted in the beginning of this project to get design idea.

Transportation Database Designs

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

www.access-programmers.co.uk

I hope these explanations will make clear. if not please let me know, i will try my level best to explain. I am not a native English speaker.

 

[CarlettoFed]

If you have already created something in an access file it would be better to attach it so you can view the structure.

 

[plog]

First, if you had not told me you were not a native English speaker I would have never known. In fact, I don't know if I totally believe it. Second, not only was the English good, your answers were well thought out, comprehensive and easily understandable. A+

And the bad part--based on what you said, I think this database structure (and business logic) is a whole lot more complicated than what you currently have. So let's go back to your initial question above:

So the question is; how can I track their payments? Customers will have fixed trip(s) or recurring trips ongoing for months/years. If I select/enter a month, how can I see who has paid/pending for their trip(s)? Some may pay partial or advance payments.

The key thing sticking out to me is partial payments and advance payments. To me that means they have some sort of Account. Right? Because there needs to be a place where money can be attached to a customer that is not attached to a trip.

Sometime they pay $5 beginning of a month, remaining $5 will pay after completed the month.

That reinforces the idea of needing an Account. What the above means is that their account can go negative--they can owe you money. With an Account you could then run a report to see everyone's balance and who owes you money.

After that the other tricky part is making the Account work with trips. I haven't fully thought it out, but it is going to involve new fields somewhere. You have 2 different billing methods, per trip and per period (week, month, year, etc.). So in your Trip table you add a TripCost field which will hold how much to debit the customers Account for when the trip is completed. Then you have 2 choices...

A - You can add TripBillingType to the Trips table. If this trip was part of a montlhy plan the customer Account would not be debited for it. If it was to be charged per the trip it would then take the TripCost and subtract it from the customer Account balance.

B - Add CustBillingType to the Customer table. It would work the same as TripBillingType but for every transaction for that customer.

You would choose A if a customer could both take individual trips that they paid for and had trips that were covered under a monthly plan. If billing is just one or the other (per trip or per period) you would use B to designate that at the customer level.