March 22, 2006 at 9:32 am
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
So, which school do you attend?
March 31, 2006 at 5:11 am
I suggest 3 tables
ResourceHireParent
ResourceHirePupil
ResourceHireTutor
March 31, 2006 at 5:58 am
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