Results 1 to 7 of 7
  1. #1
    Golden_Orb is offline Novice
    Windows 11 Office 365
    Join Date
    Jan 2023
    Posts
    3

    Database design confusion with table relationships

    I'm a newbie an am trying to develop a small system to keep track of my share portfolio. At the moment I have 3 tables - tblStockCodes, tblHoldings and tblSellOrder with the following structure:


    Click image for larger version. 

Name:	Relationships for ShareTrack.jpg 
Views:	19 
Size:	25.3 KB 
ID:	49565
    When a buy order for a number of shares in a company is executed I can enter the details into my tblHoldings table and set the SoldQuantity to zero. As I could buy more than one parcel of shares for the same company on the same date or on different dates I established a one-to-many relationship between tblStockCodes and tblHoldings using the field StockCodeID. Similarly I established a one-to-many relationaship between tblSockCodes and tblSellOrder using the field StockCodeID.

    Where my confusion creeps in is not understanding how to relate the tables tblHoldings and tblSellOrder. Depending on how I want to handle the sell order for capital gains purposes, etc I could have a tblSellOrder record for a certain company reduce the quantity of shares held by a number of the tblHoldings records for that same company ie a one-to-many relationship bewteen tblSellOrder and tblHoldings. In reality, I can have multiple sell orders reducing the quantity of shares for some of those same tblHoldings records as before until the share holdings for a particular tblHoldings rceord is reduced to zero. So I seem to have a many-to-many relationship bewteen tblHoldings and tblSellOrder. I've probably confused you with the above so let me give you a small example, although unlikely to happen.

    Today I buy 3 parcels of shares in XYZ company. Let's say I buy 500 in first parcel, 350 in second parcel and 400 in third parcel. Tomorrow I sell 2 parcels of shares in XYZ company. Let's say I sell 600 in first parcel and want to allocate 200 against the first bought parcel of 500 (leaving 300), 100 against the second bought parcel of 350 (leaving 250) and 300 against the third bought parcel of 300 (leaving 100). The second parcel sold is for 400 shares and i wish to allocate 200 against the first bought parcel leaving 100, 100 against the second bought parcel leaving 150 and 100 against the third bought parcel leaving 0.

    How do I set up my tables to be able to produce a holdings statement at any point in time and keeping track of the buys and sells that may be made from time to time?

    Maybe I'm being too ambitious in trying to develop a system to cater for such a situation and should revert to an excel based system where all entries, calculations are done with a lot of manual and duplicated effort.

    Any suggestions would be most appreciated.
    Attached Thumbnails Attached Thumbnails Relationships for ShareTrack.jpg  

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Could possibly have one table tblTransactions for both buy and sell data. Have another field for TransactionType.

    The allocation to 'parcels' you describe is like an inventory First In First Out model for valuation. Not easy to manage regardless of what application you use. It's a common topic so might search on that.

    If you aren't doing asset valuation, I see no reason to 'allocate' - just calculate quantity on hand. That is simple enough.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Why should you allocate sold parcels against bought parcels? If you buy 100, 200 and 300 shares of XYZ and sell 250, you hold 350. No need to store the 350; you (rather Access) can calculate that when needed.
    Groeten,

    Peter

  4. #4
    Golden_Orb is offline Novice
    Windows 11 Office 365
    Join Date
    Jan 2023
    Posts
    3
    Quote Originally Posted by June7 View Post
    Could possibly have one table tblTransactions for both buy and sell data. Have another field for TransactionType.

    The allocation to 'parcels' you describe is like an inventory First In First Out model for valuation. Not easy to manage regardless of what application you use. It's a common topic so might search on that.

    If you aren't doing asset valuation, I see no reason to 'allocate' - just calculate quantity on hand. That is simple enough.
    Thanks VIP - I'll give that a go over the weekend.

  5. #5
    Golden_Orb is offline Novice
    Windows 11 Office 365
    Join Date
    Jan 2023
    Posts
    3
    Quote Originally Posted by xps35 View Post
    Why should you allocate sold parcels against bought parcels? If you buy 100, 200 and 300 shares of XYZ and sell 250, you hold 350. No need to store the 350; you (rather Access) can calculate that when needed.
    Thanks - the reason to be able to allocate a sell against particular bought parcels is to exercise some control over capital gains tax that you may have to pay. As you may know, if you own an asset for 12 months or more you pay less CGT so you may wish to minimise your CGT by selecting buy records which are more than 12 months old.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Might find this of interest http://allenbrowne.com/AppInventory.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Quote Originally Posted by Golden_Orb View Post
    As you may know, if you own an asset for 12 months or more you pay less CGT
    I did not know that. I didn't even know what CGT is. That may be because I come from the Netherlands

    In that case you could link sell orders to the holdings instead of to the stockcodes. In some cases, you may need to split a sale into two or more records. Like in my example, if you bought 100, 200, and 300, and you sell 400, you must split the sale into at least 2 parts.

    In theory, your current model could be sufficient (SoldQuantity can even go). After all, all data (dates, numbers, prices) that you need for an optimal tax statement is available. You yourself apply a certain algorithm when you determine which parcel you (supposedly) sell. Access should be able to do that. But I'm afraid that's getting a little too complicated.
    Groeten,

    Peter

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with Database Design / Relationships
    By jsmccloud in forum Database Design
    Replies: 19
    Last Post: 07-24-2019, 12:19 AM
  2. Table Design Confusion
    By drunkenneo in forum Database Design
    Replies: 2
    Last Post: 09-30-2014, 11:08 PM
  3. Database Design: Many-to-Many relationships
    By rmohaisen in forum Database Design
    Replies: 11
    Last Post: 02-13-2014, 12:23 PM
  4. Replies: 17
    Last Post: 11-13-2012, 03:28 AM
  5. Database Design/Relationships
    By sloft21 in forum Access
    Replies: 1
    Last Post: 10-31-2012, 09:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums