Re: Bizarre Many-toMany relationship issue

Derek,

  What are you trying to do? Are you trying to write queries to process data,
or are you trying to design a schema that enforces business rules? If the former,
provide sample data and explain what result you are after that you cannot achieve.
If the latter, show some data that puts the database into an inconsistent state and explain what you are trying to prevent from happening.

  The only question I see here is that you want to make sure user U, who finishes
course A, gets credit for cert A and cert B. As Ray pointed out, there is no
apparent way your tables store the information that someone gets credit for something. A user deserves cert A, as far as I can tell, if there is no course
required for one of cert A's reqs that U hasn't enrolled in. It's something close to this (untested).

select cert_id from cert C            -- Select all certs where
where not exists (                    -- there isn't
  select * from cert_req Q            -- a requirement
  where Q.cert_id = C.cert_id         -- for that cert
  and Q.course_id not in (            -- for which the associated course 

> 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 Tue Feb 04 2003 - 02:17:47 CET