Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Bizarre Many-toMany relationship issue

Re: Bizarre Many-toMany relationship issue

From: Steve Kass <skass_at_drew.edu>
Date: Thu, 06 Feb 2003 17:29:26 -0500
Message-ID: <b1unge$3kn$1@slb2.atl.mindspring.net>


Derek,

  You're still not saying what you're doing. You have some tables, presumably because you want to store and retrieve information.

  Do you not know how to store your users, courses, certs, etc?   Do you not know how to retrieve information you need?   Do you not know how to enforce certain business rules?

It sounds like you know how to enter at least some of the data, and if you can tell us where you're getting stuck, but showing create table statements, insert ..values statements with the data, and some idea of what you want that you can't get, it will surely help.

SK

Derek wrote:

>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 - 16:29:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US