Is this an acceptable way to design an accounting, purchasing set oftables?

[jmDesktop]

For an accounting / banking database, I have the following in a two
transaction tables showing what someone spends or credits:

Transaction Table //one only
--
TransactionID
UserID
TransactionItemID Table //many
--
TransactionItemID
TransactionID
ItemID
AccountTable //one to one from TransactionID Table
--
AccountID
TransactionItemID
UserID
Debit
Credit
Balance
One transcation, many items tied to that single transaction. Results
of that transaction put in the account table. Is this right or too
many tables?

[Roger]

On Jun 16, 4:06*pm, jmDesktop <[e-mail]:

For an accounting / banking database, I have the following in a two
transaction tables showing what someone spends or credits:

Transaction Table //one only
--
TransactionID
UserID

TransactionItemID Table *//many
--
TransactionItemID
TransactionID
ItemID

AccountTable //one to one from TransactionID Table
--
AccountID
TransactionItemID
UserID
Debit
Credit
Balance

One transcation, many items tied to that single transaction. *Results
of that transaction put in the account table. *Is this right or too
many tables?

why do you need 'userId' in accountTable ? you have transactionItemId
which will get you the userid in the transactionTable

what do you mean by 'Results of that transaction put in the account
table', do you have a function that updates the accountTable based on
what was entered in the transactionItemTable ?

wondering why this can be done with 2 tables
Transaction Table //one only
--
TransactionID (pk)
UserID (fk)
transactionDate

TransactionDetail Table //one to one from TransactionID Table
--
TransactionID (pk) (fk)
ItemID (pk)
AccountID (fk)
Debit
Credit

Account Table
--
accountId (pk)
accountName
balance

User Table
--
userId (pk)
userName
Item Table
--
itemId (pk)
itemDescription
to answer another post, you should never store a total / balance, you
should always calculated it, with proper indexing, sum millions of
records should be quick

but I did add a balance field to the accountTable to deal with
deleting old transactionDetails. To start the balance is 0 and your
query will sum the credit / debit from transactionDetails and add
accountTable.balance

a 'delete' function will update the accountTable.balance with the
balance of the deleted transactions, so that the above query will give
you the same results after the 'delete' is done