DB design: static past, dynamic future

[Mandu]

FMP 6, Mac OS X

I'm building a simple Students/Classes-type database. A student enrolls in classes. A class is an instance of a course, plus a date ("Trigonometry I will be taught in the autumn semester and the spring semester" - Trig I is the "course"; autumn Trig I and spring Trig I are two "classes").

I have four databases: Students, Enrollments, Classes, and Courses. I use relationships (and some unstored calculations) to connect the information. If someone discovers a typo in a Course description or course fee, he can correct it and, because of the dynamic nature of relationships, this change is automatically reflected in the Class record and, further, in the Student's record (e.g. how much money he owes). Likewise, a Class record can be tweaked to change dates, instructors, locations.

This is all well and good until the future slides into the past: when the class begins (or ends) I don't want to allow anyone to change Class information, or in fact Course information for that class. For example, it may be decided that some extra material will be presented in Trig I, or that the price of the course will change, starting in 2006: they should be allowed to update the Course record without having this propagated to people who have already taken a Trig 1 class.

It's as if, at some point in time, I could sweep the databases for relationships and other dynamically-calculated fields, and turn those fields into Lookup fields in cases where the event is now in the past.

I'm sure this must be a common problem in RDB design. I'm not looking for a low-level implementation solution so much as for a high-level strategy to dealing with the static past and the dynamic future. For instance, do people create pairs of databases, one for the past and one for the future, and roll info from one to the other on a daily or weekly basis?

A very general question, I'm afraid - thank you for your consideration and patience!

Chap

[Philland]

What I have done in a few cases (which I'm not necessarily reccommending) is have three fields for each of what would normally be one field.

Field A, Field B and a Display Field.

Field A is the calculation or related data.

The Display Field is a calc

IF (IsEmpty; Field B, Field A)

IF (Not IsEmpty; Field B, Field :

Then I have a "Closing" script that's a series of Set Fields copying data from Field A"s to Field B's

This way I create a display of the state of a record when it was closed.

I have used this when updating a solution when the parameters had changed, but old records had to be maintained with no noticeable differences between old and new records.

It's a bit of a pain, but it has worked for my needs.

Phil

[Ender]

Hi Chap,

You have brought up a good question about how to use dynamic related fields when history must be preserved.

The strategy I use is two-part: Use a School Year or Fiscal Year field as part of the relationship key, and to use lookups to populate copies of certain fields, instead of using the related parent field.

The School Year field helps by distinguishing those things that are unique to each year, like in Section and Enrollment (see below.) Changes to the Room# for "Trig I Fall 2004/2005 Section 1" don't affect other school years. Because the Enrollment file is related to Section by CourseID, Section#, SchoolYear, and Semester, it can look directly at this Section to know what the correct Room# is (no lookup required.)

But a lookup might be used if there are fields that could change over time, but not necessarily from year-to-year, and it is important to know the history of what it was. An example could be Fee, where the rate changes during the year. This may not happen in real life, but lets suppose a school had an "Early Bird Rate", a "Regular Rate" and a "Late Sign up Rate". This could be kept in the Course file, along with a Fee calc that determines which of those rates to use at any given date. The Fee lookup field in Enrollment can then pull in the Fee data from Course when an Enrollment is created.

This is not shown on the diagram, but in practice, you can use global School Year and Semester fields to make filtered relationships to show just the Sections and Enrollments for the selected year and semester.