0

Which is the best practice to create the foreign key from the mentioned two- possibility 1:

table1 : user(id,name,password) table2 : exams(id,name) table 3 : user_exam(is,user_id,exam_id)

possibility 2:

table1 : exams(id,name) table2 : user(id,name,password, exam_id)

priyanka chordiya
  • 29
  • 3
  • I guess the first one, coz I feel one user could be associated with multiple exams.. user has_many exams, in that case, you can't do with the second one.. and if thats not the case, then you could have the second table where user has_one exam. – argentum47 May 28 '15 at 19:03

2 Answers2

1

Based on what you are modeling, I would guess that you have a many-to-many relationship between Exams and users. In other words, you coudl have exams without users and users without exams. In this case model 2 does not work at all.

In model 2 each user would only get one id or you woudl have to mhave multipel users records each time an exam for tehm is added, This increase the likelihood of data integrity problems espcially since password is there. Do not even consider using model 2 unless you can guarantee there will never be a need for more than one exam.

Depending on what type of exams you are talking about the user_exams table should probably include additional information such as a date. What else you might need depends on the meaning of teh data you are modeling.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

Your question is more like an opinion based question but as far as I know foreign keys should be in the junction table. So Possibility 1 is an ideal approach to use the foreign key.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • thanks Rahul:) may i know what might be the reason for possibility 1 to be an ideal approach? – priyanka chordiya May 28 '15 at 19:05
  • @priyanka:- 1. The table(user_exam) could be useful when you want to get only the ids from the two tables(*possibly in some application*). 2. It would be useful if user has many exams as said by argentum. 3. Referential Integrity should be handled on the lowest possible level. – Rahul Tripathi May 28 '15 at 19:11
  • @priyankachordiya:- And if my assumption is not wrong then this question is very much linked to http://stackoverflow.com/questions/29217781/hi-i-am-having-the-query-with-database which you have asked. So the best explanation for the possibility 1 is in your question itself ;) – Rahul Tripathi May 28 '15 at 19:14
  • Becaz u do not want password for all exams of a user. Else you will have data redundance e.g. same password will be repeated and then fetching the password for a user will use distinct or group by. Same problem will be with user name – Anil May 28 '15 at 19:15
  • Foriegn keys are often not necarray for junction tables (and probably are not more often than they are). it depends on the meaning of the data and the relationship of the data. In this case, it woudl ata a gues be a many to many realtinoship and a junction table is the correct choice. BUt an pk/FK for instance of OrderDetails to the Order table is a classic one to many and does not need a junction table. – HLGEM May 28 '15 at 19:36