Relational Training Database Setup.

[MikeAz1]

Hi,

I’m trying to set up a database which collects similar information, but I’m not sure about the best way to store the information. It is a training database which tracks Employee Info and the training they can have. Currently, an employee can have up to 40 different types of training, but for this initial purpose we will only identify 2 to keep it simple.

So with simplicity on the mind here are some tables and fields:

Employee_Info_Table:

Field_EmployeeID

Field_EmployeeName

Safety_Training_Table:

Field_EmployeeID

Field_IsApplicable

Field_FormalTraining

Field_LicenceNo

Field_CompetencyScore

Field_ExpiryDate

Field_TicketStatus

Field_OtherInfo

Enviro_Training_Table:

Field_EmployeeID

Field_IsApplicable

Field_FormalTraining

Field_LicenceNo

Field_CompetencyScore

Field_ExpiryDate

Field_TicketStatus

Field_OtherInfo

Now, currently I have each of these as separate tables, but as I said, with 40 different types of training this will add up to be a lot of tables. Plus, with the information being the same for each of the training’s I know there must be a way to keep it all together.

I had the great idea of setting this up:

Employee_Info_Table:

Field_EmployeeID

Field_EmployeeName

Safety_Training_Table:

Field_EmployeeID

Field_TicketID (which equals EmployeeID plus “Safety”)

Enviro_Training_Table:

Field_EmployeeID

Field_TicketID (which equals EmployeeID plus “EMP”)

Training_Info_Table:

Field_TicketID

Field_IsApplicable

Field_FormalTraining

Field_LicenceNo

Field_CompetencyScore

Field_ExpiryDate

Field_TicketStatus

Field_OtherInfo

It looks as though it would work, however I don’t know how to have Training_Info_Table::Field_TicketID to keep the values of 2 or more different fields.

This is only the latest idea in a long list of failed ones, so any suggestions would be appreciated. If you need any other information please post and I’ll do my best to explain better.

(Also, I don't usually name my tables and fields with "table" or "field", it's just to help avoid confusion)

Thankyou.

[Stuart Taylor]

Why not just have a "Training Program" table.

[MikeAz1]

I'm not sure what you're suggesting sorry. Would that table be to house all the training fields identified in the first example? Meaning would the "Training Program" table use all 8 'safety' fields, as well as all 8 'environmental' fields, etc?

[comment]

The suggestion is to have a table where each training program would be a record, and the name/type of the program would be a field. Then you need another table where each record is one employee participating in one training program. See a very similar arrangement here:

http://www.fmforums.com/forum/showpost.php?post/246136/