Re: Hopelessly circular relationships

Richard Hollenbeck wrote:

> I'm building a grade book program in MS-Access but I ran into a dilemma
> regarding circular relationships.
>
> * Courses have Activities.
> * Activities have SubActivities.
> * SubActivities have Scores

They do? Do you mean that students partaking of a subactivity are assigned scores, or that a subactivity has a maximum score that can be attained by students who partake of a subactivity?

> * Scores are assigned to Students.

Well, maybe: a student is assigned a score for each subactivity that they take part in.

> * Students are in Courses.

No; students take courses, but the courses exist independently of the students that take it.

> Full Circle!

I don't think you've thought this through accurately yet. And once you have thought it through, you will find that the current circularity is irrelevant. You may have some hopelessly circular thinking, but not hopelessly circular relationships.

For example - does a course get given once, or can the same course be given by the same instructor several times, with different students each time. (And, potentially, different activities and subactivities.)

Do all activities have subactivities? Can an activity have a score in its own right, or only as a sum of the scores for its subactivities.

etc.

> I have a table called "StudentsInClasses" with only two fields: StudentID
> and CourseID (both key fields) in order to join Courses with Students like a
> bridge between the two otherwise unrelated tables. Another table with
> multiple keys, called "StudentScores" is keyed to StudentID and
> SubActivityID. It the has only one other field in that table called
> "Score." There are six tables involved in this loop. Due to the nature of
> the relationships, I do not know how to represent it without this
> circularity.
>
> Is this a problem?

Your description is woefully incomplete - but before posting the proposed schema, resolve the other issues I've highlighted.

For instance, how is a SubActivityID related to anything else - course, activity, subactivity. We can guess - but we can't be sure and making us guess is worse than useless.

> I suspect it is what's causing me hours of frustration. Is this an example
> of a "Boyce-Codd normal form" problem?

No.

> Could my problem be easily solved if
> I understood that normal form better?

The primary area that you need to understand better is the application area. Your description is not clear enough.

The 'institution' runs courses - each course is different from every other, is given by a single lecturer, and has a different start and end date, scheduled lecture times, ... Even if the lecturer considers that he is teaching 'Database Modelling 101' each time, each occurrence is different.

Each course consists of one or more activities. Each activity is associated with exactly one course. Each activity has a name, and an indication of when it occurs during the course. ...

Each activity necessarily involves one or more subactivities. Each subactivity is associated with exactly one activity. It has a name, and a score and ...

The 'institution' enrolls students. Each student is allocated a student number, but also has a name and various other bits of identifying information.

Each course is taken by at least one student. A course is cancelled if no students enrol.

...

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/