1

I have 3 tables

   Student    Loan    Book
 - StudentID  LoanID  BookID

which foreign keys do i need to set so when given the student name, search all loan from that student and display the book detail

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
CompleteNewb
  • 125
  • 1
  • 3
  • 12
  • I'm assuming Students <-> Loans is 1:1 but Students <-> Books is many:many? – Aaron Bertrand Feb 11 '12 at 04:42
  • 1
    It depends. You need to decide how the relationships work and that will dictate the table design. Can a loan have only one book or more than one book? Same question for the relationship between students and loans. These are things you should include in your post to help people understand the problem you are trying to solve. – nolt2232 Feb 11 '12 at 04:46

4 Answers4

6

Here's a start with such vague requirements:

CREATE TABLE dbo.Students
(
  StudentID INT PRIMARY KEY
  -- , other columns about students
);

CREATE TABLE dbo.Loans
(
  LoanID    INT PRIMARY KEY,
  StudentID INT NOT NULL FOREIGN KEY REFERENCES dbo.Students(StudentID)
  -- , other columns about loans
);

CREATE TABLE dbo.Books
(
  BookID INT PRIMARY KEY,
  -- , other columns about books
);

CREATE TABLE dbo.StudentBooks
(
  StudentID INT NOT NULL FOREIGN KEY REFERENCES dbo.Students(StudentID),
  BookID    INT NOT NULL FOREIGN KEY REFERENCES dbo.Books(BookID)
);
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
3
Student
--------      
Studentid -PK

Loan
---------
Loanid  - PK
Studentid -FK


Book
-------
Bookid  -PK
Loanid   -FK
Ajeet Sinha
  • 2,315
  • 20
  • 20
1

Not sure what columns you have, assuming you have studentId in student table, it would be best candidate for primary in Student and foriegn in other two tables.

kosa
  • 65,990
  • 13
  • 130
  • 167
1

you have to use studentid as foreign key in both the other tables...because you want to search on the basis of student. so this key should go in the remaining tables

zeeshan hasan
  • 15
  • 7