0

So I have two tables -- Student table and Staff table, I also have an Item table, the plan is for students or staff to take custody of an item, put in another way, for an Item to have a custodian (student or staff). So I created a new table -- Item_Custodian. How do I model a relationship that would enable my Item_Custodian table holds information about an Item and it's custodian be it a student or a staff. Suggestions, tips etc are welcomed. Thanks

evuazeze
  • 102
  • 2
  • 7

1 Answers1

2

I would share three approaches I know for handling such relationships:

  • Approache 1: Define two separate nullable foreign keys in Item_Custodian one referencing to Staff and one referencing to Student resulting in two physical relationships one of which is always null.
  • Approach 2: Define two ranges of Ids for Student and Staff in a way that don't overlap. Then, create only one column in Item_Custodian and initialize it with either of the two table Ids resulting in a logical relationship.
  • Approach 3: Define two columns in Item_Custodian one as a logical foreign key and the other as the type of the first column (i.e. StaffType or StudentType).

In your scenario where there are only two custodians, I personally prefer the first approach for two reasons:

  1. It creates physical relationship between tables
  2. Only one null column does not make a table sparse
Amir Molaei
  • 3,700
  • 1
  • 17
  • 20
  • Crystal clear descriptions, in addition to the reasons you preferred the first approach, I don't have alteration access to either student or staff tables (working in a team). So, the first approach would go well. Thanks – evuazeze Mar 11 '19 at 06:25