Re: Table design advise

dwj wrote:
> 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).

You might want to be more careful with your 'many' attributes. Are they actually zero-or-more, or one-or-more, or more-than-one?

> 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.

Funny - I've always thought of it as a foreign key (referring) to something else.

> - STUDENT has a foreign key from TUTOR
> - SUPERVISOR has a foreign key from EMPLOYER

And what about the student's supervisor?

> 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

Oh? Why not?

> - effectively I'd have to have
> multiple entries for the same employer in the EMPLOYER table for each
> SUPERVISOR.
No.

> This makes me think the EMPLOYER should have a foreign key
> from SUPERVISOR.

Presumably, an employer has zero or more supervisors, and each supervisor has exactly one employee. Further, the supervisor for a student should work for the employer of the student. Each supervisor record will have an attribute that identifies their employer.

> Even still, this still means I can't associate a
> SUPERVISOR to a STUDENT for a particular EMPLOYER!!!!

It seems perfectly plausible to me. A student has attributes that refer to: an employer, a supervisor, and a tutor. A supervisor has an attribute that refers to an employer. And there's an extra constraint that the employer of a student must also be the employer of the supervisor.

> I'm really confused.

Yes.

> 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.

There is probably a constraint that the employer of a student must also be the employer of the student's supervisor - and given the declarative mechanisms available in current DBMS implementations, you might have to resort to a set of triggered actions to ensure that the state of the system remains self-coherent.

You're actually mostly on the right track - you just need to clarify your thinking about directions and cardinality.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/