Solved Planning my tables (1 Viewer)

[Teri Bridges]

I have a question on how to best construct assigning roles to courses.

I currently have a db with tables courses, lessons, topics, events, and roles. What I need to do is assign roles to the courses. There can be many roles assigned to one course.

In the course form, I need to list all the roles that are assigned to take the course.

I was going to use a list selection box on the form, but then i read that this is not a good practice.

I will later need to query the db for which roles are assigned to which courses.

Guidance is greatly appreciated.

 

[plog]

I was going to use a list selection box on the form, but then i read that this is not a good practice.

First and foremost, forms have nothing to do with table structure. You don't envision how you want your forms to work then structure your tables to suit that. You structure your tables/fields properly then you build forms to accomodate them.

Since it sounds like multiple courses can have multiple roles and vice versa, you will need 3 tables for this:

tblCourses - which you already have and lists all courses
tblRoles - which list all roles--not which list all courses they go to, just all the possible roles
tblCourseRoles - which joins the 2 tables together showing which roles go to which courses and vice versa.

Then, from a form perspective tblCourseRoles would be a subform on either the Roles form or Courses form which allows the user to select which role/course goes with the currently viewed course/role.

 

[Pat Hartman]

I was going to use a list selection box on the form, but then i read that this is not a good practice.

Don't confuse the advice to steer clear of List boxes on table definitions with their use on forms. You would always use a combo or listbox to limit selection values to a small set of predefined values.

In addition to plog's advice, you probably also want a m-m relationship between roles and employees (or whatever you are calling the students). This allows you to limit the selections of a course where necessary. To make this work smoothly, you need a role that is general and that would be applied to courses that are not role specific. So, if you are going to lecture people on sexual harassment in the workplace, it really doesn't matter what the role of the employee is.

 

[Teri Bridges]

First and foremost, forms have nothing to do with table structure. You don't envision how you want your forms to work then structure your tables to suit that. You structure your tables/fields properly then you build forms to accomodate them.

Since it sounds like multiple courses can have multiple roles and vice versa, you will need 3 tables for this:

tblCourses - which you already have and lists all courses
tblRoles - which list all roles--not which list all courses they go to, just all the possible roles
tblCourseRoles - which joins the 2 tables together showing which roles go to which courses and vice versa.

Then, from a form perspective tblCourseRoles would be a subform on either the Roles form or Courses form which allows the user to select which role/course goes with the currently viewed course/role.

This is the road I started down since the post. Thank you for the confirmation. You are all a huge resource.