0

I am developing a database where we manage jobs and freelancers having skills we need for individual jobs, and then assigning them to those jobs. For example the jobs database with examples of one-day jobs and multi-day jobs (which may overlap):

JobID JobName JobStart JobEnd
56 Some Job in London 2023-04-15 2023-04-17
57 Some Job in Birmingham 2023-04-23 2023-04-23
58 Some Job in Manchester 2023-04-23 2023-04-25

In a separate table we then have roles:

RoleID RoleName
13 Driver
32 Engineer
34 Mechanic

And in a third table we have freelancers:

FreelancerID FreelancerName
22 Joe Bloggs
23 Mary Smith
24 Mo Mohammad
25 Casey Rogerson
26 Rebecca Grant

Then, we can assign how many of each skill we need for each job:

JobRoleID JobRoleJob JobRoleRole JobRoleQuantity
4 56 34 2
5 56 32 1
6 57 32 1
7 57 13 1
8 58 32 1
8 58 13 1

So, we need:

  • 2 Mechanics and 1 Engineer in London
  • 1 Engineer and 1 Driver in Birmingham
  • 1 Engineer and 1 Driver in Manchester

Finally, we assign freelancers to jobs. Crucially, we may not need all roles on all dates:

JobFreelancerID JobFreelancerJob JobFreelancerDate JobFreelancerRole JobFreelancerFreelancer
101 56 2023-04-15 32 22
102 56 2023-04-16 34 22
103 56 2023-04-17 34 23
104 57 2023-04-23 13 24
105 57 2023-04-23 32 26
106 58 2023-04-23 32 25
107 58 2023-04-24 32 25
108 58 2023-04-25 32 22

So we have:

  • In London, Joe Bloggs as an Engineer on the 15th, then Joe Bloggs as a Mechanic on the 16th, then Mary Smith as a Mechanic on the 17th
  • In Birmingham, Mo Mohammad as a Driver and Rebecca Grant as an Engineer, both on the 23rd
  • In Manchester, Casey Rogerson as an Engineer on the 23rd and 24th, then Joe Bloggs as Engineer on the 25th

As you can see, this system can be flexible as sometimes we need all the roles for all the days, sometimes only one role on some days and not others, and sometimes a combination of roles on a combination of days.

The problem

Now I need some logic so that the number of roles cannot be reduced below the number actually assigned.

In the examples above, the only one that should be able to be "removed" (set to zero) is Driver in Manchester as that hasn't been assigned.

As another example if I had requested 2 Engineers in Birmingham, assigned Rebecca and left the other slot temporarily blank, then decided I don't need a second Engineer, I should only be able to reduce it down to 1 and no less.

What database logic can I use to get the number of people assigned, whether different people or the same people, and whether overlapping dates or not, for each role so that I can set a min value on the <input type="number"> field when editing the job roles required?

Stuart Pinfold
  • 318
  • 1
  • 5
  • 19
  • This seems like a fairly easy thing to solve, however I'm just not following your current logic. This is why we usually request code examples of what you've tried and what you expected to happen vs what happened. A lot easier to repurpose your code logic than piece this together. Such as when you say "the number of roles cannot be reduced", no idea what you mean. Number of roles needed for the job? At least use the column names in the table that you're referencing. It's causing us to have to scroll up and down to see what "Driver in manchester" means – Bellator Mar 23 '23 at 16:51
  • https://stackoverflow.com/questions/13390490/the-values-of-one-column-cannot-be-greater-than-another This seems like it could help, though. In your case, just change the math from greater to less than... – Bellator Mar 23 '23 at 16:57

0 Answers0