Payroll database- advice needed on relationships/normalisation (1 Viewer)

Hi,
I would like to get your advice on my table setup and relationships for this payroll project. The company is an engineering company with Projects (or construction sites) around the world.

The 'Candidates' are current or potential employees and contractors. There are three main pay categories:

1.Shift-workers
All shift workers doing a particular job on a particular project are paid the same rates e.g. all welders on a particular project or site in England are paid the same as each other. For that reason I want to link the pay rates with the job description for these workers. This avoides creating 50 records for 50 welders on the site in England to say that they make £10 an hour normal time (or whatever it is) etc.

2. Contract
Contract workers usually get paid a flat rate per hour. As these are negociated on an individual basis I would need to have this information linked to each individuals job (M_CandidateJobDetails).

3. Salary
Again this information needs to be input for each individuals job.

For the contract and salary people the pay frequency can vary (weekly, bi-weekly or monthly). So can the currency they are paid in. I haven't got as far as the currency issue yet.

The reason for the one-to-many relationship between M_JobClassifaction and M_CandidateJobDetails is that many candidates can have the same type of job e.g. there can be many employees that in the job classifaction of 'Electrician'. For many of the jobs at managerial level e.g. 'site manager' there will only be one.

I will have a table with the hours worked by each person per week. I can use this for those on shift work or contract to calculate what they will be paid.

One of the main reasons for this database is so that the company can print reports to see what is paid out in payroll for each site and in total (in euros). These will be gross figures and I don't need to take expenses, vacations, bonuses or taxes into account. They other thing we will need to be able to do is assign candidates to vacant positions and change them from one position to another - possibly between different projects.

So basically does anyone have any comments on the relationships, normalisation or anything else. Is this the best way to do it?

I've attached a screenshot of the relationships.

1. Due to my site's security requirements, I am not allowed to download from a site that is overseas. To me, this site is overseas.

2. Having multiple sources of wage info is going to make your job harder. However, having said that, there is some hope for you.

Look at UNION queries as a way to bring together things that are similar conceptually but different structurally.

Each leg of the query can bring in different rates based on different sources but put all the rates in the same overall place so you only look up one thing.

For the shift-workers, define a field that is their pay code in the personnel table. Make that code correspond to something from a table of shift-work job descriptions. Put that as one leg of the UNION query.

For the contract-workers, define a field that is their pay code in the personnel table. Make that code correspond to an entry in the contract table that selects a particular contract rate. Put that as one leg of the UNION query.

For the regular salaried employes, define a field that is their pay code in the personnel table. Make that code correspond to something from your salary table. Put that as one leg of the UNION query.

Select the pay codes so that the three sources DON'T overlap. Perhaps you can assign arbitrary ranges to them, or make the key a compound key with a flag for which source to use.

The union query might resemble

SELECT shiftrate AS payrate FROM tShiftrates WHERE payclass = 1 AND tShiftrates.selector = payselector UNION
SELECT contractrate AS payrate FROM tContractrates where payclass = 2 AND tContractrates.selector = payselector UNION
SELECT salary AS payrate from tSalaries WHERE payclass = 3 AND tSalaries.selector = payselector ;

You can do a lot more than this, but the idea is that with a pay class code and a pay selector code, this union query would return a rate from one of three places.

There are a bazillion other ways to do this, but if you do it in a union query like this, then refer to the UNION query rather than the individual tables, you have a LOT less work ahead of you.

Thank you so much fo that DocMan. I'm just going to spend some time seeing how I can apply what you've said to my database. I the meantime here are the relationships as they are at the moment:

I suggest a rethink. Consider the difference between a post and an employee. Your electrician post will have many employees, your manager post may only have one, but the pay rate attaches to the post, not the employee. So you should be holding the post ID in the employee table, not the employee ID in the post table.

If you want to handle different pay rates, you need one table to hold them and a field to indicate what type they are, not multiple tables.

On any relationship diagram, if there is more than one link between any two tables, this suggests a structural problem.

Thanks for that Neil,
Maybe my table naming is not the best - the M_JobClassifaction table is the table that holds post information. There is a one-to-many relationship between the post and the candidate.

Your suggestion about linking the pay info to the post rather than the individual sounds like it would simplify it. However 2 individuals doing the same job can be paid different amounts.
-For all shift workers or tradesmen like electricians - they will be paid the same.
-However 2 foremen doing the same job can be paid different amounts. From a supervisor level up the rate can vary between people doing the same job.

How would I get around this?

The reason I have separated salary, contract and shift work into different tables is because they will have completely fields. For instance the shiftwork will have NormalRate, Overtime and DoubleTime rates as well as a bonus called Second Tier Payments in some cases. The contract rates just has a rate per hour and the salary has a yearly salary. Also in the future if I do take taxes into account this will affect salaried workers and shiftWorkers but not contractors. I don't have all the details for them all but they seem to be so different that they should be in different tables? Then I can join the info I need from each one using union queries as DocMan suggests.

Then if I link pay information to the post..One employee might be taken on to do that job as a contractor but if he leaves and someone else does it they could be on a salary instead..

Any ideas or am I making this more complicated than necessary?!

RCurtin said:

Thanks for that Neil,
Maybe my table naming is not the best - the M_JobClassifaction table is the table that holds post information. There is a one-to-many relationship between the post and the candidate.

Click to expand...

Yes but you are holding the candidate ID in the Job table. That's the wrong way round.

Your suggestion about linking the pay info to the post rather than the individual sounds like it would simplify it. However 2 individuals doing the same job can be paid different amounts.
-For all shift workers or tradesmen like electricians - they will be paid the same.
-However 2 foremen doing the same job can be paid different amounts. From a supervisor level up the rate can vary between people doing the same job.

How would I get around this?

Click to expand...

Probably by having pay details in the candidate table, too. If they exist in the candidate table, they would overide the details in the job table

The reason I have separated salary, contract and shift work into different tables is because they will have completely fields. For instance the shiftwork will have NormalRate, Overtime and DoubleTime rates as well as a bonus called Second Tier Payments in some cases. The contract rates just has a rate per hour and the salary has a yearly salary. Also in the future if I do take taxes into account this will affect salaried workers and shiftWorkers but not contractors. I don't have all the details for them all but they seem to be so different that they should be in different tables? Then I can join the info I need from each one using union queries as DocMan suggests.

Click to expand...

Ok, so long as you have thought it through.

Then if I link pay information to the post..One employee might be taken on to do that job as a contractor but if he leaves and someone else does it they could be on a salary instead..

Click to expand...

Mmm... If you are based in the UK, you are going to have real problems with the Revenue if you try and argue that a contractor is covering a salaried post.

Any ideas or am I making this more complicated than necessary?!

Click to expand...

No, it is complicated! The business model is complex before you start building the database.

Ok I've changed it a bit to represent the ideas of having candidates and posts a bit better.

I think that the data I had in M_CandidateJobDetails belongs in the M_Candidates table It was supposed to be a sort of junction table between candidate and post but I think that that is unnecessary as all the data in it has to do with candidates. However, now the M_CandidateDetails will have fields that do not relate to many of the canidates in it i.e. any unemployed candidates. Is that wrong?

I'm still not mad on the idea of having to have the pay details in two separate tables. For all the shift workers and tradesmen the pay details could relate to the job as it will be the same for all of them but for everyone else it is a very much related to the person I think. Could the setup I have now work and is it the right way to do it?

As for having a certain job done by a salaried person or a contractor - I'm based in Ireland. I know that when I started working for the company I had the choice of going on their books as an employee or being a contractor. Thats why I'm reluctant to say that certain positions will always be salaried or that others will always be contract. I know that a foreman could be a shift worker or a contract worker getting a flat rate per hour. However I do need to find out more about all this. For the moment I'm just trying to get the basic setup and relationships right.

My advice on where pay rates are held is based on my experience of SAP payroll which we use at work. This system supports a standard pay on the post, but allows this to be over ridden by the pay rate on the employee. It works well for us, but I accept that there may be a better way that suits your situation.

Thanks for posting back Neileg. Its great being able to get other peoples view on it. I have just checked and there doesn't really seem to be any such thing as a standard pay for a post. It varies so much from site to site. Even within a site or project two people doing the same job can be paid different rates and that can change often too. Also because of the nature of the work (short and long term construction projects) a person might only be working in a job for a few months.

The idea of a standard rate does apply to shiftworkers and tradesmen though. So in their case I want to store the rates for a particular job on a particular project in one place. This is what I have now (trying to implement DocMans ideas for the payrates):

OK, heres whats good (I think?)
Many candidates can have the same job and job rate.
1.Shift Workers:
For instance you could have many day shift workers who are 'mechanical riggers'. All of them are paid the same rate on a particular project. So I only need to store this once for a particular job on a particular project.
2.Salary and Contract Workers
In most cases they will each be paid different rates so in practice it will be a one-to-one relationship between M_CandidateDetails and SalaryPayRates. In the case where 2 people have the same job with different pay conditions it will have to be entered into the Posts table as separate posts.

There are still some problems with it though. What happens when someone finishes a job? I would have to delete the PostID in the candidateID and then lose of the related pay information from one of the pay rates tables? So there would be no way to find out what a particular candidates rates were on a particular job.

Another main function of this DB is to be able to assign and unassign people from posts. Maybe I do need to have a junction table between candidates and posts so that when a candidate is assigned to a post a record is created in a table called CandidateJobDetails. This would hold info particular to that candidate and job and link to the correct pay rates in that instance (like I had at the start).

I feel like I'm going around in circles at this stage - I'd really appreciate any feedback. (Oh and my boss wants this done for monday! Not going to happen but I need to have made some progress)

Ok heres what I have now:


So now when a candidate is assigned to a job a record is created in M_CandidatePostDetails. It has info specific to that particular candidate and job. The currentlyAssigned field is a boolean field so we know if they are currently employed or not. That way I can show a work history for each person.

I also need to have a pay history so we can tell what a person was paid in a particular job. Thats why I have linked the candidatePostID to the PayRate tables.

The only thing is it will mean that I have to create and individual record for the pay rates of each individual shift worker. So I could have 50 electrician records where the job and all the pay details are indentical - just different people. I don't see a way around that now though?

Edit--
Just thinking - having individual rates even for the shift workers might be the way to go - that way you have a record of exactly what they were paid on a particular job - even if the rate for that job has increased since they worked there. And I could just have a table with standard rates for the shift workers to supply default data for this groups pay details..

So any comments on the design? Does anyone foresee any other problems with it? How about from a normalisation point of view?

Thank you, this is good news for other visitors


pret personnel

I would like to get your advice on my table setup and relationships for this payroll project.
sonnerie portable gratuite

do you want serious payroll advice

DONT DO IT

if you just want to analyse gross pay into departments etc, thats a different matter - but even then the cheapest payroll package you can find will have all this included as standard.

if you are trying to handle tax/social security stuff etc, DONT DO IT. Use a bureau or buy a ready made solution.

Get anything wrong (ie make deductions incorrectly) and you become liable for everything. There are so many special rules about tax, NI, permitted deductions. pre-tax and post-tax, which are changing all the while.

This is really a job for people who specially in this, with the staffing levels to make sure budget changes get implemented properly.

i've been playin the paper money on tos and have come to like the platform a lot and am thinking i'd like to transfer.
proprietaire simulation rachat credit immobilier consommation - demander un rachat de crédit, faites une simulation rachat de credit en ligne.proprietaire simulation rachat credit immobilier consommation