Relationships in a school database

Question

I am designing database for a school app. Student is the main entity in the database. Student has relation with Parent & Guardian.

I am confused as where to store references. As will student table have foreign keys from parent & guardian table or vice versa.

UPDATE: Need to store student current data like course / batch / roll no etc which keeps on changing. For this purpose I am creating a new table student_current to store the current data with one to many relation from student to student_current table. At any time, i need to create a join with student_current table to fetch latest student_current record. To improve performance, I am storing primary key of student_current table as foreign key in student table and then making a simple join on this to fetch data. Is this approach right??

Also would appreciate your help in suggesting how to store revisions for student data.

Current schema is like:

student { id, student_current_id, name .... }
student_current { id, student_id, course .... }

Please help.

Answer 1

You might need an intermediate table to store this relationship, as it's possible that a student has more than one parent/gaurdian (a mother and a father) and it's possible that a parent/gaurdian has more than on student.

student
-------
  id


gaurdian
--------
  id

student_gaurdian
----------------
  student_id
  gaurdian_id

The table student_gaurdian can store all the relations between students and gaurdians, and allows many-to-many relations.

---

RE: your new question

Having two tables with keys to each other looks a little weird to me, but if you really are having performance problems and this relieves them, then it's fine.

Answer 2

Student should be FK in Parent/Guardian table as in school you can not have parents or guardian without a student.