Design a relational database (Access) that identifies breaches in records based on user defined limits

Question

I am trying to create a structured database in Microsoft Access that monitors trading risk. Let's say an organisation wants to analyse all of the trades that it has in its books at any given point in time. In this case, a trade would be an exchange of goods for some form of collateral. Collateral can be stocks, equities, bonds cash etc... basically something that has value that can be traded.

Each trade will have its own unique set of limits. For example, the securities (collateral) that make up each trade can only be equities from approved indices (NDX Index, HSI Index , and so on...). Another example may be that a maximum of 10% of the trade amount can be made up by each security.

When the trade data is imported into the database, I want to flag or extract all of the trades where the unique limits for each trade have been breached. I don't necessarily want to delete the trade records, but I want to possibly group them into a separate table for easy viewing.

Question on Database Structure

I have attached a picture of the (simplified) proposed database schema with the tables in question. Let's say I want to set a limit on the acceptable indices that the particular trade can accept. For this limit on index example, is the structure of the database schema correct for enabling the creation of a query that checks for a given trade: The limit of acceptable indices to be made of of 80% Primary and 20% Secondary and of those indices, I want indices that are restricted to Spain and Japan?

If the securities associated with a given trade can change on a daily basis, I want to ensure that all of these new securities in this particular trade follow each of the limits imposed on the trade. In this case, a limit on acceptable indices, however different trades may have a different set of limits not only for indices, but for concentration etc.. I don't want to have to write unique queries for each trade (for example, I may want securities of a different trade to be 100% in primary indices and that the are all indices from USA).

If the limits for indices, concentration, approved countries etc. for a given trade are breached for the securities that are contained in that trade, then I want these particular trades flagged to the user.

Note: I have been exploring the option of putting all of the limits as attributes for each trade, but I don't know how I can create a generalized query structure that allows me to identify limit breaches without creating massive queries.

Any suggestions would be greatly appreciated.

Answer 1

Initial observations are that Index Type and Index Composition are attributes of Index, not of Acceptable_indices. There will be relationships between a security and an index; whether this is important to your use case or not can't be determined from the current information. Concentration should relate to another entity type saying what it is a concentration of.

More generally you need to distinguish what is an acceptable set of values from what was actually in the transaction executed. The acceptable set is your risk and exposure policies documented in the form of a normalised data model. You have to think carefully of all the conditions, including the obscure, infrequent and exceptional ones. For each executed transaction the model must capture sufficient information to determine if it meets the risk policies or not.

There must be a link between an individual transaction and the set of rules that apply to it. The easiest way is to set the rules by security type e.g. one set for bonds, one for equities, one for futures, one for options etc.

Another complication is the time-dependent nature of the data. I'm guessing this application will be for analysis after the transactions are complete, rather than pre-transaction authorisation. Likely there will be a number of days between transaction and analysis. So you need to know what rules were in place when the transaction occurred. Everything must have start and end dates (or datetime values, depending if things can change intra-day).

For a physical database design you could implement one table per entity type. This is the usual way in the absence of a strong incentive to do otherwise. Queries may be longer from this approach (if there are many limiting factors the query will have to bring in the table with limit's allowable values and the table including the actual executed value for each factor). Queries are very obvious, however, and easy to implement and maintain. The runtime engine should have not problem executing them.

Alternatively each limit could be considered as a feature of the deal. The tables reduce to bags of feature values. To check if an executed trade breaks a limit one compare the "allowed" bag of values to the "executed" bag of values. This would be a variation on the Entity Attribute Value (EAV) design pattern. Often this pattern is considered dangerous. Queries are obscure as everything is a "bag" or a "feature"; it is never obvious what each particular feature represents. Writing queries to ensure the limit's feature is compared to the corresponding feature on the trade can be painstaking work. Maintenance even more so. Performance tends to be worse because this is adding an extra layer of abstraction on top of how the engine expects to work. It is wonderfully flexible, however. If you get things just right adding another feature can reduce to a simple cut-and-paste.

A third option would be to hold each value as an attribute. This make the tables very wide. The DBMS will be able to cope with this (up to a limit!) but makes things difficult to read. The real problem comes when there can be multiple acceptable values. For your example there may be two acceptable indexes in the limits and two indexes involved in the trade. You created columns index1 and index2 on each of Limit and Trade table. To check validity one must compare all possible combinations i.e.

(
    limit.index1 = trade.index1
and limit.index2 = trade.index2
)
or
(
    limit.index2 = trade.index1
and limit.index1 = trade.index2
)

This becomes ugly real quick.

My suggestion is to implement it one table per entity. Write the queries and test them. They can be written incrementally, checking first for, say, country validity, then country and index, then country, index and counterparty and so on. If this approach surfaces a particular problem, pause and re-evaluate in the light of the new circumstances.