List of Required Items in a DB 2

[ChrisCalvert]

I am looking to have a list of employees and also a list of
required clases for those employees to take. What I want is to have the database set up in such way that it stores for each employee whether or not that employee has take each course. I have a table with name/extension/employee number. I was thinking about appending to that table boolean fields such as 'Class1' 'Class2', etc. I could just set up a 'training' tab, and some checkboxes on my form. However, I was thinking that there is probably some easier, better way to do this. Since I am not very experienced at creating databases, I thought I'd ask you all. I have other things for which I could use this type of tracking (to see which employees have what type of equipment assigned to them). I figure that putting all of the classes into a seperate table would be a good start but I am not really sure exactly how I would most efficiently make this show who had taken the courses. Suggestions appriciated...

-chris

 

[ThomVF]

For a robust, good relational design, you need 3 tables (you can show the data from these 3 tables in a single form but do not confuse data design with application/form design - they are 2 entirely different things).

Table#1: Employee
EmpNumber -- this is the key value, a 'natural' key if such a number already exists
EmpName
EmpPhone
EmpExtension
etc...

Table#2: Class
ClassNumber -- this is the key value, might be natural if a class has such a number
-- or could be generated on the fly as you create Classes
ClassName
ClassDate
etc.

Table#3: Emp_Class
EmpClassNumber -- Key, generated
EmpNumber -- Relates to the employee key
ClassNumber -- Relates to the Class key

This table will only have a record when an Employee must take a specific class, otherwise no record exists relating a particular Employee to a Class

The Emp_Class table creates the relationship only when it exists. An Employee might be required to take >1 class, so there would be 2 EmpClass records having the same EMpNumber but with different ClassNumber values. Conversely, there might be many different employees required to take a specific class, each case having an EmpClass record.

This is the best way to relate any number of employees to any number of classes,and vice-versa. It is called a "many-to-many" relationship (many classes for an employee and many employees for a class)

Some sample Data:

Employee
1, Joe, 555-1111, 100
2, Sally, 555-3434, 250
3, Steve, 555-7878, 110

Class
100, Networking, 6/6/2002
200, 'PC Repair', 7/4/2002
300, 'Database Admin', 8/3/2002

EmpClass
1,1,100 -- Joe must take Networking
2,1,200 -- Joe must take 'PC Repair'
3,2,200 -- Sally must take 'PC Repair'
4,3,100 -- Steve must take Networking
5,3,300 -- Steve must take 'Database Admin'

I hope this helps you with your question.
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[TheDisciple]

Hey there ThomVF,
I couldn't have xplained it any more straightforward than U already have! Even a baby should b able to understand your example w/ utmost simplicity. Therefore, U deserve a shiny star...here ya go dude!!!!

=TheDisciple= "So I took the roll and ate it; In my mouth it was sweet as honey but in my belly it was bitter as wormwood"

 

[ChrisCalvert]

Thanks for the help, that does provide me with a good understanding of what I needed. You guys rock!


-Chris

"What is Wormwood anyway...?"

 

[TheDisciple]

Hey there ChrisCalvert, WormWood is mentioned in the Bible;
It's a tree that produces VERY BITTER green liquid.

=TheDisciple= "So I took the roll and ate it; In my mouth it was sweet as honey but in my belly it was bitter as wormwood"