handling history/change tracking in the DB and consequences on the BL

Question

hi in the current application im working on the client requested that we will save history of every action that happended in the system and provide the ability to backtrack to the previous state of the information.

for example: lets say that my app need to handle a storage-room and each user can add/update/delete/read (all CRUD) the inventory

if the user added product P1 with action A1 and updated its info so it becomes P2 with action A2 and later on P3, P4 and so on. in the application the user will have a screen that will show all the evolution of the product and ascreen that shows all the actions that were made by users. the user could select a certain action and "undo" it.

at first glance i thought that i will store some kind of a Actions table with all the info i need about the action and when i need to undo and action i just revert the changes in code.

my problem is: if the product is not stored in one table but divided to few tables (because of optimization/understandability etc.) i need to figure out where exactly were the changes.

should i keep an indication in the Actions table of what tables were influenced and reflect it to the BL????

seems to me that there must be a better solution

Answer 1

perhaps using a more particular action so that the application will know exactly what todo

dont put logic on the DB

Answer 2

still seems a bit complex from the app side

Answer 3

Hi I did somthing like that once and to be able to revert changes you need to keep track on the field that change.

It doesn't matter where this fiels place as long as you know where this field placed. So you can make in your BL a kind of helper class that "know" where every field placed and map it to the right table->column in DB. this will reduce the strings and other DB elements in your BL.

Just put this helper class in DAL.

When you need to see changes (and/or revert them) send question to DAL...

By the way in MS-SQL 2008 there is built-in change tracking so you can use it to look for changes. - Change Tracking

Answer 4

thanks for the answer. but this solution scares me because of maintainability issues. could it be that im not follwing any of the design principles?