4060fc9f   
   From: esquel@sommarskog.se   
      
   wackyphill@yahoo.com (wackyphill@gmail.com) writes:   
   > I have a table that holds agreement information. It works well for 95%   
   > of the agreements we record.   
   >   
   > But there is a certain type of agreement that would require another 6   
   > or so fields to capture info specific to that type of agreement.   
   >   
   > My question is if its better to just add those 6 fields to the   
   > existing agreement table knowing that the info is meaningless to many   
   > of the agreement records or if its better to create another table w/ a   
   > 1:1 relationship w/ the original agreement table to extend it in the   
   > case of these special types of agreements.   
   >   
   > Neither option is all that attractive to me, but I wanted to know if   
   > one was considered a better practice than the other when you have a   
   > choice.   
      
   It's certainly a trade-off situation.   
      
   In the system I work with there is a table that holds contract notes.   
   Contract notes for bonds and other interest-related instruments requires   
   six extra columns which are meaningless for stocks. As in your case, the   
   bonds is just some 3-5% of this big table. Originally, the bond-specific   
   columns were in the table itself, but in order to preserve some space,   
   I decided at one point to put them in a table of its own. Which meant   
   many queries had to be rewritten and include an extra left join.   
      
   When I did this, the current SQL Server versions were SQL 2000 with SQL 2005   
   on the way in.   
      
   Now, in SQL 2008 there exists a different solution: I could mark these   
   columns as SPARSE, and these columns would not take up any space when they   
   are NULL. (The columns in questions are float and datetime values, so   
   unless SPARSE is used, they take up 8 bytes, NULL or NOT.)   
      
   I should add that there is a second advantage with using an extra table. If   
   there are constraints appliable to these columns, for instance if it is an   
   agreement of type X the columns A, B, and C must be NOT NULL, then this is a   
   lot easier to handle it there is a separate table. And in the same vein, if   
   it is a separate table, you can easier prevent that there suddenly is data   
   in these columns for other type of agreements.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)