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


TransactionItem ID Table //many
--
TransactionItem ID
TransactionID
ItemID


AccountTable //one to one from TransactionID Table
--
AccountID
TransactionItem ID
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]

Re: Is this an acceptable way to design an accounting, purchasing setof tables?

On Jun 16, 4:06 pm, jmDesktop <needin4mat...@ gmail.comwrote:

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
>
TransactionItem ID Table  //many
--
TransactionItem ID
TransactionID
ItemID
>
AccountTable //one to one from TransactionID Table
--
AccountID
TransactionItem ID
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 transactionItem Id
which will get you the userid in the transactionTabl e

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 transactionItem Table ?

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

TransactionDeta il 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 transactionDeta ils. To start the balance is 0 and your
query will sum the credit / debit from transactionDeta ils and add
accountTable.ba lance

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