Last post Mar 08, 2011 07:09 PM by LudovicoVan
Mar 07, 2011 10:21 PM|pzn3xq|LINK
As I begin to write a new asp.net application 4.0 using Entity Framework, the customer has requested an archiving feature. The archiving feature would be responsible for trimming records older than (6 months). Few questions:
1) Any idea how or what should actually do the purge from the database?
2) Should this be a feature controlled within the database, or should this be something managed through the application?
3) Any suggestions where the purged content should go? Flat file? Historic DB? There may be times where we'll want to reload the purged content to view
If you can point me to any online examples how to build such a feature or user experiences that would be great.
Mar 07, 2011 11:38 PM|ignatandrei|LINK
1. a console program / a windows servie
3. a :history " table with the same structure as the initial data.
Mar 08, 2011 04:34 PM|atconway|LINK
There are (2) ways you might look at this:
I think the choice to decide between the above options is based on a lot of factors: is this a 1x single solution request, no DBA to help architect a fancy SQL solution, and time is of the essence... then go with #1. If this solution needs to scale, there
is a DBA on hand to help architect the solution (#2 is much more a database design challenge than a software one; #1 is almost completely a software solution), the process might grow, and the data will be massive, then #2 might be the better approach.
The links below give a little insight into the concept of a Data Warehouse:
Transforming OLTP Data to OLAP Data Warehouses:
Data Warehousing and Online Analytical Processing:
Data Warehouse Design Considerations:
You should check with the SQL forums if deciding to go with option #2. For option #1, the following to create a Windows Service may be helpful:
Running a Periodic Process in .NET using a Windows Service:
Hope this helps!
Mar 08, 2011 07:09 PM|LudovicoVan|LINK
Another way, more reliant on the database, and of course the best in terms of easiness of coding and maintenance (though some might disagree) and performance:
1) Write a stored procedure that does what is needed;
2) Schedule a job that runs the stored procedure;
3) Write the data to another table -- it might be in another database devoted to historical data only, as customary for OLAP.
There could be variations, like a scheduled DTS package instead (old terminology: now it's part of SSIS).