Re: Bizarre Many-toMany relationship issue

Sorry guys, I missed a table here:

Attempt
+++++++++
attempt_id
status
enrollment_id

If status = c (completed) then they should earn the cert.

Steve Kass <skass_at_drew.edu> wrote in message news:<b1n48a$dlt$1_at_slb9.atl.mindspring.net>...
> 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
> is not among
> select course_id from enrollment --the courses enrolled in
> where user_id = 'U' -- by U
> )
> )
>
> Steve Kass
> Drew University
>
>
>
>
> Derek wrote:
>
> > 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 Thu Feb 06 2003 - 18:46:25 CET