XPost: comp.databases, comp.databases.ms-sqlserver, comp.databas   
   s.oracle.server   
   From: wizofoz2k@yahoo.com.au.nospam   
      
   Job Miller allegedly said,on my timestamp of 3/06/2004 7:08 PM:   
      
      
   > put the most likely to be null columns at the end of the create table   
   > and when   
   > they are NULL, they will consume NO space.   
   >   
      
   The problem is when you may have two or more of these   
   optional 1:1 relationships.  What happens when you got a bunch   
   of columns set to NULL, another bunch set to values, then another bunch   
   set to NULL again, and someone comes along and fills the first bunch   
   with values?   
      
   > benchmark it.  you would always have to join to pick up this optional   
   > information (2 or 3 LIO's at least per row retrieved for each optional   
   > set of   
   > data) vs an extra 50 bytes of flags saying "this is null".  I would go   
   > for the   
   > extra 50 bytes in a row that will be accessed via an index rather then   
   > incurring 2/3 LIO's to read an index to access another table. "   
      
   I wouldn't.  If it made the app easier to document, code   
   and expand, I'd trade the extra 2 or 3 lios easily.  Heck, I'd trade   
   10 or more lios easily, for that!  That is the sort of price I don't   
   mind paying.   
      
   Assuming of course I'm not designing/writing the   
   next 10000000/TPS TPC benchmark.   
   Then again, how many of those has anyone found in real life?   
   Then again, how many of those do indeed need a 1:1?   
   It's all relative, no?   
      
   In general, designs calling for that sort of level of   
   specification are not for performance critical apps, which makes   
   the extra lio quite acceptable.   
      
   > Your model and application are much more simple with this single table   
   > model.   
      
   I don't think so.  I have to add a heap of IS [NOT] NULL combinations   
   to handle existence or not existence of subtypes.  IS NULL is not   
   an easy condition to combine with others.  And it makes the code   
   unnecessarily dense, as well as stopping me from ever being able   
   to run this app in a db that doesn't support NULLS the way   
   Oracle does.  In all, not a good choice.   
   I'm afraid on that one I'll have to disagree with Tom, assuming once   
   again we are not talking about a TPC benchmark type of app.   
      
      
   --   
   Cheers   
   Nuno Souto   
   wizofoz2k@yahoo.com.au.nospam   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)