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
ON [PRIMARY]
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
ON [PRIMARY]
Is it possible to construct a relational database design for this domain?
Which DDF would be appropriate for this domain?
price_2
? The syntax of your query is fine, the results you're getting is because that is how many combinations there are ofdeal_2.cusip
andprice_2.cusip
. There must be a lot of repetition in the cusip values in each table.WHERE p.cusip IS NOT NULL AND d.cusip IS NOT NULL
?