XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   Iain Sharp (iains@pciltd.co.uk) writes:   
   > So, I have a series of tables with different columns (but some   
   > 'common')   
   >   
   > e.g.   
   > Sales order items (oritem)   
   > Process order stock requirements (orstkall)   
   > Picking note items (pickitm)   
   > Miscellaneous manual stock reservation (   
   >   
   > Each table can provide a requirement for stock based on status of   
   > item.  I therefore have another table (allocation) which contains the   
   > outstanding requirements for stock, from which I need to create a   
   > relation back to each of these tables. Allocations for a particular   
   > row in any of these tables are stripped and rebuilt frequently.   
   >   
   > Each of these has a primary key of the same type (int)   
   >   
   > A cutdown of allocation is shown below   
   >   
   > This is causing us issues with software like crystal reports, where   
   > one cannot include the metadata in the link without effort, and thus   
   > 'spurious' links are arising.   
   >   
   > So, 1. would you strip the metadata from this table? and 2. How would   
   > you approach removing the metadata from this table?   
      
   If I understand this correctly, there is a a column in this allocation   
   table that specified which table the row refers to.   
      
   That is rarely a good design, and tend to cause problem. Really what   
   you should do is difficult to tell with so little information about   
   the full picture. And then cryptic column names do not help.   
      
   Am I right to understand that all these orstkall, picktim etc share a   
   common ID space?   
      
   My reflection is that either all these tables reflects distinct entities,   
   in which case there should be one allocation table per entity. Or   
   they are in fact all the same entity, or subtypes of one, in which case   
   there should be a common table which also owns the ID.   
      
   But as I said, with so little information it's very difficult to come   
   with design suggestions.   
      
   --   
   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)