Normalizing a table

[aziz rasul]

I have the following table which I want to normalise: -

Table - “tblConsultants”
Consultant ID Department ID Forename Surname Tel Number Hours Rate Salary
1 1 Naomi Smith 01736 762831 50.00 40.00 2000.00
2 2 Beverly Jones 01841 215874 49.25 38.75 1908.44
3 3 Muhammad Umar 01736 742326 45.75 50.00 2287.50
4 4 James Hodges 01637 365874 41.50 46.50 1929.75
5 5 Ann Hoe 01736 763298 51.25 42.00 2152.50
6 6 Mary Wheeler 01872 632587 53.50 37.50 2006.25
…

Here's how I have normalised it. Can anyone confirm that I taken the right approach? Is this 3nf?

Table - “tblConsultants”
Consultant ID Department ID Rate ID Week Number Hours Worked
1 1 1 1 50.00
2 2 2 1 49.25
3 3 3 1 45.75
4 4 4 1 41.50
5 5 5 1 51.25
6 6 6 1 53.50
1 1 1 2 45.00
2 2 2 2 49.00
…

Table - “tblRates”
Rate ID Rate
1 40.00
2 38.75
3 50.00
4 46.50
5 42.00
6 37.50
…

 

[jeremie_ingram]

I would have normalized it a few steps further. Any field that you consistently see data repeated could easily be stored within another table.

tblConsultant
-------------
Consultant ID
Fname
Lname

tblDepartment
-------------
DeptName
DeptID

tblRate
-------
RateID
RateAmt

If the hrs for the week are entered all at once (week one 40hrs) then you can keep it as one table, but if you want to break that down to a daily entry you may want to further normalize to accommodate that. It would be useful for data tracking, if you want that.

 

[aziz rasul]

Would it not be useful to have DeptID in tblConsultants so that we could access the department in which each consultant works in?

 

[Pat Hartman]

Unless the department may change with each set of hours entered, it belongs in the table that contains the consultant's name and other personal data.

I would put department into the same category as pay rate. If you need to keep historical records (even for just last week), you need to "duplicate" the pay rate and the department in the transaction table so that your reporting will be accurated. The alternative method is to keep these pieces of information in a separate table with effective time frames. Then all your queries need to get the data from these tables based on transaction date.