Re: Hopelessly circular relationships
Date: 18 Jul 2004 12:17:13 -0700
Message-ID: <18c7b3c2.0407181117.14c4303e_at_posting.google.com>
>> * Courses have Activities.
- Activities have SubActivities.
- SubActivities have Scores
- Scores are assigned to Students.
- Students are in Courses. <<
- *Courses have Activities.
CREATE TABLE Courses
(course_nbr INTEGER NOT NULL PRIMARY KEY,
course_name CHAR(15) NOT NULL,
credits INTEGER NOT NULL,
..);
CREATE TABLE Activities
(course_nbr INTEGER NOT NULL
REFERENCES Courses (course_nbr) ON UPDATE CASCADE ON DELETE CASCADE,
activity_nbr INTEGER NOT NULL,
..
PRIMARY KEY (course_nbr, activity_nbr));
- *Activities have SubActivities.
- Note how the DRI actions are set up - important!
CREATE TABLE SubActivities
(course_nbr INTEGER NOT NULL
activity_nbr INTEGER NOT NULL,
FOREIGN KEY (course_nbr, activity_nbr)
REFERENCES Activities (course_nbr, activity_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
sub_activity_nbr INTEGER NOT NULL,
PRIMARY KEY (course_nbr, activity_nbr, sub_activity_nbr)
points INTEGER NOT NULL,
.. );
- *SubActivities have Scores
- *Scores are assigned to Students.
- No, SubActivities have points at best, and the scores belong to the student who did that sub_activity. Billy gets a 70, John gets 75, etc.
CREATE TABLE Students
(student_id CHAR(10) NOT NULL PRIMARY KEY,
..);
CREATE TABLE ReportCards
(course_nbr INTEGER NOT NULL
activity_nbr INTEGER NOT NULL,
sub_activity_nbr INTEGER NOT NULL,
FOREIGN KEY (course_nbr, activity_nbr, sub_activity_nbr)
REFERENCES SubActivities (course_nbr, activity_nbr,
subactivity_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
student_id CHAR(10) NOT NULL
REFERENCES Students (student_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (course_nbr, activity_nbr, sub_activity_nbr, student_id),
score INTEGER NOT NULL, -- not greater than SubActivities points
..);
- *Students are in Courses.
There probably would be a class register table, but this schema will also provide that information.
I have set this up so that if an activity is dropped, its subs are dropped; if course is dropped all activities are dropped and it cascades to the subs; if a student is dropped, his report card is dropped. Received on Sun Jul 18 2004 - 21:17:13 CEST