Storing Data In A Side Table That May Change In Its Main Table

This is an overall question of best practice when designing a database and I have been unable to correctly word my question to find an answer on the net. I have a database that is

Solution 1:

As far as I understand you have an employee and associated tasks. I would create one table for employees and one for the tasks. Another many to many table will link these to. In the task table you will have a status and date effective from/to columns. if the task is currently effective you can leave the effective to date null or set the value to 1/1/2999. When querying the tasks you will need to provide a date so you will be able to get the task effective as of a specific date. If you decide to use null value the where clause will be like

where t.DateEffectiveFrm<=@AsOfDate and (@AsOfDate < t.DateEffetiveTo or t.DateEffetiveTo is null)

if for currently effective tasks you use a date value in the far future ( 1/1/2999) the where clause will be even simple where t.DateEffectiveFrm<=@AsOfDate and @AsOfDate < t.DateEffetiveTo

You might consider also to add a status table , which will keep a history of the status changes