So I’ve began modelling some entity relationships.
It goes as follows:
Mandatesymbolize a consumer. A row for a consumer (Mandate) will solely seem as soon as within the
Mandatecan have a number of
Asset, and the identical
Assetcould be had by totally different
Mandates. That is relationship is modeled by way of the
Mandate Asset Bridge, by which a
Mandate IDwill likely be linked to a number of
The purpose of the
Assetdesk is for an asset to be on a single row of the desk (thus no two similar belongings will seem).
The difficulty comes when contemplating
Safety. The safety are parameters for
Assetand are depending on the
Security2. There can’t exist an entry for
Safetywith out it being linked to an
In the mean time, given the mannequin, the identical
Assetwill must be duplicated for all totally different
Safetyparameters. So whereas in
Asset, the Bloomberg ID, Identify, Underlying, and so on… will at all times be the identical, within the instance above
Asset1will must be seem twice, as soon as with the
Security1 FKand as soon as with the
This to me breaks some normalisation i.e., if the Bloomberg ticker adjustments, I’ve to undergo the
Assetdesk, and alter that each one occurences of the identical
On doable approach of fixing this I suppose is to create a three-way bridge desk, which in my case would comprise MandateID, AssetID, and Safety ID.
I’ve by no means encountered a scenario like this. Is that this a great way of doing it? Intuitively I might say no, it doesn’t appear very elegant, however I am unsure.
Let me know if something is just not clear. Thanks!