Using Effective Dates 1

[Vyurr]

Hello all,

I am designing a DB that will track employee salary costs on an ongoing basis. The biggest snag I have run into is how to handle when employees receive a pay raise (which happens yearly). My thoughts are to use a join table that uses a dual primary key. Something like this:

EmployeeID(PK) SalaryID(PK) Effective Date

1 1 2008/10/01
1 2 2009/10/01

etc...

This table, would of course contain the foreign keys from the Employee Table and the Salary Table. My resulting queries would select using a WHERE Effective Date <= type of statement.

As I am just in the planning phase, I just wanted to check and see if my logic is clear or if there is a better way to handle these changes in salary.

Thanks in advance,

D

 

[fneily]

Actually very good. This is called a junction table. But to get the latest salary, it would seem you would just use the Max function on effective date grouping by employeeid.

The only question I have is this: using the multi-field primary key, what happens if someone is demoted? If that's a possibility, then you'd have duplicate primary keys, eg.
1 2
1 3
1 2 demoted, duplicate primary key

But if this doesn't happen, then no problem.

"see if my logic". Logic must be based on protocols. In this case you're checking normalization. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[Vyurr]

Thanks much for the quick response. It could very well be possible that someone's salary could decrease...so to prepare for this possibility, would I make the EmployeeID, SalaryID and the Effective Date all the Primary Key, or is that bad form? It almost seems to me that the more normalized a DB becomes, the more likely you'll end up with multiple fields in a table as the primary key? Or perhaps I don't understand normalization...that's always a possibility as well

D

 

[randy700]

You can add an additional field (autonumber) that serves ONLY as the primary key.

ReocrdID EmployeeID SalaryID EffectiveDate
1 1 1 2008/10/01
2 1 2 2009/10/01
3 1 1 2009/11/01 (demoted)



Randy

 

[Vyurr]

Well, that just makes too much sense

Thanks,

D