Seek for comments

[rolwong]

I am going to build a virtual mail system for school. Teacher can use this system to send mail to teacher via web, and student can also check the mail via my web site. The content of email maybe either text or attached file. Teacher can set the delivery date and edit later. Teacher can also select sending to individual stutdent, all student in a class, or even all students in school. (Can be editted by teacher before delivery date). I have think about the below structure. I tried to write in PHP, but find out a bit hard to build up. I would like to hear some critize or comments with this design:

outgoing_mail table
no | from_user_id | subject | create_date | delivery_date | content_type

mail table
no | outgoing_mail_no | to_user_id | read_flag | delete_flag

mail_user_id table
no | student_no | teacher_no

mail_text
outgoing_mail_no | text

mail_attachment table
outgoing_mail_no | attachment_no

I find very difficult to write the editting part if the teacher want to change from "sending to whole class" to "sending to individual student" in a class.

[planetsim]

i think your database design isnt very good.. which would be why..

You should have like a user/student/teacher table..

with someway in the table to identify if there a student or teacher

Then from there you will need to relate the table with others according the users id

[drawmack]

table 1) stature
ID | Title | Num

holds things like student, teacher, faculty - num allows you so say things like there is only one VP.

table 2) people
ID | Name | Email | Stature

table 3) Messages
ID | From | Subject | DlvDate | Msg | File

table 4) To
ID | MsgID | PplID

now the coding should be pretty easy.