0

I have a facultyand studenttable to store each information, respectively. I made a contacttable to store the contacts of either faculty or student. I would like to use the contact table as a generic one, means it can be used by either faculty or student.

My table diagram is something like this.

I compressed faculty and student into a USER table and have an account type in that table to know whether it is a faculty or student type of user. Then the USER table will have a one is to many relationship with the contact table.

Student

Faculty

Contacts

Student & Faculty = User table
User table -----<- Contact table

Md Haidar Ali Khan
  • 6,447
  • 9
  • 38
  • 60
user3323654
  • 55
  • 1
  • 2
  • 9
  • In practice what you're proposing is good. A good practice is to also have an AccountType table that will contain the friendly description of the account type and an integer. Then use that integer in the User table in the account type field. Any indexing on that field, in the user table, will be more efficient. You only need to join to the AccountType table when you care to display the friendly name. Also modifications to AccountType values will be much more efficient. – SDillon Apr 28 '15 at 12:56
  • Is there an another way around? – user3323654 Apr 28 '15 at 13:09

1 Answers1

1

True story: I was a student at my school, then I taught there. Then one of the teachers took my class. You need to account for silliness like this.

And a faculty member or student isn't necessarily a user either.

Use table inheritance.

Faculty : Individual : Party

Student : Individual : Party

Where : means inherits.

Party hasOne User

A contact is a relationship between two parties:

Party hasMany ContactRelationships belongsTo Party

See Ready-to-Use Database models example for reference models.

Community
  • 1
Neil McGuigan
  • 8,423
  • 4
  • 39
  • 56