employee training database (1 Viewer)

[lordhp10]

Hi, im designing a employee training database system for a company of 500 where it records the training details of each employee

the following are the tables created so far

Table 1. EmpDetails

Employee no(primary key)
Employee Name
Date Of Joining
Employee Subgroup
Position
Employee Email Id
Employee User Id



Table 2. EmployeeTrainingDetails

Organizational Unit
Cost Center
Employee no(primary key)(foreign key)
Employee Name

Table 3. Program Info

Program Code(primary key)
Program Description


I have a many-to-many relationship between [EmpDetails]![Employee no] and [Program Info]![Program Code]

now im confused how to save many training details attended by each employee

1. Should I create a table and store the [Employee no] and [Program Code] in new records each time whenever a employee attends a training

OR

in some other form..??



I must be able to generate reports based on
training wise(training name and list of employees attended) and
employee wise(employe name and list of trainings attended)


can someone please suggest a good method and kindly help me out.

 

[pr2-eugin]

You seem to have started with the right structure, but kinda lost track. Not long off..

The following is the corrected structure.

EmpDetails
EmployeeNo (primary key)
EmployeeName
DateOfJoining
EmployeeSubgroup
Position
EmployeeEmail
EmployeeUserId

EmployeeTrainingDetails
OrganizationalUnit
CostCenter
EmployeeNo (foreign key)
ProgramCode (foreign key)
DateAttended

Program Info
ProgramCode (primary key)
ProgramDescription

I have highlighted the new fields that are required. You will be able to Query the tables by JOINING them. Record the information of when the employee attends a course etc. The relationship needed is..

One Employee can attend Many Training.
One Program can be attended by Many Employee.

 

[lordhp10]

how about i create a different table with the following values

New Table
EmployeeNo (foreign key)
ProgramCode (foreign key)
DateAttended


bcoz if i do it in the EmployeeTrainingDetails table doesnt it repeat the values of
OrganizationalUnit
CostCenter which are not needed every time a employee attends a training..??

 

[pr2-eugin]

I was not entirely sure what they were, so kept them intact. If the information is not required all the time, you can move it into the EmployeeTable.. Think they will be constant?

If they are for an Organization, then I guess a new table for Organization is in order.

BTW, Welcome to the Forum.

 

[lordhp10]

thanks a lot for that help
and i have started to like the forum already

im not entirely clear about the relationships design part
can you suggest me where i can start off learning it..??

thanks again

 

[pr2-eugin]

I am sure you will learn a great deal from several other helpful member on the forum too. There should be plenty of existing DB design available in DB Answers site. You might have a look at that.

Check out THIS FILE, for some basic idea on Relationships.

I am sure this was only my idea, other members might provide a better structure too. Good Luck !