I have two tables....

1. Tutor Projects where Many Tutors will have many Projects.
SupervisorID
PK = ProjectID

2. Student Projects where a Student will pick many Projects from above.
Comp PK=StudentID and ProjectID.

Now they want it so each student can't pick the same tutor more than once!
So this would mean adding the SupervisorID as a PK to table2 which
I don't want to do as it would go against '2nd normal form' rule and cause all sorts of problems!

So how fo i go about letting each student pick different Projects (as they always have) but restrict them to unique Supervisors :confused:

Hi,

I think the best way would be to perform somekind of a lookup on the user ID before data entry occurs.

so something like

select *
from [Student Projects] sp
inner join Tutor Projects tp
on tp.Projectid = sp.projectid
where tutorid = @tid and studentid = @sid

if @@rowcount > 0

print "you cannot select this tutor as you already have him on another project"

else

--perform insert statement here


I hope you can get the basic jist of what I am saying here, even if my code doesn't suit your scenario exactly.

Let me know how you get on

Cheers

Yes, I think you're right here.

I always like to do any 'enforcing' right at the beginning at the table/rel structure so as to cover all angles, but I don't think that's possible here.

They'll only be selcting via the same apx page so I should only have to code it once, to something like you suggested.

Many Thanks