开发者

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?


  1. 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 "

  2. Partitioning implementation. Have a look at this article for more information about Partitioning

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜