1

I have two tables with one to one relationship b/w student and Student_detail(kind of supplementary table) table

Design 1

Student
 student_id
 name
 ....

Student_detail

 student_detail_id
  student_id(foreign key to student_id in student table)
  ...

Design 2

Student
 student_id
 name
 student_detail_id (foreign key to student_detail_id in Student_detail table)
 ....

Student_detail

 student_detail_id
   ...

My question is which approach is better ? From design perspective is it better to keep the foreign key in main table(in this case student table) or in supplementary table (student_detail table ) ?

Per my understanding approach 2 is better as if we add more supplementary tables we don't have to modify main table ?

  • If the tables are 1:1, why have two tables?? (There are valid reasons, but I want to know your justification.) – Rick James Jul 24 '18 at 18:17
  • As main table already has close to 100 columns. So it is partitioned into two . columns which are less frequently used are kept in supplimentary table. Also this is legacy design not newly introduced. – user3198603 Jul 24 '18 at 18:27
  • 2
    Even if a justifiable one-to-one, I'd still treat the "primary" table as if it were a parent, and the "detail" as if it were one potentially multiple children. Thus the FK would go in the 'details' table. More appropriately named 'additional_student_details'. – EdStevens Jul 24 '18 at 20:36
  • @EdStevens - Put that in an Answer. – Rick James Jul 24 '18 at 21:58
  • 2
    I think a student detail cannot exist without a student. So I'd put the FK in the student detail table as a NOT NULL to enforce that. As a student probably should only have one detail entry I'd also make it UNIQUE (or the PK). – sticky bit Jul 24 '18 at 22:38
1

Suggested that previous comment be posted as 'answer'. Here 'tis.

Even if a justifiable one-to-one, I'd still treat the "primary" table as if it were a parent, and the "detail" as if it were one of potentially multiple children. Thus the FK would go in the 'details' table. More appropriately named 'additional_student_details'.

1

After reading couple of articles on google and comment here, I believe ideally there should not be any need for supplementary table. But yes if there is any FK should go inside detail/supplementary like one to many relationship. Also we don't have to modify main table in case any new further details table come up in future

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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