Table design advise

I've already posted this to comp.databases and realised this is a more appropriate place to post. I'm trying to model a student registration system - unsuccessfully. Can somebody tell me what I'm doing wrong (I've only supplied a cut down version of the part of the system I'm having difficulty with)

System Description:
Each student has an employer. Each student has a tutor at university. Each student has a supervisor whilst working for their employer.

I've modelled this as follows:
- Each STUDENT has one EMPLOYER

• Each EMPLOYER has many STUDENTS (it's possible more than one student will work for them)
• Each STUDENT has one TUTOR
• Each TUTOR has many STUDENTS (it's possible more than one student will be assigned to a tutor)
• Each EMPLOYER will have many SUPERVISORs
• Each SUPERVISOR will have one EMPLOYER (a supervisor will only ever work for one company at a time).

When coding this up the primary/foreign keys are allocated as follows:
- STUDENT has a primary key that noone references in this example

• STUDENT has a foreign key from EMPLOYER.
• STUDENT has a foreign key from TUTOR
• SUPERVISOR has a foreign key from EMPLOYER

This presents me with the following problems:
- When assigning an EMPLOYER to a STUDENT, it's not possible to define
a SUPERVISOR for a particular STUDENT - effectively I'd have to have multiple entries for the same employer in the EMPLOYER table for each SUPERVISOR. This makes me think the EMPLOYER should have a foreign key from SUPERVISOR. Even still, this still means I can't associate a SUPERVISOR to a STUDENT for a particular EMPLOYER!!!!

I'm really confused. Maybe I should have a relation from STUDENT directly to SUPERVISOR - but somehow it shouldn't be possible to select one that isn't assigned to there employer. Maybe I should code up a trigger to implement this.

Any ideas?

Thanks in advance
Regards
DJW Received on Mon Nov 14 2005 - 19:24:03 CET