Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Cardinality of the relations is of great help in order to determine where to place the references (foreign keys). –  jap1968 Jan 16 '13 at 19:34
    
I was a student at my school, now I am a prof. Consider the Party Model please. –  Neil McGuigan Jan 22 '13 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.

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.

share|improve this answer
    
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 '13 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 '13 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. –  FrustratedWithFormsDesigner Jan 16 '13 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. –  thursdaysgeek Jan 16 '13 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). –  ypercube Jan 16 '13 at 22:28

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

share|improve this answer
    
And if a Parent has more than one kids as students in the school? –  ypercube Jan 16 '13 at 18:54
    
I did not comment on full design. It is case of many to many. –  Gulli Meel Jan 16 '13 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 '13 at 19:01
    
Not sure I have understood the new requirement. –  Gulli Meel Jan 16 '13 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 '13 at 19:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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