Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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]

enter image description here


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

Which DDF would be appropriate for this domain?

share|improve this question
    
I have implemented design 2 approach and I am trying to run the following query: SELECT d.name, d.cusip, d.isin, p.orig_price, p.price, p.cmbs_spread, p.cmbs_price, p.size, p.price_type, d.deal_type, p.date FROM dbo.price_2 p INNER JOIN dbo.deal_2 d ON d.cusip = p.cusip ORDER BY p.date. The problem I am having is that I have 109 instances in the database (sqlServer) but the query is outputting 11339 instances and I am not sure why? Any help would be very much appreciated. –  user1574185 Sep 27 '12 at 14:24
    
How many records do you have in price_2? The syntax of your query is fine, the results you're getting is because that is how many combinations there are of deal_2.cusip and price_2.cusip. There must be a lot of repetition in the cusip values in each table. –  Joel Brown Sep 29 '12 at 12:36
    
@Joel Brown, Thank you so much for your response. I have 109 records in Price_2 and Deals_2 tables. The cusip column has the following data type: (cusip VARCHAR(9) NULL). I am assuming the duplication is caused due to the null data types in the dataset. How can I resolve such problem, that I get 109 records instead of 11339? Thank you for your help and time. –  user1574185 Oct 1 '12 at 9:28
    
Have you tried taking nulls out of the equation by adding a clause like WHERE p.cusip IS NOT NULL AND d.cusip IS NOT NULL? –  Joel Brown Oct 1 '12 at 11:42
    
Dear @Joel Brown, I had tried using the Where clause before which resulted in no data output. The cusip column has 36 null entries in the both of the tables. Apology for not mentioning that in my earlier post. I am not sure, how to make this query work. Thank you for your time and help. Much appreciated. –  user1574185 Oct 1 '12 at 12:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.