Database design for processing individual and standing payments

Question

We have a billing system in which we process individual payments, as well as recurring payments (subscriptions).

There are two SQL tables:

StandardCharges RecurringCharges 

The StandardCharges table stores individual items purchased by customers during the month.

The RecurringCharges table stores repeating items with a pay by date. When the time comes, our system automatically creates a recur query that adds a row to the StandardCharges table and increases the fee by date until the next month in the RecurringCharges table.

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

Is there any design model or other way to do this? Is this the correct database design? Ideally, I would like to keep all charges in one Charges table and also receive periodic payments from it?

thanks

Answer 1

I suspect your design is really correct.

When you think about data in the real world, it makes no sense to have “possible” transactions (IE, transactions that have not yet occurred and which may not occur, possibly because the client has exceeded his credit limit), mixed with committed and actual transactions.

Combining data into one table can also make reporting difficult, as you need to apply special filtering criteria and store additional metadata, such as TransactionCompleted and TransactionIsFutureCharge.

If I made a proposal, he would rename StandardCharges to something closer to the data he has, like CompletedTransactions , and RecurringTransactions is something like PendingTransactions .

Answer 2

The current design seems reasonable to me. But if you want to join the two tables, you can simply add a BIT column named IsRecurring or IsFuture or IsScheduled or whatever you want to use to assign charges that would otherwise be in RecurringCharges. Then, when your deadline expires with periodic payment, you simply insert into the same table instead of another table. As for the invoice, you simply added a condition to the query to filter out the expenses that have a set of BIT columns.