Hi There,
Another "how do you do this?" kind of question. I'm looking for feedback on this. You need to have worked with accounting DB's which I'm doing now but don't have any formal experience.
Let's start with two tables, once called invoices, one called transactions. Invoices is the "wrapper" table for an invoice -- address to mail to, comments, client id, etc.. Transactions is the actual line items table. Every invoice has at least one transation (line item) with a dollar amount. the amount of the invoice is IMPLICIT by the sum of the line items.
You could also look at transactions as the "hub" of all transactions. Now suppose I add a payments table. That's the wrapper for customer paments. Every payment has a line item also, which is a split of all invoices it was applied to. A payment is of the account Undeposited Funds.
So, here's the question/comment. My practice has been to enter each line item for the invoice, say like this:
(note ID fields are actually foreign key integers, this is just easier reading)
AccountID Qty Rate Extension
Income1 -1.5 45.00 -67.50
Income2 -2.0 55.00 -110.00
MiscExp 5.00 5.00
OK, but should I also add an "invisible" line to account for the accounts receivable (invoices) account itself?
AccountID Qty Rate Extension
Income1 -1.5 45.00 -67.50
Income2 -2.0 55.00 -110.00
MiscExp 5.00 5.00
AcctsRecv 182.50
PRO: some queries are easier
CON: can't run this query:
SELECT SUM(Extension) from Transactions WHERE InvoiceID=xyz
Because it would always be zero.
I hope that's not a long way to describe this. The Payment looks like this:
AccountID Qty Rate Extension
AcctsRecv -182.50
UndepFunds 182.50
with a wrapper of course in the payments table -- essentially this is a transfer.
Any wise feedback is appreciated.
Sincerly,
Sam Fullman
Compass POint Media
