billing database design

[Larry Mennitt]

I have an old Paradox application that I wish to replace with an Access
database. I have been able to duplicate all functions but I would like to
improve on the old Paradox design. It is for a flying club that rents
aircraft to its members who are billed monthly.

Currently, a statement with rentals, payments, other credits, monthly dues
and balance carried over from the previous month are printed with a
calculated new balance for each member.

Then the new balance replaces the old balance in a field in each member's
record for use in the next month's billing (in spite of the rule to never
save a calculated field in a table). All sales, payments and credits are
saved to an archive table and then the tables are emptied - ready to start
over for the next month's billing.

I have never liked this scheme because it makes it difficult to create a
duplicate statement from a previous month, display running totals of
rentals, age balances or generate history graphs.

A partial solution has been to save a copy of the whole application each
month before emptying the tables. I have over 10 years of these copies.

Is there a better way?

Larry Mennitt

 

[Allen Browne]

Yes, Larry, there has to be a better way.

Presumably you have:
- a table of members,
- a table of aircraft, and
- a table of bookings (MemberID, AircraftID, BookingDate, ...),
and you enter the bookings as they occur throughout the month.

The invoices are documents that also belong in their own table. At the end
of each month, you want to create a new batch of invoices for any items that
have not been previously invoiced. The invoices will be created by code
that:
1) gets a new batch number;
2) groups the members who have uninvoiced bookings, and creates an Invoice
record for each one;
3) assign the member's InvoiceID (from step 2) to Booking.InvoiceID.

Step 3 assumes your BookingID table has an InvocieID field which is left
blank until the booking becomes a line-item related to an Invoice.

The opening balance for the statement is calcuated as the sum of invoices
prior to this one, less the sum of payments received up to the day before
the invoice date. Payments need not be tied to a particular invoice, because
people do weird things with payments such as part payments, dual payments,
even prepayment of things that have not been invoiced.

Additional things to consider:
- Do you want to prevent changes to the Booking record once InvoiceID is no
longer Null?

- Do you want to prevent changes/entry of receipts that are back-dated into
the period prior to the start of the last invoice batch (which would affect
the opening balance)?

- When creating a batch, allow the user to give you an ending date, but not
a starting date. You want all uninvoiced bookings up to that date.

- Users sometimes create a batch and realize it is wrong. Consider providing
an option to the most recent batch. Dead easy: Booking.InvoiceID to Null for
the records in the batch, and delete the invoices with the batch number.

Hope that helps you devise a suitable approach.