Mailinglist Archive: opensuse-edu (45 mails)

[richard@xxxxxxxxxxxxxxxxxxxx]

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