Relational design question

• barry555

  SSC Eights!

  Points: 944

  More actions

  March 22, 2006 at 9:32 am

  #168186

  My database has 4 tables (among others!) representing the entities ResourceHire, Pupil, Parent and Tutor.  A resource can be hired by any of Pupil, Parent or Tutor.

  My question is how do I design the ResourceHire table to best enforce referential integrity (RI) whilst being normalised?  Do I add to the ResourceHire table the columns:

  a)  HirerType (Pupil, Parent and Tutor) and HirerID (the ID from the respective entity's table), or

  b)  PupilID, ParentID and TutorID

  The problem with (a) is that I can't set up relations between the HirerID column and the other 3 tables.  So, I'd have to handle RI myself, presumably through Delete triggers on Pupil, Parent and Tutor tables.

  (b) would allow me to set up relations and thus have the RDBMS enforce RI, but would involve 2 out of 3 columns being wasted for each row added to ResourceHire.

  Is (b) the comprimise, or is there a better, more 'pure' design?  Should I have a separate ResourceHire table for each entity?!

  Thanks

• magarity kerns

  SSCrazy

  Points: 2763

  More actions

  March 23, 2006 at 7:23 am

  #628334

  So, which school do you attend?

• Gerhard Schmeusser

  SSChasing Mays

  Points: 641

  More actions

  March 31, 2006 at 5:11 am

  #630044

  I suggest 3 tables

  ResourceHireParent

  ResourceHirePupil

  ResourceHireTutor

• barry555

  SSC Eights!

  Points: 944

  More actions

  March 31, 2006 at 5:58 am

  #630059

  That's what I thought.  Just seems a bit clunky.

  Thanks.