Hourly Rate

[unclefink]

Im brainstorm some table information and wanted to be sure I understood this fully before I started drafting table settings and relationships.

I have an employee table:

FirstName
LastName
Company
Position

The position option will be based on a separate table which will contain two options, Employee and Manager. In the Position Table, there will be an hourly rate associated, where the value will depending on whether someone is a manager or employee.

My question is this.

Once I start adding data into these tables and at some point in the future one or both of the two positions has a hourly rate change I only want the new hourly rate change to affect records from that point on and not in the future

Hopefully i'm explaining this right and if not it at least makes some sort of sense.

 

[recyan]

till someone comes along, perhaps,
tblEmployee
EmpID_PK
EmpName
Position - FK
....

tblPositions
Position - PK (Values - Manager, Employee)

tblPositionRates
PositionRateID - PK
Position - FK
Rate
DateOfModification

tblProjects
ProjectID - PK
.....
PositionRateID_FK / Rate


Thanks

 

[unclefink]

Thank you very much for the reccomendations. I've followed your layout with a couple minor changes. IE: I changed "position" to company as the position only applies to one of the associated companies involved in the project i'm doing. Hopefully it makes sence once you see the layout.

After putting everything together, I created a form to add a project. So far everything works out until I get to the point on the form for rate. How do i force the form to accept the rate provided in the Rate table.

See the attachment.

You will see for sample purposes, i've used generic information so that will change as soon as I understand this concept.

Thanks in advance for your help and any future reccomendations.

 

[boblarson]

The control bound to PositionRate on your form should be a combo box which selects the values from the tblCompanyRates table and stores the CompanyRateID in it.

 

[KenHigg]

This seems a mess. The persons current wage should be an attribute of the employee, so it should go in the employee table.

 

[boblarson]

This seems a mess. The persons current wage should be an attribute of the employee, so it should go in the employee table.

Yes, I agree, it is a fair mess and I wasn't sure if I wanted to step into that one. So better still - an EmployeesRate table sort of like this (just a quick example):

EmployeeID
RateID
EffectiveDate


so then history can be maintained.

 

[KenHigg]

Looks better. That way they can grab the lastest date for the current rate and connect a project date to a wage date to see what the rate was based on the project date. :banghead:

i need a goodies

 

[boblarson]

i need a goodies

 

[KenHigg]

Speaking of sugar, time to hit the trick or treat left overs

 

[unclefink]

Yes, I agree, it is a fair mess and I wasn't sure if I wanted to step into that one. So better still - an EmployeesRate table sort of like this (just a quick example):

EmployeeID
RateID
EffectiveDate


so then history can be maintained.

While brainstorming this I was thinking it would be more customary to put a rate to a person; thus the post. In this situation, the rate is really going to be based on the company the person works for as they are "contracted" to work on the property. The rates are paid to the contract company on a set contractual rate/hour not directly to the specific employee working on site who are then paid by the contract company.

If I placed the rate to each individual employee, wouldnt I then have to change the rate for each individual person if the rate changes in the future?

Sorry this is so confusing. Also the "table setup" sample is just that, nothing set so anything can be changed. I just want to understand this to make it work.

Thank you for your patience, this is a learning experience in progress and this site has definitely contributed a lot to that.

Respectfully,