How to handle costs that change frequently in a school database

[Wabamdo]

Hi guys,I am developing a school database that has courses table in it.The COURSES table contains the following fields.
ID,
CourseName,
Cost
StartDate
EndDate.

The courses cost changes frequently .so how can I make sure that the change does not affect the records where the students took the course before the change.

Thank you in advance for giving me your time

[JeroenMioch]

I would make a new table where the cost changes for each course are in.

tblCost

CourseName
Cost
CostBegin
CostEnd

This way you can allways look at the actual cost of a course and previous cost, using dates.
You can use Dlookup or SQL to pull the data you need.

Or you can put the cost in the data of the student. But that i think is not the way to go about it and would result in problems
if a student takes more then one course for example.

[NTC]

There are 2 approaches to this generic database design question:

1. linking: your post highlights the potential problem with linking in it changing the prices of past data - to manage this you must create new courses when there is new prices - and so the link of past courses doesn't change...and new students link to the new course record.

2. write in: your course list has the current price - when a student takes a course - you write in the data from the course table into the student's table - thereby preserving that data in their record and remain independent of any changes/updates to the course list table.

There is no right or wrong in terms of architecture. It is a matter of what is best for your requirement. Both have issues to manage. This question is also very common for any company managing a Part List.