XPost: comp.databases, comp.databases.ms-sqlserver, comp.databas   
   s.oracle.server   
   From: sommar@algonet.se   
      
   Robert Brown (robertbrown1971@yahoo.com) writes:   
   > The only solution I found for this is a generalization hiearchy where   
   > you have the employee table with all generic attributes and then you   
   > add tables for each new employee subtype as necessary. The subtype   
   > tables share the primary key of the employee table. The employee table   
   > has a "discriminator" field that allows you to figure out which   
   > subtype table to load for a particular entity.   
   >   
   > This solution does not seem to scale since for each value of   
   > "discriminator" I need to perform a join with a different table. What   
   > if I need to retrieve 1,000 employees at once?   
      
   So what? 1000 rows for a modern RDBMS is a breeze.   
      
   > Is that possible to obtain a single ResultSet with one SQL statement   
   > SQL?   
      
   Yes, although with many discriminators, it will be one hell of a join:   
      
      SELECT main.col1, main.col2, ... d1.col1, d1.col2, ...   
      FROM   main   
      LEFT   JOIN discriminator d1 ON d1.keycol = main.keycol   
      LEFT   JOIN discriminator d2 ON d2.keycol = main.keycol   
      ...   
      
   In practice, things might be even messier, because some values might   
   apply to four discriminators, but be irrelevant to the rest. In   
   they the would be four different columns. Although, this could be   
   addressed with:   
      
       common_to_four = colaesce(d3.common, d6.common, d9.common, d11.common)   
      
   But it may be better to add this as a nullable column to the common   
   table.   
      
   Likewise, if two discrimiators are very similar, it may be better to   
   lump them in the same table.   
      
   > I guess another approach would be to use name/value pairs but that   
   > would make reporting really ugly.   
      
   And you get less control over your spelling errors. But sometimes this   
   is the way to go.   
      
   > Seems like a very common problem. Any ideas? Is this a fundamental   
   > limitation of SQL?   
      
   Sort of. SQL tables are squared, and object-oriented hierachies are   
   jagged.   
      
   But that's alright, just don't be too object-oriented. Be pragmatic too.   
      
   --   
   Erland Sommarskog, SQL Server MVP, sommar@algonet.se   
      
   Books Online for SQL Server SP3 at   
   http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)