ER Diagram and Relationships

[DBApprentice]

Hi guys!

I am working a pet project from my job, this DB is intended to manage employees, competence and trainings certificates, installations and the dynamics of crew changes and compliance of paperwork. I have posted this question in Stack Overflow 5 days ago but I had few inputs there, so I will take my chances wih you guys.

During the desiging of the ER Diagram I cam e to a problem: each crew member can act in roles (Maintenance Supervisor, Mechanics, Lead Mechanics, Electricians, Automation Leader, etc.).

Because crew follows a hierarchy, I am interested in build a model where I can save which roles a person can act, for instance: Lead Mechanic can cover absence of a Mechanic, Automation Leader can cover Automation Techs and so on. Each installation has its own training matrix, so it also impact the roles and employees. One cannot assume Maintenance Supervisor can cover Mechanic position because this person acting as maintenance supervisor came from Automation area and may not act with other disciplines (Electrical, Mechanical, Electronics).

But my business rule is basically this: 1. An Employee needs a Diploma to be hired and act in a Role 2. Every Installation has its own Training Matrix 3. A Role needs to attend to Training Matrix of the assigned Installation. 4. An employee can act on different Roles only if he has Diploma and attended the trainings necessary based on the installation´s matrix.

Practical example: John Rambo has been hired as Maintenance Supervisor, he has a Mechanical diploma. this means he can act as Lead Mechanic and Mechanic if necessary. John is assigned to work in Installation B and take all trainings necessary to be compliant. One day, Installation C Mechanic, Sheldon Cooper, calls in sick and John Rambo is the only option we have, but we need to run a gap analysis on his trainings matrix to make sure he has all trainings mandatory and see which ones he will have to take to be sent there. The gap analysis detects a 1h course on stainless steel bolts is missing but he can take it online to close it. Then we can enroll him on this training before sending Maintenance Supervisor John Rambo to installation C to work as a Mechanic and cover Mechanic Sheldon Cooper in Installation C. Does that make sense to the ER Diagram I came up with? Thanks for your time!

As a complement, the certificate issued after a course is finished belong to the Employee, so he can fulfill requirements to act in a Role. It means that the relationship between Employee/Certificate and there is another relationship between Role/Employee and another one Installation/Role.

This is a preview of the ER Diagram, but something that I might have done wrong is about the entities Role and Trainings. Sadly I cannot explain how and why.

Appreciate any help!

 

[Ranman256]

I would have all 3: diploma, role, train as children of EMPLOYEE.

 

[jdraw]

Here is a link to a "somewhat similar" request from a few years back. It may help.

And another oldie with some related employee info.

You may find this helpful.

 

[DBApprentice]

Here is a link to a "somewhat similar" request from a few years back. It may help.

You may find this helpful.

Hi jdraw!

I have revised the model and it indeed helped me. The only part that keeps being confusing for me is the relationship between Installation and Role.

Each Installation has a number os roles to be perfomed by employees, and also a training matrix to be complied depending on the role. So when John Rambo is promoted or transferred to another installation we need to check if he complies with the matrix.

I am making another pratical example: Installation A has a role Production Leader, that requires Certificate 2, 3 and 4. Installation B requires Certificates 2, 3 and 5 for this very same role.Buster Keaton is a Production Leader from Installation A and request a transfer to work on Installation B, we can notice he would have to undergo training to acquire certificate 5 to close the gap on his matrix.

It seems to me that Role and Installation has a relationship, but when I add Employee I am not sure how that relationship works.

 

[DBApprentice]

I would have all 3: diploma, role, train as children of EMPLOYEE.

Thanks! Why Role is not and entity? Or a child from Installation?

 

[jdraw]

Not knowing your details, and commenting as I type, you might consider a Role and Prerequisite/Alternative. The thinking is you want someone with Certification X, but no one is available, so you're looking for Certification X or an Alternative for Certification X. So some sort of "this is equivalent to or could be substituted for" concept.

In the old days, people had to have college or high school or equivalent. It's the or equivalent I'm thinking about here.
It could even be a separate table that you would use as a lookup when attempting to find someone with an acceptable "equivalent".

Are you using some tool for your ERD?

 

[DBApprentice]

Not knowing your details, and commenting as I type, you might consider a Role and Prerequisite/Alternative. The thinking is you want someone with Certification X, but no one is available, so you're looking for Certification X or an Alternative for Certification X. So some sort of "this is equivalent to or could be substituted for" concept.

In the old days, people had to have college or high school or equivalent. It's the or equivalent I'm thinking about here.
It could even be a separate table that you would use as a lookup when attempting to find someone with an acceptable "equivalent".

Are you using some tool for your ERD?

I see what you mean with the alternatives, but in this business rule we can´t have that. If we don´t find a person that has all certificates necessary then we can identify whos has the lowest number of gaps and apply a Dispensation for the person to act in that role temporary. A dispensation is a business process that leaves many loopholes and problems for future audits so we use it as a last resource (A.K.A. Desperation) and it requires our VP of Operations to approve and follow-up.

As for the ER Diagram, I am nopt using one, I made that picture using PPT. Do you recommend a free tool to use?

Thanks for your usual help!

 

[jdraw]

Here's a rough draft, but it may give you some insight. Ignore the Integer 10 stuff it's part of a default in the package.
I used Visual Paradigm community/free for this. I used to use community ERWin, but it was dropped by supplier. Then went to Toad Data Modeler Community and they dropped that. VP is most comprehensive but doesn't work directly with Access.
This is a draft. You can test/vet your sample data against it and adjust as necessary.

If we don´t find a person that has all certificates necessary then we can identify whos has the lowest number of gaps and apply a Dispensation for the person to act in that role temporary.

Yes, that's what I mean with "or equivalent"

After posting:
On second thought, you could drop the relationship between Role and InstallationRole.
create a relationship between RoleCertification and InstallationRole. That should handle
InstallationRoleCertifcation (matrix/table). Employess may/may not have "approved cert"
but can act with VPOps signoff.

 

[DBApprentice]

Here's a rough draft, but it may give you some insight. Ignore the Integer 10 stuff it's part of a default in the package.
I used Visual Paradigm community/free for this. I used to use community ERWin, but it was dropped by supplier. Then went to Toad Data Modeler Community and they dropped that. VP is most comprehensive but doesn't work directly with Access.
This is a draft. You can test/vet your sample data against it and adjust as necessary.


Yes, that's what I mean with "or equivalent"

After posting:
On second thought, you could drop the relationship between Role and InstallationRole.
create a relationship between RoleCertification and InstallationRole. That should handle
InstallationRoleCertifcation (matrix/table). Employess may/may not have "approved cert"
but can act with VPOps signoff.

View attachment 97959

Your help is much appreciated, jdraw!

I will review your diagram and if I have further questions I will post them.

Let me take a look at Visual Paradigm since PPT or Excel are not good, and the free online Diagram builder I´ve found had many limitations.

Thanks!

 

[jdraw]

Happy to hear some of the responses are helpful. As for VirtualParadigm it is a full function product/tool supporting various topics and approaches. I use the free/community version only for ERD, and then only skimming some of its features. It may be overkill to get too involved and distracted.
I'm going to pose a few questions and comments that may help. Hopefully they may assist with focus.
Are your Roles different from Positions? Or is Role more aligned with "duty on a particular Project"?
Do you have a list of "diplomas/certificates" that identify requirements for your "roles" as per your post?
eg. Mechanical diploma. this means he can act as Lead Mechanic and Mechanic

Just trying to get some sense of Role requisites(mandatory) and Employee certificates to see the degree of compatability.
To determine if employee X meets requirements of role W, you'd need to compare apples to apples.

 

[DBApprentice]

Thanks jdraw!

Here are the answers to your questions:

Are your Roles different from Positions? Or is Role more aligned with "duty on a particular Project"?

Roles and Positions are the same thing, like Mechanic, Business Development Manager or VP of Operations.

Do you have a list of "diplomas/certificates" that identify requirements for your "roles" as per your post?
eg. Mechanical diploma. this means he can act as Lead Mechanic and Mechanic

Yes we have a Competence and Training Matrix per installation to do that, it helps us to identify diplomas (technical or graduation), certificates and Flag endorsements letters for each position. For instance, a Maintenance Supervisor needs to have a technical diploma in a couples of disciplines (mechanic, electrical, automation etc.) plus all certificates mandatory per country regulations and finally a endorsement letter from the local navy/port authority or from the authority responsible for the installation. Our gap analysis is in fact run based on these matrices. I have attached the training matrix so you can view the info we collect and compare, the competence matrix I won´t be able to upload here due to sensitive info.

I am attaching a image of our role deviation matrix so you can have a better grasp of the info we need to keep, this is a new matrix we decided to have at hand whenever a quick search for employees eligible to fill in positions is presented. It´s called Role Deviation Matrix and it helps me to search employees eligible to fill in positions in 2 different ways: identify which positions/roles an employee can fill depending on the installation and also given a position and installation it returns eligible employee list.

Thanks again for your time!

 

[jdraw]

Seems you have things under control. Good stuff.