Bizarre Many-toMany relationship issue

Hi everyone, thanks in advance for any feedback on this. It's driving me nuts. It's been too long since I've had to do something like this.  So anyway, here goes.

We have a Learning management system to track user progress through courses. A user is enrolled in a course and that enrollment earns him a certificate. The tricky part is, a certificate can have multiple requirements and multiple certificate can have the same requirements if viewed as a course.

So for a certificate that has more then 1 requirement, you must meet all the requirements before you earn the certificate and if you meet a requirement that has a course that fulfills 2 different certificates, you get credit for both. With me so far? Good.

So here are some of the tables we have:

USER
+++++++
user_id
company_id

COURSE
+++++++
course_id

ENROLLMENT
+++++++
enrollment_id
user_id
course_id

CERT_REQ
+++++++
cert_req_id
cert_id
course_id

CERT
+++++++
cert_id
company_id

So here are the obvious relationships:

A user is enrolled in one or more enrollments

-an enrollment can have only one user assigned
A user works for one company

-a company can have many users

An enrollment is an assignment to only one course

-a course can be assigned to many enrollments
A cert is owned by one company

-a company can create many certs

A cert can have more then one requirement

-a cert requirement can belong to only one cert

So here's the jacked up part:
We want to make sure that a user who fulfills an enrollment for Course_A gets credit for both Cert_A AND Cert_B because both Cert_Req_A and Cert_Req_B have Course_A as their course. I know, it's jacked, but I didn't create it. I just have to fix it and I'm a little stumped.

I can't create a direct relationship because I keep losing the ability for an enrollment to satisfy multiple cert_reqs. Any ideas? Received on Mon Feb 03 2003 - 11:20:13 CST