"Archiving" and Normalization

[falkaholic]

I and trying to update an existing system for a school.

The school get applications that get stored in the Applications table (one a year per applicant). If approved, basic info is copied over to a Student table and have more detailed data added along with school courses etc.

Some student stay year over year, some don't. The school wants to "archive" non-returning students. For later reference or to bring back after a year off. Speaking database-wise there can be a few ways to handle this - but none of them strike me as the best method:

-Make a copy of the Students table (called Archived Studnets) and copy old ones out and in as necessary. Seems like poor design (and normalization).

-Flag each student record as 'Current' and have a bunch script that only find "Current" students. The problems is that this database doesn't hold the hands of the user. All find tools accessible. Leaving the user to work the database as t hey see fit; having finds/filter constantly seems likely to fail and very cumbersome.

-Do away with the Applications/Student and have one database of any typeof "student". Each with a status of App/Current/Old. This method doesn't really hand applications over multiple years (applications a big part of the school) and would seem to have some of the pitfalls of the last method. Would also be a major rework!

This seems like a simple and common thing to do, has anyone else tackled anything similar and found a good way to manage it?

[bcooney]

Yes. Another way is referred to as the "Graham Method." It is a technique that uses supertypes and FM's relationships to "filter" the supertype. See this thread.