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


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?

 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.

  • I see what you're suggesting and have modeled this here link but I'm uncertain that this is really what I'm after. Please see my edited question. Sep 12 '14 at 9:58
  • Okay, with your edit, definitely not what you were looking for! You are probably going to end up implementing an audit table or a slowly changing dimension to provide that functionality.
    – Avarkx
    Sep 16 '14 at 1:01
  • Great, thanks for the heads up! Certainly something that I need to look into a bit more and those threads are a good starting point. Our client has had many problems stemming from a lack of security or user tracking in their current system and this is a fundamental requirement for them. Your initial answer did get me thinking though and I posted another question regarding the implementation of "permissions" to carry out actions in the system. Sep 16 '14 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.