c# and databasing: in theory, how to create a training list

Question

I'm obviously not looking for any actual code necessarily, but more of someone to point me in the right direction. I'm trying to convert our training lists from paper to a digital format but the manager wants to keep roughly the same format intact. This would be a great project for a database type setup but im wondering how to approach it.

The current training lists are setup basically as a matrix, a table of doc names, doc numbers, revision numbers, and what jobs each doc is applicable to. Each employee gets their own packet which is basically just a duplicate of the master list with their name on it. So I figure this master list would be a table in the database in itself. I created another table with all of the employee names and pertinent information.

What my question is, is how should I approach linking the training list to each individual employee so that i could pull up an employee and see what training they have accomplished and when, etc. Essentially, how do i mark off that training as they completed it and store that information so that it can be recalled? When I started, it didn't seem like it would be that complicated but now im kind of boggled.

Any insight would be appreciated

Answer 1

What you are looking for is some times refereed to a a link table or other times simply a many to many relationship.

You want a table for your Employees, a table for your Training and then the link table could be called something like EmployeeTrainingRecords. This link table would simply contain the primary ID of the employee and the primary ID of the training they've taken.

Additionally this link table could be expanded to record information about when they took the training.

EDIT:

As far as the revision goes, that really depends on how your organization (and your application) view training program revisions. This could be as simple as having a DateTaken on the EmployeeTrainingRecords table, and also adding a new table called TrainingRevisions which links back to Training and contains the date which the program was revised, and perhaps notes about the revision.

An alternative suggestion would be to ditch the above idea of a TrainingRevisions table and simply store the training programs revision on the EmployeTrainingRecords table. This sacrifices normalization for a design that would be a little easier to use.

Answer 2

Jason Whitehorn suggested the main tables. Just to expand on what he was saying with respect to how do you mark off the training etc?

Assuming you had some extra columns on your EmployeeTrainingRecords table to track when the training took place, when the training was completed etc.

CREATE TABLE EmployeeTrainingRecords
(
  Id int IDENTITY (1,1) PRIMARY KEY,
  EmployeeId int NOT NULL,
  TrainingId int NOT NULL,
  Started DateTime,
  Completed DateTime,

  CONSTRAINT [EmployeeTrainingRecords_Employee_FK] FOREIGN KEY (EmployeeId) REFERENCES Employee(Id),
  CONSTRAINT [EmployeeTrainingRecords_TrainingId_FK] FOREIGN KEY (TrainingId) REFERENCES Training(Id),
);

You may prefer not to have an identity column on this many to many table and use a composite key (employeeId and trainingId). The reason I prefer an identity column and single Primary Key field however is that it is convenient when it comes to mapping an ORM layer which is abstracted with something like a repository pattern. You can have all your entities implement something like...

public interface IEntity<T>
{
   <T> Id
}

This makes finding entities by an id from a repository easier as they all implement the same interface.

Training Records

With the training records table you can lookup all training records for a particular user. From this you will then know what training has been started, completed or not yet undertaken. Obviously any other data which belongs to a particular instance of a training session being undertaken by an employee you could also add here.

EDIT

You may even like to include information as to when the training is scheduled? So you might also have a Scheduled datetime column on that table. This would allow you to not only know what training an employee has been enrolled in, but when it should be taken and when they 'actually' took it (denoted by the started column) and when they completed it.

These sorts of details come down to your particular domain requirements though. Just a thought...

I like the comment posted by @Jason where he effectively said that your old schema (data your importing) should not affect or influence how you design your new model / schema. As suggested you can write a throw away conversion app for this. Looking at it from this perspective though will free up your design (and constraints) and allow you to come up with something more natural that better befits your problem or what you're trying to achieve. I don't know whether your old data is considered legacy data, but there's no reason to carry forth the design if you think you can improve on it.