Storing financial transactions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Storing financial transactions

Author

Topic

bangingtunes
Starting Member

23 Posts

Posted - 2003-10-10 : 10:34:00

Im wondering if there is a standard way of storing financial information. I need to be able to create debit and credit transactions for customers. If they buy something a debit is made from there account and if they pay something a credit is made. Retrieving the account balance (credits - debits) will be the most common query against this data.

I can think of severel ways to store this kinda data.

A/ Credit Table, Debit Table
B/ Credit column, Debit Column
C/ Amount column, Credit Boolean indicator
D/ Amount column, Using neagtive amounts for debits.

Can anyone advise as to which if any is the norm.

Thanks

Kristian

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-10 : 10:59:38

It's more about knowing your subject than anything technical...

http://www.online-accounting-tutorials-software.com/accounting-101.html

http://www.finsvc.duke.edu/finsvc/gap/

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-10 : 12:14:13

Don't use negatives to determine debit vs. credit, because there can be negative debits or negative credits sometimes.

I would use 2 columns:

"Amount"
"DebitCredit"

and store some indicator of which side of the ledger the transaction belongs to in the DebitCredit column. I have used "DR" and "CR", but you could use "D" or "C" or something like that.

You could use a "Debit?" column with a 1 or a 0 as you suggested, it's entirely up to you.

But DEFINITELY all in the same table.

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-10 : 12:17:14

GL, AP, AR, PO, ...?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-10 : 13:20:44

Hey a debit can be a negative or a positive (same with a credit)...

All depends on which "direction" you're going, or what point of view...

All the ledger stuff we get uses a "D" or "C" on a row....

Brett

8-)

bangingtunes
Starting Member

23 Posts

Posted - 2003-10-10 : 16:10:47

Thanks for the feedback guys.

(Except nr, i dont have a clue what your on about)

I was actually swaying towards using 2 tables, partly because i wish to store the payment type, which is only applicable to credits.

From a logical point of view you could say a credit and a debit are 2 different things therefor 2 different tables, or you could just say they are both financial transactions?

The fact its a 2 table query to get the balance puts me off a little, but not a lot.

What else has the 2 table approach got going against it?

Thanks

Kristian

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-10 : 16:20:31

I would hold the balances on a separate table which is maintained whenever an entry is made. You don't want to have to run aggregates every time you need to get a balance.

ap = accounts payable, gl = general ledger, ar = accounts receivable, po = purchase orders, ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kmarshba
Starting Member

24 Posts

Posted - 2003-10-10 : 16:43:42

I would go with nr's suggestion on the aggregates.

Regarding debits/credits I would leave them in the same table as long as they are similar, as I would imagine them to be.

bangingtunes
Starting Member

23 Posts

Posted - 2003-10-12 : 08:40:43

Listening to your suggestions and reading up on triggers i have come up with the following to update the account balance.

I have not used triggers before and obviously account balances are not something you want to get wrong. Ive tested the following with updates, deletes and inserts and it seems to work.
Does the following look ok to you? Is there anything noticably wrong?

------------------------------------------------------------------

CREATE TRIGGER trig_RecalculateBalance
ON dbo.CustomerTransactions
FOR INSERT, UPDATE, DELETE

AS

DECLARE @Balance SMALLMONEY
DECLARE @UserName USERNAME

IF EXISTS (SELECT * FROM inserted)
BEGIN
SET @UserName = (SELECT UserName FROM inserted)
END

IF EXISTS (SELECT * FROM deleted)
BEGIN
SET @UserName = (SELECT UserName FROM deleted)
END

IF NOT @UserName IS NULL
BEGIN
SET @Balance = (COALESCE((SELECT SUM(Amount) FROM CustomerTransactions NOLOCK WHERE UserName = @UserName AND Credit=1), 0)
- COALESCE((SELECT SUM(Amount) FROM CustomerTransactions NOLOCK WHERE UserName = @UserName AND Credit=0), 0))

UPDATE Customers SET AccountBalance = @Balance WHERE UserName = @UserName
END

Thanks

Kristian

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-12 : 10:04:42

BEWARE!! A trigger is invoked only once for a DML statement even if it affects more than one row! So if you executed a statement such as:

DELETE FROM CustomerTransactions WHERE CustomerID = 10900

...and 10 rows were deleted, the trigger would get invoked only once, and the variable @Username would contain only the last username from the Inserted table. You need to handle such situations using JOINs.

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources