Personal Accounts - Handling share trading

[essaytee]

I've now come to a mental block as to how to design and map out the buying and selling of shares and recording the associated broker's commission.

It's in relation to a personal accounts package. For non share trading transactions I've got the design nutted out but for the life of me I can not seem to integrate the share trading component. The following is what I've got at the moment:

I'd appreciate any advice regarding the design.

Steve.

 

[essaytee]

Here's my version two:

Will it work? Comments welcome.

Steve.

 

[plog]

I don't like the loop you've created between tbl_Transactions, tbl_Accounts and tbl_Line_Items. There should only be one path between objects. Why are tbl_Transactions and tbl_Line_Items seperate tables? It seems their data could go into one table. How does a transaction have multiple line items? Isn't each line item a transaction itself. Without sample data, I'm having trouble seeing what these tables do.

There seems to be a lot of Trade information in tbl_Account_Securities, shouldn't that data be in tbl_Trade_Details? With that said, I don't see why security transactions should be outside tbl_Transactions. It seems the whole bottom half isn't necessary--a lot of that data will fit into tbl_Accounts and tbl_Transactions.

 

[essaytee]

I don't like the loop you've created between tbl_Transactions, tbl_Accounts and tbl_Line_Items. There should only be one path between objects.

The loop is the reverse entry. The income/expense/transfer recorded in the tbl_Line_Items (Account_ID) must exist in the tbl_Accounts.

Why are tbl_Transactions and tbl_Line_Items seperate tables? It seems their data could go into one table. How does a transaction have multiple line items? Isn't each line item a transaction itself. Without sample data, I'm having trouble seeing what these tables do.

Consider this scenario. At the supermarket, using a credit card, I purchase groceries for $78.99 and also withdraw cash $100. The total transaction is $178.99, and it's this amount that is recorded on my credit card statement. Using multiple line items allows me to attribute $78.99 to groceries and $100 to 'cash in hand'.

There seems to be a lot of Trade information in tbl_Account_Securities, shouldn't that data be in tbl_Trade_Details? With that said, I don't see why security transactions should be outside tbl_Transactions. It seems the whole bottom half isn't necessary--a lot of that data will fit into tbl_Accounts and tbl_Transactions.

It's this part of the Personal Accounts (think of Quicken) that I'm not overly confident with. In the main you are right regarding the tbl_Trade_Details information should/could be in the tbl_Accout_Securities table and I would have written it that way a couple of days ago. My last purchase of shares (two days ago) I amended the order after some of the shares were fulfilled and as a result the one contract note indicated two prices, one price for a block of shares and another price for the remaining block of shares.

Buying or selling of the shares is a little different to the other types of transactions in that I'm not concerned with quantities whereas with shares I am. Also the pricing of shares goes to three decimal digits. I'm not sure how I could incorporate the buying and selling of shares into the Transaction table above. In the tbl_Trade_Fees, I left out an amount field by mistake.

Steve.

 

[essaytee]

I've got a feeling that I might have to model the share trading component on Inventory Control. What I refer to as securities (shares) are in fact products which are purchased, held and sold.

I came across Allen Browne's handy tip regarding Inventory Control and have subsequently re-visited Northwind Traders. Will take me a little while just to come to grips with it.

On first viewing I'm unsure how to hook in to my other accounts in order to show ie. $1000 (whatever amount) from my savings account ultimately paying for shares or receiving from a sale of shares.

As always any advice appreciated.

Steve.