XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   Iain Sharp (iains@pciltd.co.uk) writes:   
   > Each of these tables are different entities, in that one is items for   
   > sale to customer, one is items to be used to make items for sale to   
   > the customer, one is items reserved to prevent their sale/use.   
   >   
   > What they all have in common is that they add up to the amount of   
   > requirement for the material in the warehouse. So I need to be able to   
   > sum them sensibly, and work backwards from this information to the   
   > originating table.   
   >   
   > A sales item will have many different columns from the production item   
   > or the stock reservation item, but they all have the same behaviour in   
   > common. They all reserve material for use later.   
   >   
   > This reservation data is then examined either in summary (add up all   
   > the reservations for a stock item) or detail ( show me the   
   > reservations of all types for this stock item, in due date order), or   
   > a bit of both, (add up all the reservations for this stock item due   
   > before this date). So I felt I needed an indexed table storing this   
   > information, hence ste_allocation. This then has a many to one   
   > relationship with rows in the other tables (one sales item may involve   
   > several allocations).   
      
   It is always difficult to answer database design questions on forums,   
   you only know so little information. Often database design is quite   
   difficult even when you have all information, because there are always   
   trade-offs.   
      
   But it seems to me that you should have some supertable for all these   
   tables, and in that table you would have a type column that details   
   the type of sale or what it is.   
      
   You would then use that column in the allocation table as well.   
      
   As for the ID space, you could make create a common space, but you   
   could also let the supertable have a two column key: (Type, ID),   
   but it would not work out with an IDENTITY column.   
      
   Whichever way you go, the subtables should have an FK to the supertable.   
   If you have Type in the PK, you need it in the subtable as well. But   
   in fact, it's a good idea anyway. Here's an idea that I learnt from   
   Joe Celko:   
      
      CREATE TABLE Supertable (ID   int NOT NULL,   
                               Type char(1) NOT NULL   
                                  CHECK Type IN ('A', 'B', 'C')),   
                               -- More columns   
                               PRIMARY KEY (ID),   
                               UNIQUE (ID, Type))   
      
      CREATE TABLE A_Sum  (ID int NOT NULL,   
                           Type NOT NULL CHECK (Type = 'A') DEFAULT 'A',   
                           -- more cols   
                           PRIMARY KEY (ID),   
                           FOREIGN KEY (ID, Type)   
                              REFERENCES Supertable(ID, Type)   
                           )   
      
   You will find that the solution is not very different from what you have   
   now with the allocation table, but you no longer have table names in   
   your data.   
      
      
   > They do not currently share the same ID space, but as I am currently   
   > changing their primary keys, they could do. If I do this, it will   
   > markedly increase the gaps in each of their ID sequences, and I was   
   > wondering if I should then be looking at fill factors (the IDs will   
   > always be increasing numerically).   
      
   Not really sure what you mean here. If you create a common ID space,   
   you should probably assign IDs as entities comes in. Whether there   
   is 1, 2, 3, 4 in a table or 1, 6, 12, 13 has no importance, as long   
   as they are monotonically growing.   
      
      
   --   
   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)