Small Sql quandry pls help

[Solarion]

My brain is not working today. I have employees and jobs. 1 employee can do many different jobs at once like 1 hour of data capture, another hour of cleaning printers. Each item is marked as a new job.

1 job can be done simultaneously by many different employees such as 6 people doing the same data capture job or the same job cleaning printers.

Please tell me how to represent that as a table structure? I think it is a many to many relationship.

I blame the fact that I'm on strong pain killers and lack of sleep right now so don't laugh if it is something really simple

 

[Hamster]

Person 1-N PersonJob N-1 Job

That would be the most basic example. PersonJob would be the "time card"/log capture table.

 

[Solarion]

Person 1-N PersonJob N-1 Job

That would be the most basic example. PersonJob would be the "time card"/log capture table.

Thanks for helping me with one of my moments Hamster. I had put all the time card stuff; hours, rate and total amount in the Job table.

 

[Hamster]

Thanks for helping me with one of my moments Hamster. I had put all the time card stuff; hours, rate and total amount in the Job table.

To make more sense of it:

You have Jobs and each job can have many TimeEntries. A time entry is captured by a Person. So:

1 Person captures many TimeEntries
1 Job consists of many TimeEntries

So

Person 1----N TimeEntry N----1 Job

 

[rustypup]

To make more sense of it:

You have Jobs and each job can have many TimeEntries. A time entry is captured by a Person. So:

1 Person captures many TimeEntries
1 Job consists of many TimeEntries

So

Person 1----N TimeEntry N----1 Job

Correct

Reverse the logic.

Job is the primary entity

It has a Time property from which 1-N technicians can consume. This assumes the job's time property is non-variable. If it is, that, too, becomes an entity

 

[Solarion]

If I understand correctly, a Job will have it's own date such as start and end, and a TimeCard will also have it's own start and end dates?

Maybe some jobs don't even need a start or an end date. You could just keep assigning people to this particular job with their own timecards for that job. I think. I've heard of job numbers being recycled for years but don't think SARS like this or at least it's something that sort of flies under the radar

 

[B-1]

You can do many to many or have a mapping table in the middle will depend on your normalising.

So:
Job*---*Employee
Or
Job---JobEmp---Employee

 

[Solarion]

You can do many to many or have a mapping table in the middle will depend on your normalising.

So:
Job*---*Employee
Or
Job---JobEmp---Employee

This is the setup I have at the moment.

Jobs table

Id
JobTypeId
JobDate

JobTypes

Id
Description
Duration

EmployeeJobs table below. The remuneration rate is actually stored in an EmployeeType table and I populated it in EmployeeJobs table just in case the rates change in future and so it won't affect what it was when the EmployeeJobs record was created. I thnk that makes sense?

Id
JobId
EmployeeId
RemunerationRate
TotalHours
TotalEarnings

And then of course the Employee table

Id
FirstName
LastName

Note: The Employees and Jobs table contains quite a lot more fields but I've just tried to keep it simple for this topic.

 

[B-1]

Looks good from a quick glance.

 

[_kabal_]

why store TotalEarnings? shouldn't that just be a computed field?
TotalEarnings = RemunerationRate * TotalHours

 

[_kabal_]

I think you do definitely make some good points there.

I guess it does really come down to the finance model, but at the same time it doesn't make sense for eg
RenumerationRate to be ZAR without ZA VAT, while TotalEarnings is GBP with UK VAT, without a bunch of additional metadata