Subject Re: Best table structure for Debit/Credit
Author Adam
--- In, Milan Babuskov <milanb@...>
> Alan.Davies@... wrote:
> > Generally one field for the amount and another to identify the
type of
> > transaction is best.
> I have to say that I disagree with you and agree with other Alan
> (McDonald). Your approach needs complicated selects. Having a field
> identify the type of transaction doesn't give any benefit and
> it would be useless. OTOH index on value > 0 and value < 0 should
> quite fast.
> Having two separate columns is also good when you need to SUM() as
it is
> very simple, while in your case it would get even more complicated
> you have to multiply all the "credit" records by -1.
> --
> Milan Babuskov

Thanks for the ideas to consider.

Alan (M), in our business case there is no such thing as a negative
credit. We are not dealing in currency but employee leave balances. I
just put it in an example that I think makes it easier to consider.

Alan (D), that sort of idea was the other one I considered. Milan is
right that an index on such a field would usually have terrible
selectivity (although in our case there would be reasonably few
credits, 4 per year per employee (most likely) and debits would occur
any time they wanted leave. In any case, we are talking about well
under 100 records per employee so any index optimisation is a bit
fruitless, you might as well go natural.

Milan, I think a view would go a long way to simplifying the query
you end up writing in either case, and by using a stored procedure to
add a new record, it is not very difficult to change our mind later
anyway. One other possibility I have considered is to create two
computed fields on the table, one for debits, one for credits using a
simple case statement. The particular computed field could be summed
pretty easily.