mysql - Database design & normalization -

i'm creating messaging system e-learning platform , there design concerns i'd feedback on.

first of all, important me , system highly modifiable in future. such, maintaining high normalization across tables important.

on how system work:

• all members (students or teachers) part of virtual classroom.
• teachers can create tasks , exercises in these classrooms , assign them 1 or multiple students (member_task table not illustrated).
• a student can request specific task or exercise sending message teachers of classroom.
  • messages sent students sent teachers. cannot address message specific teacher.
  • messages sent teachers can addressed 1 or more students.
  • students cannot send messages other students.
  • messages behave chat, meaning private conversation starts between student , teachers when send message.

here's er diagram made:

so question is, table normalized purpose? there can done reduce redundancy of data across tables? , out of curiosity, in bcnf?

another question: don't intend ever implement delete features anywhere in system. "archiving" said classroom/task/member/message/whatever hidden/deactivated. there reason use fk?

edit: also, friend brought attention conversations table might redundant, , kinda feels so. thoughts?

thanks.

one concern have don't see users table hold students , teachers info (login, email, system id, role, etc) assume there similar in our system?

now, looking members table: students change classes every semester or , don't want last semesters' students receive new messages. suggest following:

members ============= pk member_id  fk class_id fk user_id -------------- join_date leave_date active role 

the last 2 fields might redundant:

• active: alternative solution if want avoid using dates. become false when user stops being member of class. since there not delete feature, members entry has preserved archive purposes (and historical log).
• role: depends on how setup users table , roles in system. if user entry has role field(s) not needed. however, field allows same user assume different roles in different classes. example: 3rd year student, member of class 2 years ago, working ta/la (teaching/lab assistant) same class. depends on how institution works... in bsc had "rule": grade > 8.5/10 in java volunteer workshops other students (using uni's labs). finally, field if used mask or constant, allows roles extended (future-proof)

as fks suggest using them data consistency. things can ugly fast without fks. limitations impose can worked around , needed: purpose of archiving message sender_id if sender has been deleted accident? also, note in systems fks indexed improves performance of queries/joins.

hope above helps , not confuse things :)