CODEWITHSUNDEEP
c#sqldesign-patternsdatabase-designsql-server-2008-r2
03Usr
03Usr

Reputation: 3435

Database design for handling individual and recurring charges

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

Upvotes: 6

Views: 2039

Answers (2)

Kane
Kane

Reputation: 16802

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.

Upvotes: 2

ekolis
ekolis

Reputation: 6786

The current design seems reasonable to me. But if you want to merge the two tables, you could simply add a BIT column called IsRecurring or IsFuture or IsScheduled or whatever you want to use to designate the charges that would have otherwise gone in RecurringCharges. Then when your due date is hit for a recurring charge, you just insert into the same table instead of a different table. As for the invoice, you'd just add a condition to the query to filter out the charges that have the BIT column set.

Upvotes: 0

Related Questions