MAY TO MANY RELATIONSHIP

[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.Many SHARES may have many DIVIDENDS and vice versa ,at any given time.Your help will be highly appreciated!!!


Please help

[Fredo0709]

Hi fadone,

I'm not an expert on database design but from what I've been reading, a many to many relationship can be achieved by setting two one to many relationships. You will need another table that will be able to act as a sort of lookup table between the SHARES table and the DIVIDENDS table.

[matthewspatrick]

I suspect what you really need is a structure like this:

Table: tblInvestmentTypes (i.e., common stock, preferred stock, coupon bonds, options, etc.)
InvTypeID (PK)
InvTypeName
InvTypeDescr

Table: tblCompanies
CompanyID (PK)
CompanyName
CompanySector
<other attributes of the entities--may be govt--you invest in>

Table: tblInvestments
InvestID (PK)
InvTypeID (FK)
CompanyID (FK)
InvestName
InvestDescr
<may have other fields for things like maturity dates, tax status, etc. Some fields may be N/A for some invest types>

Table: tblInvestBuySell
BuySellID (PK)
InvestID (FK)
BuySellDate
BuySellUnits
BuySellPrice
BuySellCommission
<possiby other fields related to transactions>

Table: tblInvestIncome
InvIncomeID (PK)
InvestID (FK)
IncomeTypeID (FK)
InvIncomeDate
InvIncomeAmount

Table: tblIncomeTypes (i.e., dividends, interest, capital gains distribution, etc.)
IncomeTypeID (PK)
IncomeTypeName
IncomeTypeDescr



That should be enough to get you started; you may need additional tables depending on what you want to track (individual accounts? transactions with individual brokers? other things?).

Overall, I suspect you would ultimately be better off with a package like Quicken or MS Money. Both are quite good, and very reasonably priced (especially if you purchase them together with tax software).