In my system, there are a number of different TradeBaskets. Each TradeBasket contains a number of different Securities. The Securities have a Type, Value, Sector etc... and new Securities are populated into the basket each day (and others can be removed).
Each TradeBasket has a unqiue list of EligibleEquity that can be accepted from the Securities within the TradeBasket. EligibleEquity can take the form of CommonStock, PfdStock, Reit, MutualFunds etc. My initial approach was to model the EligibleEquities as a multivalued attribute (instead of an entity). But I believe the best approach is to model EligibleEquity as an entity.
The TradeBasket also has a list of EligibleIndices that are accepted for the EligibleEquities in the TradeBasket. Thus, for each type of EligibleEquity in the TradeBasket, there is a list of associated EligibleIndices. It is true that each type of EligibleEquity(such as CommonStock, PfdStock etc...) can exist in a number of different EligibleIndices (such as S&P500, AS25X, CAC40 etc...) and that each EligibleIndices can be associated with a number of different EligibleEquities. Thus, I have assigned a Many:Many relationship between EligibleEntities and EligibleIndices.
In the ERD provided, I have colour coded the relationship between the TradeBasket and EligibleEquities and EligibleIndices.
In one case (red), I have mapped the EligibleEquity and EligibleIndices as a 1:Many relationship with the TradingBasket. In this case, I consider EligibleEquity and EligibleIndices as multi-valued attributes of the TradeBasket. Thus, in order to normalize the TradeBasket, the EligibleEquity and EligibleIndices are placed in separate tables (using the primary key of TradeBasket as the foreign key in each EligibleEquity and EligibleIndices child table). However, is it possible to create a Many:Many relationship between ElibibleEquity and EligibleIndices if they are children to the TradeBasket?
In the other case (purple), I have mapped the EligibleEquity and EligibleIndices as a Many:Many relationship with the TradingBasket. In this case, I consider EligibleEquity and EligibleIndices as entities in their own rite. and the Many:Many relationship between EligibleEquities and EligibleIndices is straightforward.
To facilitate a relationship between EligibleEquity and EligibleIndices, what the cardinalities should I adopt (red or purple - with respect to the ERD)?
If the EligibleEquity and EligibleIndices should be mapped as multivalued entities of the TradingBasket, what would the primary key of the "junction" table between EligibleEquity and EligibleIndices be?
This question has a similar discussion, but doesn't fully answer my question.
Thanks for any help!