On Sun, 2005-06-05 at 21:07 +0100, richard@tortoise.demon.co.uk wrote: [snip]
I've implemented tables class & classification with this design, which you will see if you connect again. (The previous 'look-up' tables are still also there for now.) I've put a bit of data into them. A problem I have with this design, which maybe somebody can give a solution to is this:
I now want to say what ethnic group students belong to, and so on, so I need a table like (apologies for word-wrap):
create table student_classification( student_classification_id integer unique, student_id integer constraint references student(student_id), classification_id integer constraint references classification(classification_id) )
However, a student belongs falls into at most 1 ethnic group.
Is that right - there is no possibility for mixed ethnicity? I would have thought it would be the other way round, that is at minimum a student belongs to 1 ethnic group.
Therefore I need a constraint on the table to prevent the entry of two records where the same student is assigned 2 classification_id's which actually belong to the same class (ie class_id=2 , Ethnic).
Comment above aside, if you want only one student_classification per student_id, then make student_id in the table above unique - if you do this (if this is what you want) you are creating a 1-1 mapping between a student and their student_classification, so there is no need to have a student_classification_id column, since a record is uniquely identified by the student_id.
A table constraint won't do, as two tables are involved.
regards Richard
-- richard@tortoise.demon.co.uk