Bizarre Many-toMany relationship issue
Date: 3 Feb 2003 09:20:13 -0800
Message-ID: <979919aa.0302030920.3434be31_at_posting.google.com>
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 - 18:20:13 CET