2

So I have started modelling some entity relationships.

It goes as follows:

  • A Mandate represent a client. A row for a client (Mandate) will only appear once in the Mandate table.

  • Each Mandate can have one or more Asset, and the same Asset can be had by different Mandates. This is relationship is modelled through the Mandate Asset Bridge, in which a Mandate ID will be linked to one or more Asset ID.

  • The goal of the Asset table 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 Asset and are dependent on the Mandate, so Mandate1 could have Asset1 with Security1, and Mandate2 could have Asset1 with Security2. There cannot exist an entry for Security without it being linked to an Asset.

  • At the moment, given the model, the same Asset will need to be duplicated for all different Security parameters. So while in Asset, the Bloomberg ID, Name, Underlying, etc... will always be the same, in the example above Asset1 will need to be appear twice, once with the Security1 FK and once with the Security2 FK.

  • This to me breaks some normalisation i.e., if the Bloomberg ticker changes, I have to go through the Asset table, and change that all occurrences of the same Asset

Entity Relationship

Solution

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.

enter image description here

Let me know if anything is not clear.

Improve this question
4
  • Well second diagram looks better than first, (no weird FKs), however the rule "There cannot exist an entry for Security without it being linked to an Asset." does not hold. Problem is in wording, it is hard to understand. For example Asset has a ticker, say Apple Inc AAPL. Security has ISIN, say Apple US0378331005; but there is an association table in between? Describe relationship between asset and security. If mandate is a client (person, organization?) how do asset and security depend on it?
    – Damir Sudarevic
    May 15, 2020 at 17:26
  • The relationships between them is as follows: A client (or mandate) can choose to invest in one or more assets. The asset represent the underlying asset the client wants to invest it (so like S&P 500). The asset gets it associated with the security once the bank chooses where the assets gets traded. Thus the security denotes the venue where the asset gets traded, which is defined by its ISIN, and which trades in a specific currency, and has a given delay post trade for any buy/sell transaction.
    – George Well
    May 18, 2020 at 6:49
  • How many securities per an asset? Can a specific asset be associated with more than one security?
    – Damir Sudarevic
    May 18, 2020 at 9:32
  • Yes exactly, one asset (so say the S&P 500) can be traded either in Luxembourg, in France, in Spain, etc... (and each of these will have a specific ISIN). So S&P 500 traded in Luxembourg will have been traded in that exchange with the ISIN LU123 and the S&P 500 traded in France will have isin FR456.
    – George Well
    May 18, 2020 at 10:29

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.