Employee Compensation

[biggcc]

Trying to setup a database to track employee compensation and need some advise on the setup. I was told in a previous post to normalize my tables which I think I've done. I would just like some other input on this to know if I've done it right.

I created a table called Employees with the following fields: autonumber id field, firstname, lastname, department, position, hiredate.

Next I am creating a new table for each year which will contain the following tables: autonumber id, base, bonus, addbonus, overtimecomm, car, 401kcon.

The autonumber ID fields in each table have been set as the primary keys and I've setup a relationship between Employees and each of the compensation tables.

Does this sound like it will work? Or do I need to go back and rethink some of the process?

 

[KenHigg]

Hum...

Sounds like a little more info is needed...

What are the deal-breaker questions you will be asking you database?

 

[biggcc]

Basically what this database will be used for is year end reviews. What they want is the ability to print a form that give them the employee's compensation information for the last 3 years. Previously it was done with an Excel spreadsheet and an Access report but each year they would have to go in and change all the information so that it would reflect the correct years. I'm trying to automate that process so I don't have to go back each year and make any changes.

Does this help answer your question? Please let me know if you need additional information.

Thanks,

 

[KenHigg]

I would just do one compensation table and have a year field in it...

 

[biggcc]

I thought that would work but I couldn't figure it out. There would be about 8 compensation fields for each of our 120 or so employees. Using your suggestion wouldn't I keep repeating information in a single table? I'm assuming your talking about a structure like the one below.

Employee Dept. HireDate Year Base Bonus Car Overtime Commission Other

If I did that I'd keep repeating the employee information over each year - I was told in another post that you didn't want to do that.

What are your thoughts on this?

 

[KenHigg]

This database would be very one dimensional, but should do what I think you're talking about...

Table 1 would be the employee info table. It would have a autonumber fld for a primary key. Then whatever other flds you needed. Something like this:

tblEmployees
recordNumber (Primary Key)
firstName
lastName
etc...

Then you would have one compensation table, something like:

tblCompensation
recordNumber (Primary Key)
employeeID (Foriegn Key)
year
basePay
etc

Then, you set up a one to many relation ship between the recordNumber fld in the employee table and the employeeID fld in the compensation table.

Does this make sense?

 

[biggcc]

That sounds like it would work. Let me give it a try and I'll let you know.

Thanks,