4

I would like the design a financial instrument terms and conditions database and can see 2 possible solutions. The first is what I call the minimal design that I see as having the greatest flexibility. The second would invovle having a Master database with a cascading update system into sub-tables that store the finer detail of each distinct security type.

As an example to illustrate these concepts. For the minimal design, I think only 4 columns are needed. This table I see as being the most versatile as Derivative instruments should easily be incorporated by including the new AttibuteName's

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 does allow for exotic derivatives that have barrier triggers to be changed easily as the SecurityType can be changed at a later date when the trigger-event changes the security characteristics. This method also preserves the securitie's "old" characteristics which are retained (the UpdateDate defines the effective date upon which a securitie's details change). The only problem with this is that one would need to build a Pivot Type Query where the distinct AttributeNames are then transposed to be the column names of a new Pivot Table. (I am not sure what the correct term is but lets call it inflating 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

Where AttributeName's are common to all securities (Name, Ticker, Currency), these columns would be stored in a SecurityMaster Table. Where instruments have "perculiarities", there would be a cascading update sub-table where the particular contract details would be held (for instance SecurityBond, SecurityEquityFuture, SecurityEquityOption, SecurityInterestRateFuture, ...)

Hopefully you can see the 2 paradigms. The first table is very compact and easily accomodates any security. The only problem with 1 is that one would need to build the Dynamic Pivot table code that essentially builds the individual SecurityType tables that would be created under paradigm 2.

Any comments/pointers would be welcome and apologies if anything is not clear (or where I have not used formal DB terminology). Especially if someone has the Pivot code to get from paradigm 1 to paradigm 2.

Many thanks and kind regards, Bertie p.s. Experience: 2-3 months of working with Sql Server Express (in a company with no Software Engineers).

Bertie
  • 1,163
  • 3
  • 14
  • 26
  • There is an alterantive - have you considered using an XML field and storing some fields in XML? ;) You can have different schema and extend them, qerying is faster than the EAV model. – TomTom Dec 19 '11 at 19:20
  • I won't post this as an answer because the context seems to be clearly limited to SQL Server (and I've already upvoted one of the answers), but are you committed to using relational storage? The difficulty in storing such data relationally is that the relations (tables) themselves are difficult to define, large and change often (new instrument types). It may make more sense to investigate NoSQL alternatives, e.g. RavenDB, MongoDB. See http://martinfowler.com/bliki/DatabaseThaw.html for some high level motivation. – Adam Ralph Dec 20 '11 at 07:51

2 Answers2

3

Several poor ways:

  1. Full on EAV: this is your first proposal above
  2. Some normalised tables (ISIN, Name, Issuer, Currency etc) and the rest EAV

I've worked on both in previous roles and they will bite you eventually
EAVs are anti-patterns: One, Two

Better ways:

  1. Superkey/subtype pattern (SO answer 1) with an instrument table then child table for each type: Structured Product, Derivative, Bond, Share etc: this is your 2nd option above
  2. Fully Separate tables per type

With full separate tables you can have the common data access via a view or denormalised table, but I'd go with the Superkey/subtype pattern

Also see this SO answer too for how to do it (MySQL but hopefully you get the idea) and on DBA.SE too

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
1

Your first approach is known as EAV data model, whereas the second is regular relational model. EAV being an open schema data model, it provides you greater flexibility for defining new entities (securities in your case) or extending existing ones. But as you can't create indexes on the dynamic pivots tables, the performance will be very poor for complex queries on large data sets.

Alternatively you can create materialized views for the pivot tables of each entity and create indexes on them. But every time an entity changes you will have to rebuild the materialized view.

Raihan
  • 10,095
  • 5
  • 27
  • 45