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.

  • Cardinality of the relations is of great help in order to determine where to place the references (foreign keys).
    – jap1968
    Jan 16, 2013 at 19:34
  • I was a student at my school, now I am a prof. Consider the Party Model please. Jan 22, 2013 at 3:17

2 Answers 2


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.




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.

  • Thanks for the answer. But it will always be one to one in case of parents, while as for gaurdian, it might be one-to-many. This approach is appropriate. Thanks for the help. I would appreciate if you can help with the updated question.
    – Ehs4n
    Jan 16, 2013 at 19:02
  • What can be better approach in this scenario. student base table is having one-to-many relationship with student_current table. At any time, I want to fetch students with their current details (here the current detail will be latest one based on date).
    – Ehs4n
    Jan 16, 2013 at 19:12
  • If you know the student ID, you can just join student to student_current and filter by date. It's a pretty common practice. Having two tables with foreign keys pointing to each other can be done, it just feels weird. Jan 16, 2013 at 19:45
  • Perhaps in your design it will always be one to one in case of parents, but in real life, that will not always be the case. Even if you only need one parent listed, the student may have divorced parents and this month he's with one, next month he's with the other: parents die and divorce and remarry and generally make database designs more complex than you want them. Jan 16, 2013 at 21:41
  • @FrustratedWithFormsDesigner I'll go with your first feeling, that it's weird (and to be avoided). Circular paths in the FK constraints are "easy" to be dealt with, only in DBMSs with deferrable FK constraints (like Postgres). Jan 16, 2013 at 22:28

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

  • And if a Parent has more than one kids as students in the school? Jan 16, 2013 at 18:54
  • I did not comment on full design. It is case of many to many.
    – Gulli Meel
    Jan 16, 2013 at 18:56
  • ypercube - It is always one to one as they need to store independent relationship for a sibling, as there can be simple variation in name etc of the parent. So doesnot need to be many to many. @gulli - Thanks. Please check the updated question.
    – Ehs4n
    Jan 16, 2013 at 19:01
  • Not sure I have understood the new requirement.
    – Gulli Meel
    Jan 16, 2013 at 19:03
  • Student is having some additional data that keeps on changing like the class he is studying in, additional details related to that class like his roll no, section etc. Additionally the details in student base table may be updated at any time and I want to create a new revision of the record so that the previous record is saved somewhere.
    – Ehs4n
    Jan 16, 2013 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.