A look up table, single generic Temp Table which will hold New, Deleted and updated records & a single generic History Table to move all records from Perm tables(at the EOD) to History by looking for data in the Temp Table for deleted records wud do...am wud not be over head for any performance related issues.
With this you dont have to have flag columns in Perm tables..since all actions could be interpreted by having a flag column in generic Temp table & possible History table...
Look up table can contaion the Tbl Name, Date and flag, so u know which table is acted and need proceesing at EOD.
At EOD wat we could possibly do is have some proc or some stored code...which will look into Temp & Look up tables and act acordingly on the perm tables ( Like New Entry -> insert, Delete -> Move to history, Update -> Update perm tables' rec ).
Well then with having single generic TEMP table will call for too much mapping....but that will be one go.
Well again it depends on Requirement.
Abhay.
