Database design for handling individual and recurring charges

Question

We have a billing system where we process individual charges as well as recurring charges (subscriptions).

There are two SQL tables:

StandardCharges
RecurringCharges

StandardCharges table holds individual items purchased by customers during the month.

RecurringCharges table holds recurring items with a charge by date. When the time comes our system automatically creates a recur request which adds a row to the StandardCharges table and increases the charge by date to next month in RecurringCharges table.

At the end of each month we get the total values for each customer from StandardCharges table and create an invoice.

Is there a kind of design pattern or another way of doing this? Is this the right database design? Ideally I would like to hold all charges in one Charges table and manage recurring charges from there as well?

Thanks

Answer 1

I suspect that your design is indeed correct.

When thinking about the data in real world terms it makes no sense to have "possible" transactions (I.E., transactions which have not yet happened and may not materialize, perhaps because the customer had overrun their credit limit) mixed in with committed and actual transactions.

Merging the data into a single table can also make reporting difficult as you have to apply special filtering criteria and store extra meta data - like TransactionCompleted and TransactionIsFutureCharge.

If I was to make a suggestion it would be renaming the StandardCharges to something closer to the data it holds like CompletedTransactions and the RecurringTransactions something like PendingTransactions.