I don't know this material as well as the accounting stuff we worked on in a different thread. That said, I don't see why you need both tables TransactionLineItems AND SharesTraded. Your LineItems table, which should not contain the Amount in yellow (always calculate that!) only has a notes field. The rest are keys. That's not enough data for a whole table.
Also, if the Transaction is a child of an account, does each line item need to link to an account too? Could a transaction link to an account and still have line-item children that link to a different account? Then maybe the transaction shouldn't link to an account?
What kind of transaction will there be? You need to buy, sell, and record price changes right? What else?
Thinking out loud a little, hope this helps,
Thanks Mark, I like the 'thinking out loud' bit, that's what I need.
Forget the share trading aspect for a moment, that leaves me with three tables.
Scenario:
I purchase groceries from the local supermarket for say $150 and at the same time I withdraw $100 in cash. For this one transaction, my savings account (because I used my debit card) will reflect $250 when I come to reconcile a month later upon receiving my statement. I want to record the fact that one entry is $150 for groceries and another entry is $100 cash.
For the one transaction I need the ability to separate items to their respecitive accounts (category types - income, expense or transfer) hence the inclusion of the third table solely for the separate items.
In the table "..... Line_Items" there is an Account_ID field which in effect is the corresponding or reverse account, to where the funds are being attributed and this Account_ID must exist in the table, "tbl_Accounts".
After entry of the initial transaction, spending $250, I will have the application automatically create the reverse entries. In fact, the reverse entries actually create two entries in the table "tbl_Transactions" referring to the Accounts "groceries" and "cash" and each of these "tbl_Transactions" entries will have only one table "....Line_Item" entry and each will refer to the Savings Account.
Now including the Share Trading aspect:
On trading shares, it's common for the individual share price to be recorded to three decimal digits, obviously multiplied by the quantity to arrive at a total amount for the block of shares traded.
Following on from my scenario above, an entry is made in the table "tbl_Transactions" and the originating account will be my share trading account. Two entries are made in the table, "......line_items", one for the block of shares traded and another for the 'Commission' expense in relation to the trade of shares. There could be other expenses associated to the trade of shares.
I've loosely modeled the share trading aspect on an inventory model. I need to know, at any given point in time, how many shares I own (not that many by the way).
I'm not sure if I should combine the tables ".... Line_Items" and ".....Shares_Traded" as share trading is only a minor component of my overall transactions. I see that the "....Shares_Traded" table is more 'product quantity - on hand' transaction type table and not a costing/pricing type table.
Market value of shares:
I've not included a table relating to the 'End of Day' data for shares. This information will basically contain, date, share, high price, low price, open price, close price. It will be the 'close price' of most concern. When I get my underlying basic schema in order I can then hook this table into the schema.
Some other thoughts (single amount field):
The actual amount entered will be recorded as either negative or positive. Upon creating the reverse entry the sign will be reversed. The data entry screen will have purpose controls for debits/credits, spending/receiving etc and this will dictate the sign of the amount to be entered into the tables.
Lastly:
I'm coming from a background of using Quicken and years ago, Microsoft Money, for handling my personal accounts. I hope the above clears things a little and provides a bit more information to advise on making improvements. I'm all ears.
Steve.