How to deal with Historical Data

[padlocked17]

Here is my scenario. We run a school where students will come back multiple times over the course of a few years. We are currently creating a student profile with a table and then corresponding students with a class number and the respective qualification type they are coming for in a separate table.

In the class table, we have a start date and a closed date. Essentially when all students from that class have processed through, the class will be closed.

My question is how do I deal with all of that data? I'd like for the historical data to be saved and able to be opened later, but I don't want anything associated with a closed class to appear in the db in queries, forms, drop-downs, etc.

I've attached a sample so you can see what I've got so far and if clarification is needed, please let me know. Just trying to figure out the best way to deal with this.

 

[pbaldy]

I'm off to a meeting so don't have time to look at the sample, but I'd leave the data in there. Anyplace you don't want to see closed classes, use a query that excludes them (WHERE ClosedDate Is Null).

 

[Steve R.]

At first glance, I think you have three possible solutions.
1. If there is an entry for the CloseDate then you can test to see if the class is closed or not. Something along the lines of ISNULL(CloseDate) then the class is open or ISDATE(CloseDate) then the class is closed. I don't like this approach.
2. You can create a boolean field in your TBLClasses. When the class is closed, the value of the boolean field could be set to true as well as entering the close date. With this approach you could use a query to select all records where that boolean field is either true or false. This is the approach I tend to use.
3. It is possible that your classes could have several states: Not yet open, pending, enrolling, closed, canceled, etc. If that is the case, instead of having a boolean field, you may want to have an integer field that identifies the current state of the class.

A potential benefit of approach #3 is that if a student enrolls and the class is canceled the student could be informed should the same class be re-offered.

 

[padlocked17]

Thank you both. I think I'll add in a new column that has the status. Is there a way that I can write some code somewhere that basically says: "IF Start Data and Closed Date are NOT null, THEN class is open ELSE class is closed and then apply the correct integer from a lookup table to apply the status?

Maybe place this for when the database opens up to run tha snippet of code?

 

[pbaldy]

I disagree with ortaias' option 2, though in part it's simply a personal preference. I would say that a boolean "closed" field on top of a closed date field is a normalization issue (in essence you're storing the same piece of info twice). I can examine the date field just as easily as the boolean field to determine which are open/closed, and I don't have the extra field to try to keep updated. The question you're asking would appear to prove my point. You're going to jump through hoops to keep it linked with the date field; why not just look at the date field?

I would agree with option 3 however, if the situation called for it.

 

[padlocked17]

Really that does make sense when you talk about normalization. The fact of the matter is we either have it as open or closed. We don't have any inbetweens. I suppose if the closed date field only allows a date to be entered, then it's same to assume that if date is put in there, that class is closed. I'll probably run with the WHERE statement in the SQL.

I like it, thanks so much everyone.

 

[padlocked17]

SELECT tblClasses.ClosedDate, tblClasses.ClassID, tblClasses.ClassName
FROM tblClasses
WHERE (((tblClasses.ClosedDate) Is Null));

Did the trick for starters anyway. Thanks!