best practice: storing credit/debits
Keychain
transactions.
The structure looks like this:
tid, uid, amount, balance, type, dateline
The data looks like this:
1, 1, -10, -10, debit, 1202651856
2, 1, 3, -7, credit, 1202652000
A running balance is kept, which makes it easy to pull the newest record for
the current balance.
The debit/credit amounts are stored as positive and negative values.
Is there anything flawed about this approach?
Should the balance be kept in a separate table? Exp. table structure: uid,
balance
Should the amounts all be stored as absolute positive values?
What about having separate debit and credit columns and skipping the type
indicator? This would mean one of the columns for each record would always
be zero.
Any other thoughts/suggestions?
Thanks in advance.
Jerry Stuckle
> I need to create a table that records monetary-like (non-ecommerce)
> transactions.
>
> The structure looks like this:
>
> tid, uid, amount, balance, type, dateline
>
> The data looks like this:
>
> 1, 1, -10, -10, debit, 1202651856
> 2, 1, 3, -7, credit, 1202652000
>
> A running balance is kept, which makes it easy to pull the newest record
> for the current balance.
>
Don't keep a running balance. Just total the amounts for an account.
If the table gets too big, do it like the banks do - have a starting
balance for the month and total from there.
> The debit/credit amounts are stored as positive and negative values.
>
That's how I would do it.
> Is there anything flawed about this approach?
>
Just ensure you are encrypting any sensitive information (i.e. if this
were an ecommerce database, encrypt the credit card info).
> Should the balance be kept in a separate table? Exp. table structure:
> uid, balance
>
Nope. See above.
> Should the amounts all be stored as absolute positive values?
>
Definitely NOT.
> What about having separate debit and credit columns and skipping the
> type indicator? This would mean one of the columns for each record would
> always be zero.
>
Nope. And why do you need a type indicator? You can tell which one is
is based on the amount being positive or negative.
> Any other thoughts/suggestions?
>
> Thanks in advance.
>
KISS. Don't add more data than you need, and definitely don't duplicate
data. Try reading up on "database normalization". Google will give you
several good hits for tutorials.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
ThanksButNo
> I need to create a table that records monetary-like (non-ecommerce)
> transactions.
>
> The structure looks like this:
>
> tid, uid, amount, balance, type, dateline
>
> The data looks like this:
>
> 1, 1, -10, -10, debit, 1202651856
> 2, 1, 3, -7, credit, 1202652000
>
> A running balance is kept, which makes it easy to pull the newest record for
> the current balance.
>
> The debit/credit amounts are stored as positive and negative values.
>
> Is there anything flawed about this approach?
>
> Should the balance be kept in a separate table? Exp. table structure: uid,
> balance
>
> Should the amounts all be stored as absolute positive values?
NO.
There will be occasions to "undo" a transaction, which will be handled
as separate transactions.
E.g., you put in $100 to Debit. Somebody goes "Oops!" that was
supposed to be $50. So you add two more transactions -- first is a -
$100 to Debit, to clear out the mistaken entry -- second is the $50
correct entry.
Depending on how serious this has to be, you might consider adding a
column that points to the transaction being corrected.
Accountants are very big on this sort of thing. They'll lose sleep if
the books don't balance.
>
> What about having separate debit and credit columns and skipping the type
> indicator?
YES.
The database should reflect the "real world" as much as practical, and
that's the way accountants handle it.
> This would mean one of the columns for each record would always
> be zero.
Yah, so? At least you're not wasting paper.
>
> Any other thoughts/suggestions?
Get a book like "Bookkeeping for Dummies" or some such. The problem
most programmers have is they too often don't try hard enough to learn
what their customers are really doing, and so try to re-invent the
wheel.
"Oh, but this is a great way to do it, and it comes up with the right
answer!"
"Maybe so, but that's not the way WE do it."
>
> Thanks in advance.
You're welcome in reverse. ;-)
ThanksButNo
> On Feb 26, 7:18 pm, "Keychain" <some...@somewhere.com> wrote:
>
> E.g., you put in $100 to Debit. Somebody goes "Oops!" that was
> supposed to be $50. So you add two more transactions -- first is a -
> $100 to Debit, to clear out the mistaken entry -- second is the $50
> correct entry.
That word-wrapped badly. That's what I get for trusting the machine.
It should read (something like):
"negative $100 to Debit, to clear out the mistaken entry"
then a "positive $50 correct entry".
:P
Norman Peelman
No, that's not the way it's done. You don't have one area you put money
in and a different area you take money out. Like Jerry said, you just
have to compute the balance:
SELECT SUM(balance) from accounts where uid = ?
The first entry for an account will be a positive (credit) to a
technically zero balance account.
>
>> This would mean one of the columns for each record would always
>> be zero.
>
> Yah, so? At least you're not wasting paper.
>
>
but your wasting time maintaining a completely unneeded table.
>> Any other thoughts/suggestions?
>
> Get a book like "Bookkeeping for Dummies" or some such. The problem
> most programmers have is they too often don't try hard enough to learn
> what their customers are really doing, and so try to re-invent the
> wheel.
>
> "Oh, but this is a great way to do it, and it comes up with the right
> answer!"
>
> "Maybe so, but that's not the way WE do it."
>
>> Thanks in advance.
>
> You're welcome in reverse. ;-)
--
Norman
Registered Linux user #461062
ZeldorBlat
There are lots of different ways to do this (as other replies in this
thread indicate). If you only ever have two transactions (credits and
debits) then you really don't need a separate type and you can get by
with just the signs.
In our business, however, we have many different types of
transactions. Granted they each either take money out or put money
in, but it's important for us to be able to sum transactions of a
given type. So we end up with tables like this:
account:
accountID
companyID (who this account belongs to)
balanceAmount
transactionType:
transactionTypeID
name (credit, debit, fee, etc.)
transactionType (either -1 or 1)
transactionHistory:
postDate,
accountID,
transactionTypeID,
amount
So when a transaction is added we update the account balance like
this:
set balanceAmount = balanceAmount + (transactionType * amount)
As someone else has pointed out accountants don't like it when you
delete transactions, so instead you back them out with another
transaction. Given the above example, suppose we created a
transaction of type "fee" -- which presumably reduces the account
balance (suppose transactionType == -1) but then later decided we
didn't want to. Rather than create another "fee" transaction for the
opposite amount we create another type of transaction (call it "efee")
that has the opposite transactionType. Now when you add them together
you get zero. Additionally it leaves a better record of what actually
happened and why it actually happened.
As I said before there are lots of different ways to do this and it
will all depend on how much detail you need to keep track of.
Keychain
news:T9KdnXZO-7lxR1na...@comcast.com...
If it wasn't clear in my original message, this is NOT a real ecommerce site
so security (from a sensitive financial data perspective) isn't an issue.
I will continue to store the amounts in one column as positive/negative
numbers.
So the balance column isn't necessary. I can simply Sum() the amount column,
but I am concerned about performance down the road. How would "starting
balance for the month" work programmatically and data structure wise?
It's true, the amount will indicate debit/credit. However I do need a type
column to distinguish the type of credit/debit for reporting purposes.
Thanks to everyone who responded.
Kees Nuyt
<zeldo...@gmail.com> wrote:
>As someone else has pointed out accountants don't like it when you
>delete transactions, so instead you back them out with another
>transaction.
Accountants (and beancounters) also want to know
"whodunnit", that is, who initiated the transaction.
That's another column in transactionHistory.
Just my 0.02
--
( Kees
)
c[_] Tidying the house while the children are growing
is like shovelling snow while it's still snowing. (#235)
Jerry Stuckle
Do it like the banks do. On a regular basis, archive the existing
transactions and start with a new balance.
Or, for instance, you might archive all transactions over six months old
and put in a starting balance as of the date.
For instance, if you want to archive everything before 1 September, 2007:
1. Start a transaction (using INNODB database)
2. Sum all the transactions up through 31 August and remember.
3. Copy all transactions through 31 August to your archive table
4. Delete all transactions through 31 August
5. Insert a transaction dated 1 September with the balance from Step 2
6. Commit the transaction
How many rows are you planning to have in the table, anyway?
And yes, I know you said it wasn't a real e-commerce site - that was
more for those who are doing e-commerce sites and reading this thread.
Jerry Stuckle
> On Wed, 27 Feb 2008 06:52:29 -0800 (PST), ZeldorBlat
> <zeldo...@gmail.com> wrote:
>
>> As someone else has pointed out accountants don't like it when you
>> delete transactions, so instead you back them out with another
>> transaction.
>
> Accountants (and beancounters) also want to know
> "whodunnit", that is, who initiated the transaction.
> That's another column in transactionHistory.
>
> Just my 0.02
In case it wasn't clear, this isn't an e-commerce site. So you don't
need all of the audit capabilities.
However, a database is not a good thing to use for an accounting system,
anyway. It's too easy for someone to get in there and "fudge" the
numbers, add/delete rows, etc. without going through the standard
program. You need to keep *very tight* security on the server, both
electronically and physically to prevent unauthorized changes.
And yes, you can add some security features to rows, such as a checksum,
and keep the algorithm secret (in compiled code). But all that allows
you to do is detect changes.
If you do use a database, you need a full audit trail generated by the
database itself and kept in a secure directory with very limited access.
Keychain
Jerry, thanks very much for your instructive response.
As for the number of rows in the transaction table, the web site (which will
be free to participate) has the potential of eventually attracting a
somewhat large following (100,000+ users) with a hundred or more
transactions per month per user. 100 x 100,000 = 10,000,000. A potion of
those transactions will be calculated and recorded periodically via cron.
Your archive scenario seems like a solid approach--thanks!
ThanksButNo
> ThanksButNo wrote:
> > On Feb 26, 7:18 pm, "Keychain" <some...@somewhere.com> wrote:
>
> >> This would mean one of the columns for each record would always
> >> be zero.
>
> > Yah, so? At least you're not wasting paper.
>
> but your wasting time maintaining a completely unneeded table.
>
It's not a table, it's a column.
toby
> indicator? This would mean one of the columns for each record would always
> be zero.
This approach works well for our application.
Keychain
I stumbled across another challenge today. I need to create a ledger type
report, which will include debit, credit and balance columns. The report
will use pagination with one or more pages. The trick is how to display the
running balance. As per the advice given here, I eliminated the running
balance from the transaction table. I know a running total can be calculated
per row in the query, but what with a range of rows using LIMIT?
Keychain
news:ZpmdnUZj29vA8k7a...@comcast.com...
After giving this matter some additional thought, it seems like the best way
to go about having a running balance with pagination is to use a separate
query to retrieve the beginning balance at the start of each page. For
example (assuming each page showed 20 records) page 1 retrieves records
1-20, page 2 retrieves records 1-40 and so on. The running balance would be
calculated as the page is being built. This method adds one additional query
per page, which isn't bad.
Michael Austin
It also sounds like you really need to do what should have been done in
the first place. Sit down with the "customer" and determine all of the
absolute requirements for the project. This is a lost art in today's
business where RAD (rapid application development outweighs multiple
ReRAD (re doing the same application over and over because someone can't
make up their mind or truly do not know what they want).
What you really need is a report-writer not just the SQL.
Report-writers use multiple sql-statements to "build" the report to suit
the requirements (Think billing/invoice statements from your vendors or
that "pretty-looking" report for the bean-counters/execs).
Oracle incorporated a lot of the page formating syntax to build those
sorts of reports and pagination etc... and I am sure some companies
still use those reports, but I do not see that sort of formating within
the MySQL engine(s) - I have not read the docs cover-to-cover yet... :)
but, I also could not find those sort of things using google or the
search feature on the MySQL site either...
Keychain
news:MnXAj.25487$J41....@newssvr14.news.prodigy.net...
Thanks for the response, but I don't need a lecture about how to scope a
project. Your answer has absolutely nothing to do with my question (or the
thread.) Perhaps you should take your own advice and take the time to
educate yourself before jumping to conclusions.
Jerry Stuckle
Actually, I think Michael is quite correct. These are the types of
questions which make a difference in a project. In some cases, it
changes the database design; it can even affect the selection of the
RDBMS you should be using.
I also agree with him that now you need a report writer. I'd be looking
at another language to write the report and not try to do it all in SQL.
You probably could do it all in SQL, but it wouldn't be easy.
Keychain
A quick follow-up.
It turns out the BEST way to go about this is calculate a running balance
using a user-defined variable in a temporary table query. It's then easy to
return a range of records for pagination.