The standards set out for data exchanges in England should help you here - CBDS is pretty specific about things like ethnicity religion. Each student has exactly one ethnicity, from this list: http://www.teachernet.gov.uk/_doc/6845/ETHLIST.XLS so you make ethnicity a field in your student table, as you have done. As stated earlier, there are several ways you can use the list of possible values. Enumerating them in the table definition would work (you could still put something in your front end to allow editing of the list) or you could store them in your "ethnic" table if you preferred - this seems clearer to me. The whole class/classification thing is more flexible, but more complicated. You will need this level of complexity further down the line when you come to think about the connections between students, teachers, classes and courses, but at this early stage the things you are talking about are straightforward attributes of students, and so IMHO belong as fields in the student table. This may also help: http://www.teachernet.gov.uk/_doc/7995/CBDS%20Pupil%20V3.1.xls On Monday 06 June 2005 14:53, Thomas Adam wrote:

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

-- Robert Jones ------------------------------------------------------- -- Robert Jones