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.