--- richard@tortoise.demon.co.uk wrote:
I now want to say what ethnic group students belong to, and so on, so However, a student belongs falls into at most 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).
A table constraint won't do, as two tables are involved.
So you need to split it up a little. The problem you have is that each classification category itself might have many things associated with it. That's fine, but that calls for a re-think. One solution (ignoring any form of schema design) would be to make each classification below each heading (i.e. 'ethnic group' for instance, might have several elements attached to it) an enum of possibilities. Whilst this is possible, there's several issues with it -- least of which is the fact that you'd then have to use some form of trigger, which is overkill in this case. So what I propose is we revist the schema overall. I don't have a lot of time to spend on this as I'd like. However, I'll outline some of my suggestions. As it stands, the relationship between 'class' and 'classification' as entities is OK. What we need now is a means of linking a student to this, in such a way as to meet the constraint [1] that you're wanting. It seems to me that you want something like the following (I apologise in advance for my poor ASCII diagrams): +--------------+ | Class | +--------------+ | | / \ +-------------------+ +----------------+ | Classification | | Student | +-------------------+ +----------------+ | | | | / \ / \ +----------------------------------------+ | Belief | +----------------------------------------+ The nomenclature for table 'Belief' is questionable, and a more suited name is advised. Essentially. 'Belief' is a link entity. The assumption here is that there is a M-M (many to many) relationship now, between Classification and Student. Due to the referential integrity model, one cannot (directly) implement a M-M relationship between two entities, so a third table is introduced. Essentially, 'Belief' would hold foreign key references to both Classification and Student, so that in this way, 'Belief' could then easily define whether a student could be a member of a group or not, based on table constraints enforced therein. Does that make sense? -- Thomas Adam [1] It's not really a constraint in the sense of a check, it's more a design feature. ___________________________________________________________ How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com