Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    29

    Unanswered: many to many relationship

    I am using MS Access2000 for developing my Investment Management database.I am not very much into database designing but I have a general database knowledge.I have encountered a many to many relationship between SHARES and DIVIDENDS but I dont know how to handle it perfectly.In which case,Shares might be bought several times and also dividends might be given several times,at any given time.I am stuck on what to start with.....Your help will be highly appreciated!!!I can send the database snapshot if need be...
    Please help

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you are potentailly confusing the owning of the shares and transaction applying to share ownership. Strictly speaking you may have an asset which is a share, and that asses may, or may not pay a dividend, but thats not all that happens with an asset (it may be sold, split, liquidated or bought)

    if you think of the way in which you accquire the shares (either bought, gifted on share split, or from a great auntie agatha). having accquired your shares you may be entitles to dividends, or selling them.

    so if you have a table which contains investments, if you can grab a data feed identifying the equities
    you have another table which contains transactions
    eg
    TX TXType InvestmentID Value Qty ForexValue, ForexCode
    1 PUR 1 -1000.00 500 -1000 GBP 'purchase of 500 shares in Fadone plc
    1 DIV 1 10 1 10 GBP 'fadone plc pays out 2p per share dividen
    1 SAL 1 25.00 500 25 GBP 'fadone price tanks liquidator pays 5 p per share

    the one issue of going down that route is that you have to run an SQL query to work out what the current holding of a particualr investment is, which for JET is a heavy requirement

    its a SQL aggregate query thats required

    you need to work out what transactions are possible (so thats another table)
    you nee to work out what the effect of that transaction is

    a purchase, depletes cash on hand,
    a divident increases cash
    as does a sale

    the further complication is whether you want to handle forex. If you want to analyse invesment performance then ideally you need to know what the gain has been due to currency movements aswell as share prices.
    bear in mind that you may also have investments which don't pay dividends, some gift shares in lieu of dividends, some invesments may pay interest, some may pay income. Generally in invesment terms you have 2 possibly 3 classes of investment Stocks & Shares, Cash & Bonds
    HTH

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by fadone
    I am using MS Access2000 for developing my Investment Management database.I am not very much into database designing but I have a general database knowledge.I have encountered a many to many relationship between SHARES and DIVIDENDS but I dont know how to handle it perfectly.In which case,Shares might be bought several times and also dividends might be given several times,at any given time.I am stuck on what to start with.....Your help will be highly appreciated!!!I can send the database snapshot if need be...
    Please help
    Hi fadone1
    The way to handle M:M relationships is to break them up into two M:1 relationships. Create a "SharesPaysDividends" table, the primary key a compsite key of the primary keys of Shares and Dividends and referencing each of these as foreign keys. In the interests of advocating the surrogate approach, you could make these two fields a unique index on "SharesPaysDividends" and create an autonumber to act as the primary key.

    A text on relational theory to bring you back up to speed on this sort of stuff

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2005
    Posts
    29
    Quote Originally Posted by healdem
    if you think of the way in which you accquire the shares (either bought, gifted on share split, or from a great auntie agatha). having accquired your shares you may be entitles to dividends, or selling them.
    Thanks very much healdem.Yes,assuming that I have acquired shares,they would be entitled to Dividends and Selling them.Ofcourse,my ONLY concern here is what types of relationships exists between SHARES PURCHASED , DIVIDENDS and SHARES TO BE SOLD?????Currently,shares might be bought several times before entitled to dividends(sound like M:M relationship?) ,and can be sold at any time thereafter....How to design a meaningful and consistency database???
    I will really appreciate your support.Thanks.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you don't have a M:M relationship
    My mapping of the problem would be a table to identify assets / investments
    A table containign transations
    a Table containg transaction codes (identifying if the result of the transaction was positive or negative [ie a purchase or sale of that asset (bearing in mind of course that you may buy or sell in incremental blocks
    effectively its a 1:m from asset to transaction. its my understanding (in the UK at least) that when buying shares you complete an individual bargain ie even though you could complete many purchases or sales in one phone call each line is a separate transaction.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •