Training Tracker Table Relationships

[Desstro]

Im having a little difficlty deciding how to set up relationships. Basically I will have a personnel table that houses names, positions emergency contact info etc....



What this DB will be used for is tracking a persons classroom training.

There is kaplan courses, BP courses, Acuren courses, RAT courses and Smith driver training courses. There are multiple training modules within each course and each have a specific amount of time they are good for before a refresher is needed ie annual refresher bi annual refresher etc....

I was thinking of setting up a table for each i.e., Kaplantbl, BPtbl, etc....

And the purpose will be to be able to enter data as employees complete courses and then set up queries to to run reports showing if an employee is coming due.
Would also like to be able to set a query to run a report on an individual to see what training (s)he has completed.

Really Im just a little unsure as to how to set up my relationships.

Any suggestions would be much appreciated.

[jzwp11]

All courses, no matter which organization sponsors them should be in 1 table (rule: like data in 1 table). So, you might have the following 2 tables:

tblCourses
-pkCourseID primary key, autonumber
-txtCourseTitle
-fkSupplierID foreign key to tblSupplier


tblSupplier (the organizations that provide the courses)
-pkSupplierID primary key, autonumber
-txtSupplierName

The above structure assumes that a supplier can provide many courses, but that the same course cannot be supplied by more than 1 supplier.

Now, can a course be sponsored by more than one supplier? If so, then we would have to alter the above structure as follows which includes the addition of a junction table (tblCourseSuppliers)

tblCourses
-pkCourseID primary key, autonumber
-txtCourseTitle

tblSupplier (the organizations that provide the courses)
-pkSupplierID primary key, autonumber
-txtSupplierName

tblCourseSuppliers
-pkCourseSupplierID primary key, autonumber
-fkCourseID foreign key to tblCourse
-fkSupplierID foreign key to tblSupplier

You also said that a course can have many training modules. If I were to assume that a course can be supplied by multiple suppliers then do the modules change from one supplier to another?

[Desstro]

Thanks,

The first part of your response is correct, Suppliers provide training but no supplier provides the same course name.

[jzwp11]

OK, then we can go with the first set of tables:

tblCourses
-pkCourseID primary key, autonumber
-txtCourseTitle
-fkSupplierID foreign key to tblSupplier


tblSupplier (the organizations that provide the courses)
-pkSupplierID primary key, autonumber
-txtSupplierName


Now, since a course can have many modules, that describes a one-to-many relationship


tblCourseModules
-pkCourseModID primary key, autonumber
-fkCourseID foreign key to tblCourse
-txtModuleName
-longFrequency (# of days between when employees have to retake the training module)

Do you have courses that do not have any training modules or do all courses have the modules? Do modules as well as courses have specific refresher frequencies?