Should I have an "Or" in fieldname? (shudder)

[trb5016]

For part of my database we need to track how people are paid. (i.e. keep a history of what funds paid for what person's "salary"



Design Stipulations:

A person is either paid bi-weekly (wage) or monthly.

A person can only be wage OR monthly at a time, but a wage person could become monthly at some point and vice versa.

Each Month/Pay Period their could be 1 or more funds that go towards a persons "salary"

My first idea is to create separate tables, one for wage and one for monthly and each of those tables has an additional table to keep track of the funds.



This seems potentially wasteful because the "Monthly Funds" and "Wage Funds" Tables are really identical. Also, the "Monthly" and "Wage" tables are basically storing almost the same information.

To combat this I came up with this idea.


This has 2 fewer tables but would require storing the 2 week pay code OR a month in the same field. While this will be a finite dropdown (26 codes + 12 months) Having an OR in a field name is a big No-No.

Anyone have any suggestions? Something I haven't tried?

[TheShabz]

Try using your OR field in the second design and adding an additional field telling you if it is a wage or salary. you can either do "PayInd" and have the options be salary, wage, comission, stipend, etc. that way when you report, you can query off of PayInd and you'll know that your OR field will display the correct data.

Will that work for you?

[jzwp11]

First, why not store the wage history of the employee. Anytime there is a change in rate or term (biweekly or monthly) then you would add a new record with a new effective date. The record with the most recent date is what you would use to calculate their current pay.

tblEmployeeWageHistory
-pkEmpWageHistoryID primary key, autonumber
-fkEmpID foreign key to your employee table
-payRate
-fkPayTermID foreign key to tblPayTerm
-dteEffective

tblPayTerm (2 records: monthly and biweekly)
-pkPayTermID primary key, autonumber
-txtPayTerm

I would have a table to hold the pay period info and specify which pay term each applies to

tblPayPeriod
-pkPayPeriodID primary key, autonumber
-dtePayDate
-fkPayTermID foreign key to tblPayTerm

Then you would junction the payperiod and employee's most recent pay history in your wages table

tblWages
-pkWagesID primary key, autonumber
-fkPayPeriodID foreign key to tblPayPeriod
-fkEmpWageHistoryID foreign key to tblEmployeeWageHistory (you would actually use an aggregate query (Max(effectivedate) to pull the correct record)

Of course you would then have a one to many relationship with the funds from which the wage is funded.

[trb5016]

Shabz, Your idea would definitely work, my only thought is that it would be storing semi-redundant info in the new field because if its a Month it has to be non-wage. But it would definitely make writing queries easier.

jzwp11, your solution is great for most payroll things, but this is a bit different. I wasn't clear enough in my OP

Each month/pay period the funds the wage is being paid from changes (not 100% but most of the time)

The end goal is each fund has a given amount and we'll eventually want to calculate the remaining balance.

Your idea is so close to working, but: There isn't really a need for an effective date that a rate goes live because it is always changing for the pay period/month (due to the finances coming from a different fund)

gah, this is so confusing, I might just go with with Shabz's idea and say to heck with whatever proper normalization might be.

[TheShabz]

i dont see how it is redundant. if wage and monthly is all you will ever offer just make that field a yes/no called "IsMonthly" or "IsWage". That still looks normalized to me.

[trb5016]

i dont see how it is redundant. if wage and monthly is all you will ever offer just make that field a yes/no called "IsMonthly" or "IsWage". That still looks normalized to me.

Yea, you're correct, I'm not sure what I was thinking. I've just been looking through my past designs and being super critical of everything I thought was right.

Marking as solved...

[jzwp11]

I failed to show how the funds worked into the structure I proposed previously. I've attached an example database that illustrates the complete design.