Database for Training Matrix

[DBApprentice]

Hi, I am coming to you once again to ask you help so that I can develop an ER Diagram of a Training Matrix Database that I am willing to build and replace the current Excel Spreadsheet. The current file is becoming difficult to follow-up on and some features were identified as a nice to have but an Excel would not allow such level of relationships and the time to build that would be a major downside.

I had a previous conversation here in this forum about part of that, so if this resounds similar, it´s not as the ther thread deals with another DB I am also working on. This is post I used. Gasman was very kind to support me on that matter and this time I came a little more prepared and designed a ER Diagram using his advice

In this diagram I have tried to explain some of the tables for follow-up. The original file in Excel is also attached so you can see and understand the current situation and how to move it to a RDBMS. If you have any trouble to access the link I have attached a copy of the ER Diagram in PDF too.

Can you please help to identify any mistakes I have made before I move on to MS Access?

I really appreciate any help.

 

[mike60smart]

Hi

Your PDF is very difficult to read in its current format.

Can you change the format so that it is easier to read?

 

[DBApprentice]

Hi

Your PDF is very difficult to read in its current format.

Can you change the format so that it is easier to read?

Hi mike60smart the link brings you to the ER Diagram, just click here so you can view and zoom in and out. Thanks for your time!

 

[mike60smart]

Hi

The ER Looks like a standard Many to Many Relationship as follows:-

Many Training Subjects are required by Many Departments

Many Training Subjects are carried out by Many Schools/Organisations

 

[DBApprentice]

Hi

The ER Looks like a standard Many to Many Relationship as follows:-

Many Training Subjects are required by Many Departments

Many Training Subjects are carried out by Many Schools/Organisations

I am not sure I followed you...

Indeed I tried to avoid Many-To-Many relationships on this ER Diagram, for instance Trainings are carried by Schools, but only the approved ones by my company will be accepted so I figured I had t make a TrainingBySchools, since it can change over time based on training quality we can remove a school untill they prove their quality again I built the TrainingBySchoolUpdates table to handle these situations.

In our situation, the Trainings are required by Installations and Positions, not by Departments.

Do you think I made a mistake in those parts?

 

[Pat Hartman]

Indeed I tried to avoid Many-To-Many relationships

Relationships are what they are. You don't get to decide to implement them differently.
The Trainings ..TrainingsComments relationship is backwards. TrainingID belongs in the comments table. Remove TrainingCommentsID from Trainings.
The Schools == SchoolAddress is also backwards.
The TrainingsBySchoolsUPdates is a mystery but it should propbaly be BETWEEN Schools and TrainingBySchools
The TrainingBySchools== SchoolTrainingComments is also backwards

 

[mike60smart]

I am not sure I followed you...

Indeed I tried to avoid Many-To-Many relationships on this ER Diagram, for instance Trainings are carried by Schools, but only the approved ones by my company will be accepted so I figured I had t make a TrainingBySchools, since it can change over time based on training quality we can remove a school untill they prove their quality again I built the TrainingBySchoolUpdates table to handle these situations.

View attachment 99956


In our situation, the Trainings are required by Installations and Positions, not by Departments.
View attachment 99955


Do you think I made a mistake in those parts?

Hi
If you look at your table for Training then you need to delete the field TrainingCommentID
The same applies for the table TraininBySchools - delete the field SchoolTrainingCommentID
All Comments should be in a separate table
See the ER Diagram attached mapping out the relationships between Training and Schools.
There is a Junction table between Training and Schools to cater for Many Training Items and Many Schools

 

[DBApprentice]

Relationships are what they are. You don't get to decide to implement them differently. (...)

Hi, Pat! Thanks for your input. Relationships are part of my problem in DB design, I am still trying to wrap my head around it. I found a tutorial on YT that I will be dedicating some time to get it right. So far I realized it is a design from the business rule but anything that would violate 1-3 NF would be help me understand relationships.

The Trainings ..TrainingsComments relationship is backwards. TrainingID belongs in the comments table. Remove TrainingCommentsID from Trainings.
The Schools == SchoolAddress is also backwards.
The TrainingBySchools== SchoolTrainingComments is also backwards

Appreciate your keen eyes! I have them fixed.

The TrainingsBySchoolsUPdates is a mystery but it should propbaly be BETWEEN Schools and TrainingBySchools

The training updates has to do with the constant updates and revisions we receive from authorities, for instance the Navy decided to change a mandatory training so it requires Able Seamen to have Fast Rescue Boat training we need to put this information there.

All revisions or inclusions of trainings to our matrix and wee need to track it to see when they will be in effect, which authority changed it and an explanation on how it affects our matrix. Some trainings are affected by 2 or 3 different authorities' overlaping decisions and we have discussions with the owners of the risk so they can provide feedback on how to do the update (make it affect more positions so we can have more people to cover), we need to have this tracking too, as they hold final decision on the matter.

Anything you see inside the red box is a regulation affecting the training. If SF 301 is changed on ACLS training we need to include what is the change and how it affected our matrix (Removed or Included Positions? Change the hours in each training? Everybody needs to be retrainined?)

 

[mike60smart]

Hi
Everything in the Red Box needs to be removed from this table and each element needs to be a new Record in a separate table.

 

[DBApprentice]

Hi
If you look at your table for Training then you need to delete the field TrainingCommentID
The same applies for the table TraininBySchools - delete the field SchoolTrainingCommentID

Thanks, I have updated them as per Pat said too.

All Comments should be in a separate table

I created SchoolTrainingComments to keep track of feedbacks and issues we have from the school as for TrainingsComments is to track discussions we have about a specific trainings. Despite they are holding the same fields now I am feeling I will have to create the fields to collect feedbacks from TrainingSchools. But you mean all comments can reside in a single table?

See the ER Diagram attached mapping out the relationships between Training and Schools.
There is a Junction table between Training and Schools to cater for Many Training Items and Many Schools

I have compared them and I think they are OK now. Agree?

 

[DBApprentice]

Hi
Everything in the Red Box needs to be removed from this table and each element needs to be a new Record in a separate table.

Hi, I have a new image to example how they work today in Excel. In the ER Diagram I have the table RequiredBy handles them.

Is it correct?

 

[Pat Hartman]

1. You could use a single table for the comments but I do not recommend it since it would prevent you from enforcing RI which is more important. You can reuse forms/reports by changing their RecordSource when you open them. This will be trivial as long as all the columns and validation rules remain consistant.
2. If the courses change versions over time, the FK from the soldier to the training should be to the specific version he took. That is why I suggested changing the relationship of the updates table. If one version includes training in RIB and the others don't, that's a pretty important distinction. This would have been downright impossible to manage with Excel but you can do it pretty easily with Access. To make this work well, add a discontinue date so you know that a course is no longer available. This will ensure that only active courses will show in the list you are picking from. Also, only the current version should have a null discontinue date and as you add the new versions you can ensure this is taken care of.