--- In firebird-support@yahoogroups.com, Milan Babuskov <milanb@...>
wrote:

>
> 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

to

> identify the type of transaction doesn't give any benefit and

indexing

> it would be useless. OTOH index on value > 0 and value < 0 should

be

> 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

as

> you have to multiply all the "credit" records by -1.
>
> --
> Milan Babuskov
> http://swoes.blogspot.com/
> http://www.flamerobin.org
>

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.

Adam