Hi,

I currently have a number of tables in my student database and I am just worried about some aspects of the design. It's about later changes that may be made to the tables. I have tables like:

Courses
-------
- courseID (PK)
- name
- fee

StudentFinance
---------------
- financeID (PK)
- studentID (FK)
- courseID (FK)

This is just part of the tables' definition as I want to focus on one aspect. Suppose, the fee changes over time, the data would become corrupt. What is normally done to prevent this?

The data doesn't have to be "corrupt" just because the fee changes. The data may become out of date, but keeping the data up to date is not a database design problem, it's a question of management and process.

If you want to record the history of values as they change over time then you need a "temporal" or "history preserving" model of your data. In other words your tables must preserve each change, usually as a new row for each new value.

The easiest way to keep history for this situation is add a Semester table (with attributes of year, semester # within year). Remove the fee from the Courses table and create a CourseSemester table (with CourseID, SemesterID, and fee). Then link the StudentFinance table to the CourseSemester table instead of the Courses table. That way you keep the fees for each semester and keep a record of what each student paid for each course they took (with the added bonus of when they took it).

The most efficient way to keep history for this situation is to add ValidFrom and ValidTo dates to the Courses table then add a CourseTaken date to the StudentFinance table. There is a decent article on temporal database design on Wikipedia.org to help understand how this would work.

when's the final project due?

Thnks for your replies. Very informative and concise. These are my other tables to help keep track of history of things.

TokenFees
----------
- tokenFeesID (PK)
- courseID (FK)
- tokenID (FK)
- fees
- academicYear
- date
- semester

AcademicsToken
----------------
- tokenID (PK)
- name
e.g of tokens would be: exams, dissertations, course

OwedFees
----------
- owedFeesID (PK)
- tokenFeesID (FK)
- date
- studentID (FK)

StudentFinance
---------------
- financeID (PK)
- financeEntityID (FK)
- studentID (FK)
- entityID (FK)

@dportas: So to the TokensFees table i only allow 'modify' the actual row if there are mistakes or errors but updates create a new row with a date.

@mark: The ValidFrom and ValidTo is interesting, I can integrate that with the Course table but I guess the semester thing is taken care in my tables isnt it?

@brett: isnt a final project or assignment my friend as many people think it is, or maybe it is because I want to finish this software (final project in this case) ASAP for a business school that needs to keep track of its students' accounts. They don't want a complicated thing because they are just business people like you know they want simple things KIS

Okay one more thing to history. What if the name of the course changes over time with slight modifications in its name? Then we wouldnt know the exact name of the course the student took at that time. Or is it necessary to do so? like i think dportas suggested once in a thread it's YAGNI thinking. But am just curious and want the program to be as informative as possible.

So in this case, will it be like dportas suggested to add a new row each time and also like mark said to add validFrom and validTo dates to the Courses table? I think that is the best combination dont you think? in this way everything stays on record over time.

I am not sure that I like OwedFees. I would call it StudentFees and add a paid amount (which could represent a full or partial payment) or a paid date.

Yes, since you have semester in the TokenFees table that takes care of at least part of your history. If you want absolute history then you add the ValidFrom and ValidTo dates to everything. This can be a difficult concept to handle for many, so I would get your basic design working first, then add the valid dates. (As always, my opinion )

thanks for your advice and opinion mark. appreciate it. I have another table to handle the full or partial payment thing although I like the change in name from OwedFees to StudentFees very much! always a problem to choose a suitable name.

Payment
--------
- paymentID (PK)
- amountPaid
- owedFeesID (FK)
- date

You did reply to one of my threads on this financial aspect of the project and was successfully adopted