×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

List of Required Items in a DB
2

List of Required Items in a DB

List of Required Items in a DB

(OP)
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

RE: List of Required Items in a DB

2
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

RE: List of Required Items in a DB

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"

RE: List of Required Items in a DB

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


-Chris

"What is Wormwood anyway...?"

RE: List of Required Items in a DB

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"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close