Sql-server – How to design a database for financial bond prices

database-designsql server

I have following flat file table composed of bond deal names and asociated prices.

Each deal name is identified by its unique CUSIP and ISIN codes and each deal name is classified with a certain deal type.

One deal name can have many prices over the course of a day. Each price is classified with a different price type; as in the following table:

field names are shown in brackets like (XXX).

  • CSAMF 2A PREF (name) 12629B309 (cusip) 3,000,000 (size) M50S (original price) 55.00 (price) TALK (price_type) CLO (deal_type) 23/09/2012 (date)

  • CSAMF 2X PREF (name) KYG2577W2005 (isin) 2,000,000 (size) M50S (orig_price) 55.00 (price) TALK (price_type) CLO (deal_type) 23/09/2012 (date)

  • FN 465343 (name) 31381M5C5 (cusip) 2,613,100 (size) 80a (origin_price) 80a (cmbs_spread) 80.00 (cmbs_price) TALK (price_type) CMBS (deal_type) 24/09/2012 (date)

  • FN 465273 (name) 31381M2E4 (cusip) 1,760,000 (size) MH70s (origin_price) MH70s (Cmbs-spead) 77.00 (cmbs_money)TALK (price_type) CMBS (deal_type) 24/09/2012 (date)

Each deal name has either one CUSIP or one ISIN or both. Prices with deal type of CMBS (Commercial Mortgage Backed Securities) are recorded from the original price into CMBS spread and prices with deal type other than CMBS are converted into money prices.

I have constructed two possible solutions for the relational database, as shown below:

Design 1:

Deals (deal_ID, name, CU_code, IS_code, deal_type)

Price (price_id, orginal_price, money, cmbs, cmbs_money)

Deal_price (deal_id, price_id, date, size, price_type)

Design 2:

 CREATE TABLE [dbo].[deal_2](
[cusip] [varchar](100) NULL,
[isin] [varchar](100) NULL,
[name] [varchar](100) NOT NULL,
[deal_type] [varchar](100) NOT NULL

 CREATE TABLE [dbo].[price_2](
[price_id] [int] NOT NULL,
[cusip] [varchar](9) NULL,
[isin] [varchar](12) NULL,
[orig_price] [varchar](50) NOT NULL,
[price] [decimal](18, 2) NULL,
[cmbs_spread] [varchar](50) NULL,
[cmbs_price] [decimal](18, 2) NULL,
[size] [int] NOT NULL,
[price_type] [varchar](50) NOT NULL,
[date] [date] NOT NULL

enter image description here

Is it possible to construct a relational database design for this domain?

Which DDF would be appropriate for this domain?

Best Answer

Not strictly an answer... but... my 2cents (not interest bearing, might interest ing).

So, Security X has identifier (ISIN or CUSIP). This identifier doesn't identify the trade, exchange, currency - just the security.

This Security has value, established by trades - which fluctuates over time, place... This is tracked by deals. A deal requires a Security.

Price is parcel of deal.

I would create a table of Securities - Security (it has an ID int identity(1,1) PK), Code Unique constraint, CodeType (CUSIN/ISIN), linksToID (here is a funny: ISIN can have CUSIP too so link both to each other so you can identify the securities by either code) etc.

I would create a table of Deals - Deal (it has an ID int identity(1,1) PK), time, place, and Security.ID, and TYPE).

I would create a table of Prices - Trade (it has an ID int identity (1,1) PK, DealID, Value, currency, TIME, etc)

So, I would create a unique/distinct list of securities, and insert that into Security. Matching the Security to TradeReport (assuming the trade report to be the data you want to capture contains either ISIN or CUSIP but not neither) insert the ID of the matching ISIN of the trade data, and then inserting the matching ID of the CUSIP matching if a linked item isn't already there! to get a unique deal list.

Then I would reference the different deals back to the same import data and capture the prices of the deals to the prices table.

See the hierarchy?

from there you can splice and dice your analysis...

Yeah, that about covers my thinking... hope it helped.