Database Design To Archive or Not to Archive?

Posted on 2008-02-06
Medium Priority
Last Modified: 2010-04-21

I am develping an application for my college.  Each semester instructors have to create a course portfolio which includes course objectives, materials, lessons, etc.  

The format is determined by the curriculum committee.  I want instructors to ceate an electronic portfolio.  No problem.  The issue is regarding the archiving of old portfolios.

Should I create a separate tblportfoliohistory table since I cannot display old portfolios in the way they were originally designed if the college changes the format template or make a semesterid field in the tblPortfolio table that would sort them by semester?

I like the history table since a lot of data manipulation will ocur in current portfolios, so speed is important - a lot of searching, inserting and updating.  The history table would rarely be used except on the occasions where an old portfolio needs to be accessed (no editing of archived portfolios is possible).

Creating extra tables is usually a db no no but is the performance tradeoff (if any) worth it.  What say you??

Question by:vstack
  • 2

Accepted Solution

bradleys40 earned 2000 total points
ID: 20833152
I have created archive tables in many DB's in the past and the performance has been fine, if the tables are heavily used the the least data and a good index design will stand you in good stead.

Author Comment

ID: 20834522
Thanks for the advice.  I'm still thinking about it.

Author Closing Comment

ID: 31428526
Thanks for taking the time to respond.  I have decided to create archive tables since 98% of the db activity will be on the current tables.

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question