The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

billing systems database design

I've been looking for some good articles and pointers on the subject. The basic needs are to register invoices, payments and enable users to view their balance at any given time.

So, there would be tables for
* invoice
* payment
* customer
* balance?
* credit limits?
* ...

Any hints where to look? I know that it isn't really rocketscience, but I want to avoid the common mistakes.
very newbie Send private email
Wednesday, August 31, 2005
Excellent article by Martin Fowler re: accounting patterns:

Also check out his "Patterns for things that change with time" article. Scroll down to the "Dimensions of Time" section to read about Actual date vs. Record date.
Weef Send private email
Wednesday, August 31, 2005
There's an excellent book, "Data Model Patterns: Conventions of Thought", that you might want to consider.
David Aldridge Send private email
Wednesday, August 31, 2005
Why not just have look at an existing working solution like SQLledger?
Michael Van Wesenbeeck Send private email
Wednesday, August 31, 2005
You also need to track:
*what* you are billing (time, materials, fees),
*how* you are billing (fixed price, time & materials, cost+, etc)
*who* you are billing (not just customer, but project)

Oh God, I'm having flashbacks.
KC Send private email
Wednesday, August 31, 2005
Take a look at the book "Enterprise Patterns and MDA":

The title is actually misleading.  The bulk of the book is devoted to fleshing out "business archetypes", or the basic objects that recur repeatedly in business software.  For each archetype the authors develop what they call "archetype patterns", which are supposed to be analogous to "design patterns" for the basic design structures that recur repeatedly in software in general.  They have chapters on the "Party (Contact)" archetype pattern, "Product", "Inventory", "Order" archetype patterns and more. 

This book is really helpful even if you don't end up implementing everything in their patterns.  They've really thought things through.
Herbert Sitz Send private email
Wednesday, August 31, 2005
You can find a example database layout for virtually anything here:

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Thursday, September 01, 2005
I've been designing billing systems for use in web hosting for nearly six years now, and I'll definitely cast my vote for "Enterprise Patterns and MDA"

This book really hits the nail on the head in terms of putting together a *complete* and *perfectly normalized* billing/customer resource management database.  It's important to keep in mind, however, that this book outlines a very complex (albeit thorough) model of such a system.  Your particular system may not require everything covered in this book, so it's your responsibility to use what you need and leave out what you don't.

One recommendation I can make is that starting with a simpler model and moving toward a more complex one when the need arises is a much better approach than trying to think of every conceivable scenario at your drawing board.  You'll simply drive yourself mad, and you'll likely never even see a working prototype (although I guess that's more of a methodology issue than it is database design ;) ).
Matt Lightner Send private email
Sunday, September 04, 2005
I forgot to mention that there are some pretty complete examples of database schemas for open source billing systems available out there.

Of particular interest might be a system called "freeside" which makes its database schema available here:

And as a giant PNG diagram here:

(a DIA source file is also available from the first page)

Further to that, I'll just briefly go over some of the more fundamental "billing" concerns that I, personally, have come across when designing billing systems.  I've presented two basic approaches to handling charges and payments (essentially the accounting side of a billing system) below.  There are, of course, other (and likely better) ways to go about things, but I'd like to think that some of the below brain dump will be of use, or at least get you thinking in the right direction.  :-D

** Simple journal-based (or register-based) handling of charges and payments**

One option here is to use a journal/transaction/register (all words apply I think... ) style system, where you simply track credits and debits (or charges and payments, or whatever you'd like to call them), and sum them to get the customer's current balance.

Some considerations to be mindful of when using a simple transaction register:

- How will you calculate when a customer's account is "past due" (the customer is late on their payment)?  Payments aren't made toward any specific charge--they are simply made toward the customer's "account" with the company.  As a result, you can't accurately determine if a given charge is past due, and, in fact, determining if a customer's account is past due on the whole is somewhat tricky.  My approach, which I believe to be about as good as you can get, is presented below in perl/ruby-ish pseudocode:

Algorithm for calculating an account balance in a basic "register" style system:
# This is the sum of all payments ever made to this account
payment_stack = sum of all of an account's payments

# Initialize variables
charge_stack = 0.00
oldest_unpaid_charge_date = undef

# Iterate through each "charge" record and compare with payment_stack
foreach charge (account.charges) {

  # Add the next charge to our "stack"
  charge_stack += charge.amount

  # See if this is the charge that pushes the charge
  # stack higher than the payment stack
  if (charge_stack > payment_stack) {

    # This is the first "unpaid" charge to the account
    # Use this charge date for calculations
    oldest_unpaid_charge_date =

    last # Stop processing charges

  # Find the "past due date" by adding 30 days to the
  # date of the first unpaid charge.
  account_past_due_on_date = calculation_date + 30 days


In words:
The basic idea is to stack all payments in a column, and then iterate through the customer's charges until you determine the first charge that the payments are not sufficient to cover.  That is the date from which you perform the past due calculation.

** Invoice-based handling of charges and payments **
An alternative is to add an additional level of grouping: invoices.  Invoices allow you to group multiple (usually related--either by time or by type) charges together into an "invoice" which generally defines the billing parameters for all attached charges (usually called "line items" in this context).  So all line items on a given invoice will share the same post date, due date, customer, payment terms, etc.

Additional considerations when using invoices as opposed to a simple register:

- Do payments on an account get applied toward a specific invoice?  If so, you will need to add in an intermediate layer between the physical payments (money received) and the application of those payments to specific invoices.  This will allow payments to be "split" between multiple invoices.  Without this layer, payments would need to match invoice amounts *exactly* in order to "zero out" (and effectively "close") an invoice.  For instance, if an invoice is for $45 and a customer sends a check for $50, you'll want to split the payment and apply $45 toward that invoice, and then leave $5 "unapplied" (which will likely be applied toward the next invoice automatically).

- When an invoice is "closed" (I.E. achieves a zero balance), your system should effectively lock it and prevent any further modifications.

- Will you cache an invoice's "total" (price, cost, amount, whatever you call it) in the invoice table, or will you calculate it dynamically from the line item records every time you need it?  The former is less resource-intensive, but the latter ensures database accuracy.  A good alternative would be to use triggers--either at the database level if your DB engine supports them, or at the application level, if you're using a DB framework with built in cross-database trigger support.  (note that this solution to this can also be used for retrieving a customer's overall balance from their billing records)

That's just a cursory glance of some of the high-level considerations I've run into.  There are dozens more you'll no doubt come across while building your system--just know that it's very likely that someone else has come up with a solution.  The trick is being able to find the answers before you can properly define the problem (at least with the same words that everyone else is using ;) ).

Best of luck!
Matt Lightner Send private email
Sunday, September 04, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz