Many to Many form setup

[schmadly]

I have a db with a total of 4 tables. The first 3 are "Topics" (1 topic can be taught by many Instructors), "Instructors" (1 instructor can teach many topics), and "SkillSet" (the join table w/ the primary keys from the previous 2). The 4th table is "Courses" (1 course can have many "SkillSets).

My issue:

I want to input a course in a form and then in the subform to be able to choose a topic (from a drop-down) and then have a second field that will show me which Instructors can teach that topic. I cannot figure out the following:1. How do I tie the Topic Name field to the Instructor name field (so that based on the topic I choose it only shows me the instructors for that topic).
2. The proper design - right now I have no primary key in the join table (SkillSet) since I am using the primary keys from "Topic" and "Instructor".

Any help would be greatly appreciated.

 

[The_Doc_Man]

Answer to #2 - junction tables, if they have any primary key at all, very often have a compound primary key comprised of the individual foreign keys that lead to the individual entry.

Answer to #1 - depends on something not seen clearly here.

I'm going to attack this one in the way I would approach it.

Instructors:
InsID - PK, number for this instructor
demographic data regarding instructor

Topics:
TopicID - PK, number for this topic
name of topic
other information about topic worthy of database space

SkillSets: Junction table between instructors and topics
TopicID: Foreign key to Topic table.
InsID: Foreign key to Instructor table.
Option: Add an autonumber to act as skillset number. (Not required but some people like to do that.)

Courses:
CourseID - PK, unique course identifier
Course name and other stuff

Here is where we have the problem:

CourseSkills (you said this a 1/many with SKILLSETS)
CourseID - FK to course
Skillset's PK used as FK
- or -
CourseTopics
CourseID - FK to course
TopicID - FK to ONE of the course topics.

These two tables have different scope with respect to the instructors and their abilities. It might be the source of your confusion. You have a many to many mapping between courses and skillsets. And that means you cannot expect to get a valid answer for "Which instructors can teach this?" At least, you can get a score, perhaps. But there seems to be a disconnect between the data you have and the data you want to measure. Am I outright reading that wrong? Or did you get loose with your verbiage?

 

[schmadly]

Thanks for the response (I was out yesterday). Yes, we understand each other as far as what I have tried w/ the db design. I essentially have four tables (PK and FK just as you described) and "SkillSet" is in the middle - the other 3 link into it. That's where I'm stuck.

What I want to do:
1. Type in a Course name (such as "Mgmt. Skills") and then pick any combo of SkillSets (this Instructor taught this Topic) and build a course, so to speak full of various Instructors teaching various topics. Each time I create a Course, it will probably be a different combo of SkillSets (Instructors and what they teach). Does this make sense?

Should I just abandon the Courses table and maybe list it somewhere else?

 

[The_Doc_Man]

Some issues that are not clear:

How many instructors will teach a single course?

Is it possible for a course to teach more than one skillset and for no single instructor to have exactly that course's combination of skillsets?

Is it possible for a course to teach more than one TOPIC for which no single instructor would have skillsets that exactly cover that course?

Your business rules and the complexity of the fine structure of what you are describing makes this a non-trivial problem to understand.