0

I am developing a school management system, where the database has been simplified as given below:-

users(id, email, password);

students(id, user_id, roll_no, first_name, last_name, dob);
teachers(id, user_id, name, qualification);
guardians(id, user_id, name, phone, address);

From the database it is cleared that, we have a common login system for all kinds of profiles.

Problem: I can access a user credentials from a given profile such as students or teachers as there is a foreign key associated into the profile table. But to know the type of profiles associated to a user, I can not go down without searching each profile table.

What I have tried: I have created an intermediary table such as:- 

user_profiles(id, user_id, table_name)

But this does not seems to be efficient as I need to code extra. Is their any way to associate users table to their respective profile table in the database layer itself?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Dipendra Gurung
  • 5,720
  • 11
  • 39
  • 62
  • You will definitely need an intermediate table. It is like similar to having a User belongs to one or many Roles. A centralized table to know which ones exists on other profiles. – johnpili Jan 18 '17 at 07:39
  • Assuming the 3 tables are for profiles, since you also have the users table you do not need to have other tables or to "go down with searching", you just join. Work through an example. – philipxy Jan 18 '17 at 08:31
  • @Bill Karwin, I didn't knew that term "polymorphic associations". Since it is not possible to do using SQL constraint, what do you have to say about merging all those tables to single one as `profiles`? – Dipendra Gurung Jan 18 '17 at 08:39
  • @DipendraGurung, I linked your question to another as a duplicate. Please see my answer there. Or see http://www.slideshare.net/billkarwin/practical-object-oriented-models-in-sql – Bill Karwin Jan 18 '17 at 18:14

0 Answers0