Database Design To Archive or Not to Archive?
Posted on 2008-02-06
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??