Paul, normalization is a good point, and the class table shoud probably be expanded to have date/time/period for multiple classes. You also will want to drop the underscore at the beginning of the field name, as this is usually reserved for system variables. Also, the designation of Table or Tbl is usually put at the beginning of the name to distinguish it from views, procedures or triggers.
What I am seeing then is that there are 3 base tables:
Class Table - identified by Class Id and describing class information, Student Table identified by Student Id and describing student information and Teacher Table identified by Teacher Id and describing teacher information.
There are then 2 connector Tables, Student Class Table and Teacher Class Table. I assume that the relationships are that every class has at least one teacher and that every student has at least one class and that the constraints would reflect these relationships. These tables would have foreign keys identified by Class ID, Student ID and Teacher ID.
Have you considered building or accounting for historical archival information, as in the case of a teacher, student or class changing? If so, you might want to put some date/time stamps on the connector tables. -Pat
Paul M Foster <paulf@quillandmouse.com> wrote:On Mon, Oct 14, 2002 at 11:08:08PM -0400, Russell Hires wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello all...
>
> Well, I'm off to the races. I looked around the internet a little bit and
> found that there are indeed Opensource/Free Gradebooks out there to be had.
> The problems with them are that they are too big/comprehensive, too difficult
> to use (we're talking teachers here!), don't have web-based interfaces, or
> have to built from scratch by somebody. Teachers don't have time for this.
>
> To start, I've put together some basic SQL tables, but before I commit them
> to the database, I would like to solicit comment from my primary Linux
> community about basic design, things I might have missed, etc. I'm using
> Postgresql as my database, and the web-based interface will be built with
> Zope.
>
> But here's the tables. The way I've got this is in a shorthand format, with
> underscores around the primary key of each table. The table names will have
> _tbl at the end of their names, and will be the item directly before the
> opening parenthesis.
>
> Student_Tbl(_STUDENT_ID_, FNAME,
> LNAME,SSN,ADDRESS,CITY,ST,ZIP,GRADELEVEL,MOTHERNAME,FATHERNAME)
I'd leave off SSNs in all tables. Privacy issues. Even if the school
needs to have this number, I suspect teachers don't; student_id should
suffice for teachers.
>
> CLASSES_TBL(_CLASS_ID_,NAME,DESCRIPTION)
>
I'm not sure if you're aiming at just having teachers run this, or
having the school administration have something to do with it. If the
latter, then the school itself would be filling in this table. In that
case, you would also want to add grade_level, and class_period to this
table, since these things are inherent in a "class".
> STUDENTS_CLASSES_TBL(_STUDENT_ID_, _CLASS_ID_, CLASS_PERIOD,GRADE_AVG)
>
The classes_tbl would really need the class_period field, not this one.
This is just a many-to-many table to link students to classes. And
grade_avg? Really needed?
> TEACHER_TBL(_TEACHER_ID_,FNAME,LNAME,SSN,ADDRESS,CITY,STATE,ZIP)
>
As above, drop SSN, particularly if other teachers could potentially
look at this teacher's SSN.
> TEACHER_CLASS_TBL(_TEACHER_ID, _CLASS_ID_, _CLASS_PERIOD_)
>
This is another many-to-many link table between teachers and classes.
Since a class_id is unique to each class, and the class_period is in the
classes_tbl, you don't need class_period here (normalization, you know).
And this table is only necessary at all given the assumption that there
may be more than one teacher per class. Otherwise, you can put the
teacher_id in the classes_tbl and be done with it.
HTH,
Paul
---------------------------------
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos, & more
faith.yahoo.com
This archive was generated by hypermail 2.1.3 : Fri Aug 01 2014 - 16:11:02 EDT