[thelist] Invoices and ledgers and schema

[Bill Moseley]

I'm having a few problems getting a schema I'm happy with, so maybe
someone here can help.  Or at least bounce some ideas off of.
Hope I'm not being too verbose below...

I have a basic shopping cart application for managing online workshop
registrations.  People can sign up for more than one workshop at a
time (just like more than one item in a cart), and they can also sign
up for workshops that are full, and be placed on the wait list.
There's no charge for signing up on the wait list.

If someone cancels a confirmed registration the cost of the workshop
is credited.  Likewise, if someone moves from the wait list to being
confirmed they are charged.  People often sign up weeks in advance,
but can cancel up to a week before the event for a full credit.

The actual money processing is handled by a separate application (a
separate agency completely), so the web application doesn't really
contain much accounting.  For example, I'm not currently doing double
entries.

The web application only needs to let the agency know of new
credits/debits that have happened over a period of time.  This agency
sends out the invoices (no online credit card charging at this time).


There's a "cart" table that represents an order, and there's a
"registration" table that holds the individual items ordered that
reference the cart.  Again, a cart might have multiple ledger entries
over time.


Now, to track charges I have a ledger table:

    create table ledger (
        id                      SERIAL PRIMARY KEY,
        cart                    integer NOT NULL REFERENCES cart,
        registration            integer REFERENCES registration,
        transaction_by          integer REFERENCES person,
        transaction_time        timestamp(0) with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        transaction_type        integer NOT NULL references transaction_type,
        amount                  numeric NOT NULL default 0,
        invoice                 integer REFERENCES invoice
    );

I have questions about that "invoice" column below.


Periodically, the application is suppose to generate a list to be sent
to the agency that will be used for invoicing (or credits).  So, one
idea is to find all carts that have a non-zero balance:

    select c.*, balance from cart c
    join ( select cart, sum(amount) as balance from ledger
           group by 1 having sum(amount) != 0) as l
           on l.cart = c.id;

Obviously, I only want to do that once.  One option would be to add
in an offset adjustment to zero out their balance after running the
above select.

But, at each nightly run the agency needs more than just the current
balance.  It needs the list of new individual charges.  That's what the
"invoice" column is for.  NULL "invoice" columns means the ledger
item has not been sent for billing/crediting.

So, the nightly run finds all non-zero ledgers, then sends all rows
that have NULL "invoice" column, then updates the row with an invoice
number.

Following?  Does this seem sane so far?

Here's where I need some ideas:

Now, if someone cancels a registration and I send a new "invoice" off
to the agency (because a ledger now has a non-zero balance or has NULL
"invoice" row) I'm not clear how best to get their previous balance.

I can add a "current_balance" to their cart -- and set it each time
an billing run generates an invoice.  But, then I have two places in
the database representing the same data: the ledger and the cart.

The other idea is to have the current balance in the invoice table.
But, I'm not sure that's best when a cart has multiple invoices:

    create table invoice (
        id                  SERIAL PRIMARY KEY,
        invoice_time        timestamp(0) with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        invoice_group       integer REFERENCES invoice_group,
        balance             numeric NOT NULL,
        prev_invoice        integer REFERECES invoice,
        comment             text
    );

(invoice_group is to track batches of invoices sent to the agency.)


Or, is it better to get their current balance from adding up all the
transactions that have a non-NULL "invoice" column?

How would you setup this schema?

Thanks,



-- 
Bill Moseley
moseley at hank.org