Database design & copy data

[smally]

Hello, I'm a little bit confused as to whether my database is setup correctly or if I need to add something to it.

I have 3 tables.
tblEmployees - PK-EmployeeID, Firstname, Surname, etc...
tblTheoryBookings - FK-EmployeeID, FK-TheoryID, BookingDate, Result
tblTheories - PK-TheoryID, TheoryName, Cost

Employees have to do these various theory tests, and I log when they're doing it/have done it, and what the result was (pass, fail, no show).

In order to see how much money has been spent I use a query and use the cost field from tblTheories.
However the prices for the theory tests are changing and if I change this in my table then all my previous records will be wrong.

I don't know if I've set my database up incorrectly, or if I need to move the cost field into tblTheoryBookings. However I can see this being awkward for me as I'll have to type in the cost for every theory test

 

[Ranman256]

you need a Cost field on the tTheoryBookings to hold the cost at that price that day.
Then as tTheory cost changes, all new bookings will keep THAT new cost.

 

[plog]

You should set up a new table to handle costs. It would look like this:

tblTheoryCosts
TheoryID, number, foreign key to tblTheories
CostStartDate, date, first date that this cost is valid for
CostEndDate, date, last date that this cost is valid for
CostAmount, number, actual cost

Then you could set up a query using tblTheoryCosts and tblTheoryBookings. The JOIN clause would look like this:

INNER JOIN tblTheoryCosts ON tblTheoryCosts.TheoryID = tblTheoryBookings.TheoryID
 AND tblTheoryCosts.CostStartDate>=tblTheoryBookings.BookingDate AND tblTheoryCosts.CostEndDate<=tblTheoryBookings.BookingDate