[thelist] Invoices and ledgers and schema

Ken Moore psm2713 at hotmail.com
Mon Jun 5 22:44:48 CDT 2006


Hi Bill,

I'm not certain that I understand, but applying standard database concepts, 
this is what I would say.

The lefger table needs 1 change. Remove the amount field (column) as 
follows.

     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,
         invoice                 integer REFERENCES invoice
     );

Ditto for the balance field in invoice table.

     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,
         prev_invoice        integer REFERECES invoice,
         comm
      );

The reason for both is that you (almost) never record any type of calculated 
amount.

Next add an invoice_item table
    id              Primary Key
    invoice_id   PK from invoice table
    amount      Do all of your calculations from here.
    other_fileds

This way, all of your amounts roll up into the proper invoice and all of the 
invoices into the proper ledger account. And you process a cancellation 
exactly as a sale which will zero the previous charge. In addition, the 
cancellation should fire a trigger (automatic action on a given condition or 
user action) to find the next one waiting in line and create an entry into 
the invioce_item table for them.

Think about this before you decide not to procede this way. Let me know if I 
can help

Ken


>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
>
>--
>
>* * Please support the community that supports you.  * *
>http://evolt.org/help_support_evolt/
>
>For unsubscribe and other options, including the Tip Harvester
>and archives of thelist go to: http://lists.evolt.org
>Workers of the Web, evolt !

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/




More information about the thelist mailing list