Database Design To Archive or Not to Archive?


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??

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vstackAuthor Commented:
Thanks for the advice.  I'm still thinking about it.
vstackAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.