So I have started modelling some entity relationships.
It goes as follows:
Mandaterepresent a client. A row for a client (Mandate) will only appear once in the
Mandatecan have one or more
Asset, and the same
Assetcan be had by different
Mandates. This is relationship is modelled through the
Mandate Asset Bridge, in which a
Mandate IDwill be linked to one or more
The goal of the
Assettable is for an asset to be on a single row of the table (thus no two same assets will appear).
The issue comes when considering
Security. The security are parameters for
Assetand are dependent on the
Security2. There cannot exist an entry for
Securitywithout it being linked to an
At the moment, given the model, the same
Assetwill need to be duplicated for all different
Securityparameters. So while in
Asset, the Bloomberg ID, Name, Underlying, etc... will always be the same, in the example above
Asset1will need to be appear twice, once with the
Security1 FKand once with the
This to me breaks some normalisation i.e., if the Bloomberg ticker changes, I have to go through the
Assettable, and change that all occurrences of the same
One possible way of solving this I guess is to create a three-way bridge table, which in my case would contain MandateID, AssetID, and Security ID.
I've never encountered a situation like this. Is this a good way of doing it? Intuitively I would say no, it does not seem very elegant, but I'm not sure.
See below for what a three-way associative relation would look like.
Let me know if anything is not clear.