title: Design suggestions for Teachers,subjects and grades database
body: <p>As part of database design task for a high school, I am kind of stuck with classes table. The tables created so far are:</p>
  
  <pre><code>Students
  --------------
  Id
  name
  
  Grades (grade 1,2,....9,10)
  ------------------
  id    
  description    
  term
  
  Subjects (science,maths...)
  -------------------
  id
  name
  
  grade_subject (subjects tought in grades)
  ----------------
  id
  grade_id
  subject_id
  
  teacher
  ---------------------
  id
  name
  
  teacher_subject (teachers who are assigned to teach subjects in particular grade)
  ---------------------
  id
  teacher_id
  grade_subject_id
  </code></pre>
  
  <p>I am not confident about table design of teacher_subject table. This table makes use of id of grade_subject_id which might (not) be a good design. Should this table have two FKs , one grade_id and subject_id?</p>
  
  <p>I also need further to store number of classes conducted  on particular subject and grade by particular assigned teacher.</p>
  
  <p>Would this table fit in this case:</p>
  
  <pre><code>Class (stores daily teaching schedule)
  -----------------------------------
  id
  *teacher_id
  grade_id
  subject_id*
  
  *or only teacher_subject_id from teacher_subject table*
  
  date
  start_time
  end_time
  status ( conducted/cancelled/postponed)+
  </code></pre>
  
  <p>And lastly, a table to store information about who attended the class</p>
  
  <pre><code>attandants
  --------------------
  student_id
  class_id
  </code></pre>
  
  <p>Any suggestions would greatly be appreciated.</p>