Need suggestions on database design
-
Hi everyone.
I've got a database design question for all you guru's out there. I've got a database currently in use on the production database which is storing salaries for employees and what job they do, all that to calculate the cost price of every job.
GLOSSARY :
my database is in french, so I tried to translate the term at the best of my knowledge!Speciality = the job the employee is doing in the factory (material handler,general helper, foreman, etc)
Class = the higher the class for each speciality, the higher the wages (A, B, C, N/A). Every speciality does not have all classes.
The design is currently like that :
-------------
TBL_EMPLOYEE
-------------
id_employee PK
name
...
id_speciality
---------------
TBL_SPECIALITY
---------------
id_speciality PK
Description
------------------------
TBL_EMPLOYEE_SPECIALITY
------------------------
id_employee PK
id_speciality PK
class PK
salary (every employee does not have the same salary for the same speciality/class)
order (if 1, it's the primary job of the employee. 2 3 and so on is the secondary, third job of the employee)
Now, my problem! The HR departement asked my to develop an history of salary module. This module will be keeping history year after year of all the salaries for the primary job of each employee. I figured a table like:
------------------
TBL_SALARY_HISTORY
------------------
id_employee
date
id_speciality
class
Annual salary --> for the office employees
Hourly salary --> for the factory employees
Raise %
...
1) If the employee work in the office, the field "Annual salary" will be used and "Hourly salary" will be "Annual salary"/Nb of working hours a year (I need that number of another module not linked for now). For the factory worker, it will be the opposite, the field "Hourly salary" will be use and the field "Annual salary will be empty.
2) The office employees don't have a class, only a speciality. That information need to be inserted in the database, because it is not right now.
3) The HR departement only wan't to keep history for the primary jobs of the factory working employee, the others don't need to be kept afterward because these salaries are based on the collective agreement. So I won't have to keep this info in the table TBL_SALARY_HISTORY, but I do need it in TBL_EMPLOYEE_SPECIALITY. So the primary job's salary of each employee will be keep in both tables (TBL_SALARY_HISTORY,TBL_EMPLOYEE_SPECIALITY), but the secondary and so on jobs will be kept only in TBL_EMPLOYEE_SPECIALITY.
4) When the salaries are changed in TBL_SALARY_HISTORY, I need to change the salaries in TBL_EMPLOYEE_SPECIALITY. I figured I could do that with a trigger, but it does not respect the rules of normalization.
I can change the database design, because there is only one application using these tables, but it would be a lot (LOT.....) of work and a lot of double-check since this info is vital to the company!!! If I could be able to use my tables like that (adding fields is of course not a problem) and somewhat respect the database design "best practices", I would be glad!
I had a couple of ideas :
1) Office employees does not have a class with their speciality :
1.1) use "n/a" as the class, and still use the table TBL_EMPLOYEE_SPECIALITY
or
1.2) create another table called TBL_EMPLOYEE_OFFICESPECIALITY that just does not have the class field and link it like TBL_EMPLOYEE_SPECIALITY
2) Info must be kept on only the main job for factory employees. (I'm not sure what to do here....)
That's all I thought of for now. Do you have any suggestion to have a good and effective design with these conditions? I hope I've been clear, if not, I could clarify some points if you need to.
Thanks in advance!
-
Actually, you do not need two tables to do this.
tbl_Employee_Salary_History
EmpSalaryHistoryID (Identity) PK
EmployeeID
EffectiveDate
SpecialityID
ClassID
RateType
RateAmount
The EmployeeID and Effective Date are the business key. Rate type is annual or hourly, and rate amount is the annual or hourly amount.
The max of employeeid and effective date is the most recent or current salary and you can create a view that will show this.
-
Or you can just keep the salaries in TBL_SALARY_HISTORY. Make id_employee, id_speciality, date the primary key and maintain integrity by using a foreign key that references TBL_EMPLOYEE and another that references TBL_SPECIALITY. Now you can maintain a history of all the salaries of all the jobs (specialties) of the employees over time regardless of where the work.
-
Both above look good But It is my experience that at some point you may want to get rid of an employee and guess what will happen?
You CAN'T because it is required for the HISTORY which in my experience even if you leave a company they DON'T discard your history so my advice is DENORMALIZE the History so that in contains EmpLname, EmpFName, etc and NO Foreign Keys. That is one of the problems that usually happen with invoice history tables. If you have a vendor Address you probably Save the Address that the Vendor had at the time because It could Change. Again it is just another thing to keep in mind. If you find that it doesn't matter that the employee record stays even after the employee is gone (use a flag in the record) then just follow the above suggestions
HTH
* Noel -
True, I hadn't thought of that. My initial thought was that a lot of companies will save info for everyone that has worked there. Thus the flag would have been my choice. Also if an employee's info is not wanted after removing said employee a simple delete cascade on the foreign keys would take care of that.
-
The design I gave was the correct one. For the employees that are gone, you will have to keep them for a minimum of 7 to 12 years any way because of government regs. The best way of handling history for them after that time period is to write them to an archive file or writing it at termination. But, the only reason to denormalize is for reporting, never for storing data. And, the design I gave will give you all the jobs, specialities and the complete salary history, look at the structure again.
-
Thanks everyone for your reply on a sooo long message. I'll look at your ideas and take the best one that fits my current database design.
Thanks again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply