The best design of financial instruments. Database design (minimal and complex).

Question

I would like to create a database of financial instruments and see two possible solutions. Firstly, this is what I call the minimal design, which, as I see it, has the most flexibility. The second query will have a base database with a cascading update system in sub-tables, which stores more subtle details of each individual type of security.

As an example, to illustrate these concepts. For minimal design, it seems to me that only 4 columns are needed. This table, which I consider the most universal, as derivatives should be easily included, including the new name AttibuteName

UpdateDate SecurityID AttributeName AttributeValue ----------------- ----------- --------------- --------------- 09/12/2011 18:01 1 Name HSBC Plc 09/12/2011 18:01 1 Ticker HSBA LN Equity 09/12/2011 18:01 1 SecurityType Equity 09/12/2011 18:01 1 Currency GBP 09/12/2011 18:01 1 Country UK 11/12/2011 12:23 2 Name RBS 6% 15-Mar-2015 11/12/2011 12:23 2 Ticker XS0000000123 Corp 11/12/2011 12:23 2 SecurityType Bond 11/12/2011 12:23 2 Currency EUR 11/12/2011 12:23 2 Country UK 11/12/2011 12:23 2 Coupon 6% 11/12/2011 12:23 2 Maturity 15-Mar-15 11/12/2011 12:23 2 CouponFreq 2 

While most financial instruments do not change their properties, this method makes it easy to change exotic derivatives that can be changed using barrier triggers, since SecurityType can be changed later when the trigger event changes security characteristics. This method also saves the stored "secret" characteristics of securitie (UpdateDate determines the effective date of the change in the security data). The only problem is that you will need to create a Query Query Query where the various attribute names will then be transferred to the column names of the new pivot table. (I'm not sure if this is the right term, but let it inflate the data).

This is a glimpse of how I see the second solution.

 Name Ticker SecurityType SecurityID Currency Country --------- --------------- ------------ ---------- -------- ------- HSBC Plc HSBA LN Equity Equity 1 GBP UK 

If the AttributeName is common to all securities (Name, Ticker, Currency), these columns will be stored in the SecurityMaster table. In cases where there are “features” in the tools, there will be a cascading update subcategory in which specific contract details will be stored (for example, SecurityBond, SecurityEquityFuture, SecurityEquityOption, SecurityInterestRateFuture, ...)

I hope you see two paradigms. The first table is very compact and easily accommodates any security. The only problem with 1 is that you will need to create the Dynamic Pivot table code, which essentially creates separate SecurityType tables that will be created according to paradigm 2.

Any comments / pointers would be welcome and apologized if something is unclear (or where I did not use formal DB terminology). Especially if someone has a summary code to go from paradigm 1 to paradigm 2.

Many thanks and kindly, Berti postscript Experience: 2-3 months of work with Sql Server Express (in a company without software engineers).

Answer 1

Some unsuccessful ways:

• Full on EAV : this is your first sentence above
• Some normalized tables (ISIN, name, issuer, currency, etc.), and the rest EAV

I worked in previous roles, and eventually bit you EAV are anti-patterns: One , Two

The best ways:

3. Superkey / subtype template (SO answer 1) with a tool table and then a child table for each type: Structured Product, Derived, Bond, Share, etc .: this is your second option above
4. Fully split tables into types

With complete separate tables, you can share data through a view or denormalized table, but I would go with the Superkey / subtype template

Also see this answer in the same way as doing it (MySQL, but hopefully you get this idea) in DBA.SE too

Answer 2

Your first approach is known as the EAV data model , while the second is a regular relational model. EAV is an open data model of the scheme, it provides great flexibility for defining new objects (securities in your case) or expanding existing ones. But since you cannot create indexes on dynamic pivot table tables, performance will be very poor for complex queries on large datasets.

Alternatively, you can create materialized views for the pivot tables of each object and create indexes on them. But every time the essence changes, you have to rebuild the materialized representation.