Tracking changes and process pending approvals in SQL
I am working on this extranet travel site that allows users to update information about their properties. The site is not too complicated, but the one thing that makes this problem extremely difficult is the following:
Client wants users to update their info, but such information should be left with status "pending for approval", unitil the extranet admin actually approves the change. If approved this request should be archieved in some kind of history.
开发者_开发知识库I tried to figure out a solution and end up with possible solutions - keep records of changes in new table (requestID, requestDate, requesttype, request (store asp.net datatable as xml)) - duplicate record current_row as new_row and add requestID column in this table (this could drastically increase the size of this original table) - duplicate the structure of the original table, where we would hold only pending request (this could also lead to the numerous number of tables, depending on different request types and number of original tables)
Would appreciate any comments regarding my ideas.
What do you need beyond adding a status column with values pending/approved?
You should archive your data by separating in new tables by doing this you can improve query performance. From a performance point of view, if it is going to be obsolete data (It means after admitting the user request and keeping the history of it) that is never or rarely used, query execution can be time-consuming. This is because queries also scan the this obsolete data. So you should have separate table for keeping the history. So I will go with "keep records of changes in new table "
Partitioning implementation. Have a look at this article for more information about Partitioning
精彩评论