[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