How to create persistent data model for the future?

I have a concern that always accompanies me on the consistency of the data model with respect to future changes and backwards compatibility.

Assuming we have an application that cycles through periods (each year) and a model where a portion of their data, these organized hierarchically and that this hierarchy may or may not change between periods, there are implementations where it simply separated into a different database each cycle, but is the problem of data interoperate between cycles, as we can keep this hierarchy and its changes in each cycle, without having to store the entire hierarchy each cycle, since it does not necessarily change much less change it all, but there is that possibility.

Example:

academic information system, we have a hierarchy of subjects in each knowledge area

• Mathematics
  • Algebra
  • Tri开发者_运维问答gonometry
  • Arithmetic
• Social Sciences
  • History
  • Geography

now based on this hierarchy holdings keep the qualifications of each student in the 2010 period, now in the period following 2011, the hierarchy changes

• Mathematics
  • Trigonometry
  • Arithmetic
• Algebra / * here's a change * /
  • Algebra
• Social Sciences
  • History
  • Geography

or

• Mathematics

  • Trigonometry
  • Arithmetic / * here's other change no more algebra * /

• Social Sciences

  • History
  • Geography

the system is working and continue to keep the grades of students in the period 2011, now a student needs its past rating period, but the hierarchy has changed, as you can get the system the previous hierarchy

how I can fix this problem?

---

Here is a modeling suggestion: a subject entity should have attributes

• subject_id (unique primary key)
• name
• superordinate_subject_id (if empty, you have a top node in your hierarchy)
• lifetime (from_year, to_year; when to_year is empty, it is the currently active subject)

Subjects of similar names should not have overlapping lifetimes. Every time you make a change to an active subject in the hierarchy, make a copy of the subject and change the lifetime fields accordingly. As long as the hierarchy does not change, you have nothing to change in your data.

To match your example:

subject Mathematics, lifetime: from_year=2010, to_year=NULL
    Algebra:  lifetime: from_year=2010, to_year=2010
    Trigonometry: lifetime: from_year=2010, to_year=NULL
    Arithmetic: from_year=2010, to_year=NULL

subject Algebra: lifetime  from_year=2011,to_year=NULL
    Algebra: lifetime  from_year=2011,to_year=NULL

Another option is to have a single "year" field in your subject instead of a lifetime; that may be a much simpler solution, better suited to the case when you want to store a different grade for each student per subject per year. But that would mean to store the entire hierarchy each cycle, what is what you excluded.

---

Don't mix up the identity of each subject and its position in the heirarchy.

If I got a B+ in Algebra in 2010, that data looks like:

ClassID  StudentID   Grade
100      100001      B+

the ID of the 'Algebra' class shouldn't be changing when your categories change.