XPost: comp.databases, comp.databases.ms-sqlserver, comp.databas   
   s.oracle.server   
   From: laconic2@comcast.net   
      
   "Robert Brown"  wrote in message   
   news:240a4d09.0405270856.4ce55c7d@posting.google.com...   
   > Is there a good approach to modelling many heterogeneous entity types   
   > with that have some attributes in common?   
      
   This is a frequently asked question.  Unfortunately, the frequently given   
   responses don't  usually settle the matter.   
      
      
   > There are many employee subtypes and more can be dynamically added   
   > after the application is deployed so it's obviously no good to keep   
   > adding attributes to the employees table because most attributes will   
   > be NULL (since janitors are never doctors at the same time).   
   >   
   > 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.   
      
   If you invent new subtypes on an adhoc basis,  (e.g. a DBA is never either a   
   janitor or a doctor)   
   and you invent new tables for subtypes on an equally ad hoc basis,  I'd   
   suggest that you are altering your data model on an ad hoc basis.   
      
   You can do that if you want,  but don't expect the same power and simplicity   
   that you get from a stable data model.   
      
      
      
   >   
   > 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?   
      
   Why would you ever need the attributes of more than one subtype?  If your   
   query is about doctors,  why would you need to join the table about janitors   
   into the query?   
      
   If your query is about employees, why would you need to know any of the   
   subtype attributes?   
      
   >   
   > Or do you I need to iterate look at the discriminator and then   
   > perform the appropriate join? If this kind of iteration is necessary   
   > then obviously this generalization hierarchy approach does not work in   
   > practice   
   > since it would be painfully slow.   
      
   You need to ask two questions about the model,  and about any imlpementation   
   based on the model:   
   first, is it correct and complete?  Second, if yes to the first,  is it fast   
   enough?   
      
   In general,  different implementations of the same model should be   
   transparent to users of the imlpementation who only look at the features   
   visible in the model.   
      
      
   >   
   > Is there a better approach to modelling these kind of  heterogeneous   
   > entities with shared attributes that does not involve creating a table   
   > for each new employee type or having sparce tables (mostly filled with   
   > NULLS)   
      
   If you have new entities, you are going to have new relations.  That either   
   means having new tables,  or fudging the relationship between tables and   
   rleations.  Do the second one at your own peril.   
      
   > Seems like a very common problem. Any ideas? Is this a fundamental   
   > limitation of SQL?   
      
   I would suggest it goes beyond SQl to the very heart of using foreign key/   
   primary key aossciations to establish linkages.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)