ERDs and Modeling Ownership of Roles Within Job Table

erd

I'm currently in the process of modelling what I see as a relatively
simple relationship through my ERD. I'm not even certain that strictly
speaking this is Many-to-Many but nevertheless there are Many
Employees who are associated with Job and many Jobs with which an
Employee is associated.

I think that this differs from what I'd assume is a "real"
Many-to-Many relationship (and by that I mean one which requires a
link table in the database for example: Students to Courses), as I'm
really talking about two different roles. One employee being
responsible for the Authorisation of Many jobs and One employee being
responsible for the Administration of Many jobs (although, that could
potentially be the same Employee)

EDIT: Trying to clear up any ambiguity, @Avarkx got me thinking and what I really need to be able to do is to control the overall permissions via some sort of "CanManage" table for allowing functionality to Invoices, Quotes, Drawings & Jobs; rather than allowing an Employee certain functionality on a Job by Job basis (which would be the case in the diagram below)

But, I also need to be able to record which Employee actually carried out the action within Job, which is what I was trying to get across in the paragraphs above (albeit in a roundabout way… Sorry!)

Here's my attempt at modelling the previous suggestion:
My attempt at modelling this on the ERD

Best Answer

I think you'll want an EmployeeCanAuthorizeJob table and an EmployeeCanAdministrateJob table for a straight many-to-many representation ( right now, I think you'll find you'd be duplicating your job records with the current structure ). This way, you could tie many EmployeeIDs to JobIDs and vice versa for either of the management tasks.

That said, I would likely only end up creating an EmployeeCanManageJob table, along with possibly a secondary look-up table ManageJobType. The purpose of the second table would be to include the functions an employee can perform on a particular job. Today you've got two, but who knows if CanDelete of whatever needs to be broken out later down the line?

EmployeeCanManageJob
------------------------------------------------
 PK | Employee_FK        FOREIGN KEY Employee.EmployeeID
 PK | Job_FK             FOREIGN KEY Job.JobID
(PK | ManageJobType_FK   FOREIGN KEY ManageJobType.ManageJobType_PK)

This way, if ManageJobType_PK 1 was "Administrate" and 2 was "Authorize", employee 1 could administrate job 1 with a ( 1, 1, 1 ) record and authorize the same job with a ( 1, 1, 2 ) record.

As per the edit:

There at least two popular methods of change tracking: the first being an audit table, the second being some form of slowly changing dimension. To save you a little grief down the line, you are not dealing so much with a "slowly changing dimension" here as you are a "rapidly changing fact," if you will, so I expect you will find yourself looking at one of the numerous ways to implement the audit table scenario. Which method you choose will largely be subject to you and your team's comfort level with the technologies involved ( or more specifically, each options advantages and disadvantages ). I've personally always been a fan of the log trigger approach and think it would be more than suitable for your needs here, but if you have out-of-the-box capabilities available to you, like in Sql Server, perhaps you and your team would be more comfortable with that method.

Related Question