How to reference one foreign key column with multiple primary key column

Question

I am creating BOOK_Issue table which will contain id of person to whom the book is issued. i have a column name user_id witch will contain ids from tbl_student as well as tbl_faculty. so how to set user_id field of book_issue table with reference to two primary key columns.

Answer 1

Create a "person" superclass that can be either of type "student" or type "faculty". Reference this from the BOOK_Issue table instead.

Basically to create this relationship, you'll need one unique ID that spans both "student" and "faculty". Put this in a table (tbl_person?) and have each row in tbl_student and tbl_faculty reference this new table. It's probably also best to then pull out the fields present in both tbl_student and tbl_faculty and put them in this new supertable instead.

Answer 2

You can solve the problem by either having an extra column in BOOK_Issue table, next to user_id, which indicates if this is a Student ID or a Faculty ID.

Alternatively, the IDs themselves may readily include some pattern which indicate their nature (for example no all faculty Ids may start with say "UC", and none of the student Id are so).

The two solutions above then allow using queries similar to the following

SELECT B.*,
       CASE B.BorrowerType  -- could be LEFT(user_id, 2) etc...
          WHEN 'S' THEN S.Name
          WHEN 'F' Then F.Name
       END As Name,
       CASE B.BorrowerType 
          WHEN 'S' THEN S.PhoneNumber
          WHEN 'F' Then F.Phone    -- Note that these constructs allow
                                   -- mapping distinct columns names etc.
       END As PhoneNr
FROM BOOK_Issue B
LEFT JOIN tbl_student S ON B.BorrowerType = 'S' AND B.user_id = S.id
LEFT JOIN tbl_faculty F ON B.BorrowerType = 'F' AND B.user_id = F.id
WHERE B.DueDate  < '11/23/2009' -- or some other condition

This can get a bit heavy when we need to get multiple columns from the student/faculty tables. A possible alternative is a UNION, but this would then cause the repeating of the search clause.

Finally, the best solution but not avaible on all DBMS is a sub-query driven by an "IF B.BorrowerType = 'S' " condition.

Answer 3

Your database schema is not correct.

If you expect unique IDs then they should be in one table.

You can create a table with all the users, and have a column to set their type (student, faculty). Then create 2 different tables for each type that has the proper information for each user based on their type.

Answer 4

This should be your table design:

FacultyTable (FacultyID, FacultyName) StudentsTable (StudentID, StudentName, FacultlyID, ...) BookTable (BookID, BookName, ...) UsersTable(UserID, UserName, UserPassword, StudentID, LastLogin, ...)

Now this is the main thing:

BookIssedTable(BookIssedID, BookID, UserID) //This table tells me that a book of "BookID was issued to a user of "UserID" //this can be better for this is certainly a great improvement from the initial design.