I'm currently designing a database structure for our team's project. I have this very question in mind currently: Is it possible to have a foreign key act as a primary key on another table?
Here are some of the tables of our system's database design:
user_accounts
students
guidance_counselors
What I wanted to happen is that the user_accounts
table should contain the IDs (supposedly the login credential to the system) and passwords of both the student users and guidance counselor users. In short, the primary keys of both the students
and guidance_counselors
table are also the foreign key from the user_accounts
table. But I am not sure if it is allowed.
Another question is: a student_rec
table also exists, which requires a student_number
(which is the user_id
in the user_accounts
table) and a guidance_counsellor_id
(which is also the user_id
in the user_accounts
) for each of its record. If both the IDs of a student and guidance counselor come from the user_accounts table
, how would I design the student_rec
table? And for future reference, how do I manually write it as an SQL code?
This has been bugging me and I can't find any specific or sure answer to my questions.
See Question&Answers more detail:os