Relationship and Table Design Help

[jmw229]

Hey Guys,

I'm building a database that will be used to track the professional training and development courses employees take at my work. I'm relatively new to db's, and I find the relationships a little confusing to grasp in this case.

I have attached what i've done so far.

I believe i have created the right tables but that I have made a mistake in the relationship.

I think the relationships are many-to-many, but I have managed to put one-to-many and not sure exactly how to switch it to many-to-many

At the end, I plan on having a form with a subform that allows the user to view the courses and individually add people to that course.

Your help, input and advice would be greatly appreciated.

Regards,

Jason

[timothybard]

You mention that you think the relations are many to many. Can you tell us which tables you think have a many-to-many relationship. From the tables in the database, it seems it would be appropriate to have a many to many relationship between the employees and the courses (each employee can be enrolled in several courses and each course can have several employees enrolled).

Creating a many-to-many relationship is actually done by using two one-to-many relationships with an intermediate table holding the necessary information for the relationship. When viewing the relationships in the database, it appears you have already done this by the use of the enrollment table.

Therefore, everything that you have in your database so far looks good to me; the one-to-many relationship from employees to enrollments and the many-to-one relationship from enrollment to courses forms the many-to-many relationship from employees to course.

Please let me know if you need further explanation.

[jmw229]

Hey Tim,

Thanks for your response. Yes, I was thinking employees and courses should have a many-to-many but I'm glad to hear I've already done that (haha) as well as that I've got the right tables etc.

I was also thinking maybe I should zero-to-many because an employee can be enrolled in no subjects or many. However, I'm not sure it is necessary to do that and how to do that.

The user-side of this is suppose to be very simple and straight forward as it is just a quick tool I'm providing them. I've created a form with a subform that displays who's enrolled in which class, their position, date enrolled and status of enrollment. I'm not sure if it would be a good idea to use this form as simply a method to view enrollment or if i should make another form to ADD/Remove staff to a course as well as another form to ADD Courses. Do you think it would be smarter to make separate forms and create a menu for them to navigate?

I've attached two pictures:
What do you think of the instructor ID lookup because I'm not sure how to switch it to just the name haha

[timothybard]

I think a zero-to-many relationship between between employees and enrollment is appropriate; however, I believe MS Access treats a zero-to-many relationship as a sub-type of the one-to-many relationship, so you can not specify a relationship as a zero-to-many relationship.

Of course the forms you decide to go with will depend on how comfortable your users are with them. In my experience, the following form design is easy for users to understand as well as powerful enough so users don't have to open different forms each time they want to add or remove information:

Employee form: This form shows the employee fields at the top and allows the user to add, edit and delete each employee. The bottom of this form is a sub-form with a data source of the enrollment table; this allows the user to view and edit the employee as well as view and edit the employee is enrolled in.

Course form: This form allows the user to view and edit the course information at the top and the bottom portion of the form allows the user to see the employees who are enrolled in the course as well as add and remove employees who are enrolled.

This results in two main forms, employee and course, with the enrollment subform incorporated into each form to see who is enrolled in each course.