Hi,
I was wondering if someone could help me with the following senario, I have a database with these tables:
TaskTBL (TaskID(Primary Key), Task, PayID) where PayID is a code used to determine the rate for each Task
PayTBL (PayID (Primary Key), PayNo, Rate)
ContractTBL (ContractID (Primary Key), ContractStartDate, ContractDuration)
The TaskTBL is related to the PayTBL (PayID) - There are about 10 different Tasks and PayID's
What I am trying to do is, when the new contract starts, what is the best way to add the new Rates?
I can currently add the same Tasks to the TaskTBL under new ID's and add the New PayID to these and link these by creating a new table (ContractPayTBL) where each PayID is given a new ContractID, is this the correct way to achieve this problem or is there a better way?
Example:
TaskTBL
TaskID Task PayID
Task1 Work1 1
Task2 Work2 2
PayTBL
PayID PayNo Rate
1 1 $20
2 2 $30
ContractPayTBL
ContractPayID ContractID PayID
1 1 1 (this would go to 10)
11 2 11 (this would go to 20)
Thanks