[Zope-DB] Database design help

[Russell Hires]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> Hi Russell,
>
> I'm no expert in this myself but I've spent some time working on my own
> design recently. 
For something similar to what I'm doing? Let's collaborate! 
> It looks pretty good so I have no direct criticism but a
> couple of questions which might help you finalise the design. Your
> descriptions are a bit too brief. It would be more useful if you wrote full
> SQL statements including types and references.
I'll send that in a different followup. I've never done anything like this...
> Can students belong to more than one class at a time?
Yes, they have to! :-) 
> How about a table of users (USER_ID, fname, mname, lname) so that students
> are just a specialisation of users? In fact you might want to allow
> teachers to have addresses as well. 
Well, this is just for keeping track of assignments/grades. I thought about 
the teachers having addresses, but decided I should limit information on the 
teachers. 
> You have lots of different "grade" attributes. It's not quite clear how
> they work.
Yeah, this is a problem in dealing with school related stuff...the terms have 
multiple meanings, and then the terms clash with programming terms as well, 
like "class"...
> Both students and classes have "grade_level" this is probably redundancy
Ugh...this is multiple meaning problem with its ugly head: students have a 
grade_level because they could be in 8th grade, or 10th grade...classes have 
a grade_level because they are normally taught on a 9th grade level, or an 
11th grade level (i.e., most students in the 9th grade take geometry), but 
the students themselves could be in a different grade (advanced 8th graders 
taking geometry)
> Why do Grades have a "grade" attribute.
This is a separate place for keeping track of "final" grades or a running 
total of an accumulated grade average. Maybe I should change the field to 
"grade_avg"...
> I'm not sure what grades are but if STUDENT_ID and CLASS_ID are both
> primary keys you will get errors as soon as you get you have students with
> the same CLASS_ID. Seeing as students_classes has the same relations as
> grades you can eliminate it and use grades instead.
Okay. That sounds like a plan. You're right about that. I'm still monkeying 
about with the grades table, though, since I only have a basic feeling about 
what should be where. For my first DB, I had to choose something difficult to 
model! :-) 
> "semester" and "school_year" are duplicated in grades and classes.
> What is the difference or relation between "semester" and "school_year"?
Semester is half a school year, and high schools (at least in Florida) keep 
track of your grades based on semester. Two semesters (fall and spring) where 
you pass all of your classes you get to go the next grade. OTOH, maybe I 
should switch that to quarter, since that's how often report cards come out. 
But then again, some schools do report cards every six weeks and not every 
nine weeks.  
> If you're going to use foreign keys make sure you use a database which
> supports them.
Yes, I'm going to be using PostgreSQL for this. 
> Charlie

Thanks so much!

Russell

- -- 
Linux -- the OS for the Renaissance Man 
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE97gbjAqKGrvVshJQRAkHjAKCHsQjTC6MG9iHvi6Tix8xcxNEdgQCg3UE2
EOAk2k8ogq2hEI/BC7wL1bc=
=4o0t
-----END PGP SIGNATURE-----