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