Mailinglist Archive: opensuse-edu (45 mails)

< Previous Next >
Re: [suse-linux-uk-schools] Yet another school database
Hi Thomas Adam

>
> Hence the "Class" table would be the master, and "Classification" the
> detail. "Class" would therefore have:
>
> classId (Primary Key)
> classification (varchar (255))
>
> and "classification":
>
> classfnId (primary Key)
> classId (foreign key)
> classfn_name (varchar (255))
>
> Using referential integrity from within postgres (via SQL), it's then a
> smatter of linking child --> master. So yes, it's certainly possible
> to do.
>

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

regards
Richard

--
richard@xxxxxxxxxxxxxxxxxxxx



< Previous Next >
References