Post reply

Relational design question

  • 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

  • March 23, 2006 at 7:23 am

    #628334

    So, which school do you attend?

  • March 31, 2006 at 5:11 am

    #630044

    I suggest 3 tables

    ResourceHireParent

    ResourceHirePupil

    ResourceHireTutor

  • March 31, 2006 at 5:58 am

    #630059

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

    Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply